The Art of Structuring, Part 1 — From Messy Information to Multiple Projections

Even records for the same company produce separate sales totals when systems use different IDs and spellings of its name. A person recognises one company, but the computer cannot treat the records as one customer.

This three-part series examines how familiar data can be made processable by computers.

Part 1 uses record linkage and everyday copy-and-paste work to explain how to decide what counts as one item and how to choose purpose-specific tables and storage formats.

1. Structuring as familiar data organisation

1.1 The difficulty of linking customer lists

Suppose customer management, billing, sales support, and customer support each export a CSV file. Each contains one record that appears to describe the same corporation.

customer_id,company_name,address,phone
C-1042,株式会社アソピテック,東京都千代田区丸の内1-2-3,03-0000-1234

billing_account_id,bill_to_name,bill_to_address,tel
B-7780,（株）アソピテック,東京都千代田区丸の内1丁目2番3号,03 0000 1234

account_id,account_name,office_address,main_phone
S-223,ASOPI TECH Inc.,"1-2-3 Marunouchi, Chiyoda-ku, Tokyo",+81-3-0000-1234

organization_id,organization_name,address,phone
T-9981,アソピテック,千代田区丸の内1-2-3,

The four records cannot be aggregated as data for the same corporation while retaining each system’s ID. A human reader may infer that they describe one corporation, but the ID columns are named customer_id, billing_account_id, account_id, and organization_id. Their values—C-1042, B-7780, S-223, and T-9981—do not match, and neither do the forms of the company name, address, and telephone number. Aggregate them by company as they stand and one corporation’s transactions are split into four subtotals.

In record linkage—identifying and combining the same customer across datasets—the columns corresponding to company names and telephone numbers in the four systems are mapped to one another. The process then decides which records represent the same corporation and links them to a common customer ID.

Have you repeatedly copied customer names, product names, quantities, and delivery addresses from a neatly formatted order list or form in Excel or Word into a separate ordering spreadsheet? In the destination table, was it difficult to decide whether one row should represent an order or a product, and which columns should receive the customer name and quantity?

The same decision about the unit of one item and its column mapping appears in the opening record-linkage example. To group records by corporation, did someone compare names, addresses, and telephone numbers and manually link tens of thousands of records to common customer IDs?

Structuring means deciding what constitutes an entity (managed unit), which attributes, relationships, and constraints it has, and which data types and formats represent it, so that information can be searched, compared, aggregated, validated, and inferred from.

1.2 Matching notation and corporate identity

Remove 株式会社 from the company_name value 株式会社アソピテック in customer-management.csv, and remove （株） from （株）アソピテック in the bill_to_name field of billing.csv; both become アソピテック. Remove hyphens from 03-0000-1234 in the first file’s phone field and spaces from 03 0000 1234 in the second file’s tel field; both become 0300001234.

For +81-3-0000-1234 in the main_phone field of sales.csv, the country code +81 must be converted to the domestic leading 0. Because phone is empty in support.csv, アソピテック in organization_name and 千代田区丸の内1-2-3 in address can narrow the candidates.

Normalisation of notation and the identity decision are separate processes in record linkage. Replacing （株） with 株式会社 reduces input variation, but cannot determine whether 株式会社アソピテック and ASOPI TECH Inc. are the same corporation. Unicode defines normalisation forms that convert visually identical character sequences into comparable forms; they are not rules for corporate identity.

The attributes used for an identity decision depend on the population being matched and the use case. Candidates here include name, address, telephone number, corporate number, source, and observation time. NIST’s Digital Identity Guidelines likewise describes identity resolution as collecting the minimum evidence and attributes needed to distinguish one person or entity within a population. Before assigning an identifier, a rule must state what counts as the same thing.

Identity criteria must consider both false merges and false splits. Merging on telephone number alone may combine separate corporations that share a main number. Allowing only exact matches leaves the English spelling in sales.csv as another corporation.

Record linkage, deduplication, or entity resolution can be divided into comparison across attributes, candidate generation that narrows the pairs to compare, and an identity decision about whether they are the same entity. A US Census Bureau overview of record-linkage research likewise treats name and address standardisation, approximate string comparison, and candidate search as separate processes.

The criterion for combining companies into one billing destination may differ from the criterion for confirming they are legally the same corporation. Sales analysis may treat several corporations in a corporate group as one customer, while contracts and billing separate them. The identity boundary changes with the use case even for the same records.

1.3 What does one row represent?

Suppose a configuration register contains this row.

server_id,application_id,environment,location,owner_team
srv-01,order-api,production,Tokyo,Platform Team

Define the row as one server deployment and it can represent the relationship that srv-01 runs order-api in the production environment, is placed in Tokyo, and is managed by Platform Team. The row count is then the number of server deployments.

If the team leader and contact details must also be managed, identify the team as a separate entity and refer to it from owner_team.

Order data adds a granularity problem not present in the customer and configuration examples. If one order contains several products, treating a row as an order requires several products in one row. Treating a row as an order item makes order_id appear in several rows, so the row count is the number of items, not orders. Payments and deliveries have their own counts as well. The design must decide whether each row represents an order, order item, payment, or delivery and relate them by ID.

The starting point for organising data is the use case: what users need to know and which operations they need to perform. Break goals such as combine records for the same corporation, count orders, and retry only failed payments into search, comparison, aggregation, validation, inference, and execution. Define entity boundaries, granularity, identity, and relationships, then collect observed attributes and values with their sources and observation times. A purpose-specific representation selects required attributes and defines fields, data types, and formats. Its storage mechanism follows from the same use case’s update frequency, random-access needs, batch processing, and retention period.

1.4 Representations change for lookup, aggregation, and relationship traversal

Suppose the four records in Section 1.1 have been linked and assigned C-001. The next examples use three fictional datasets recording customers after linkage, contracts, and relationships between corporations.

customer_master.csv maps a customer ID to a legal name, English name, and address.

customer_id,legal_name,english_name,address
C-001,株式会社アソピテック,ASOPI TECH Inc.,東京都千代田区丸の内1-2-3
C-002,株式会社アソピテック西日本,ASOPI TECH West Inc.,大阪府大阪市北区梅田4-5-6

contracts.csv records the customer ID, signing date, amount, and currency of each contract.

contract_id,customer_id,signed_at,amount,currency
K-1001,C-001,2026-07-01,1200000,JPY
K-1002,C-001,2026-07-18,800000,JPY
K-1003,C-002,2026-07-20,500000,JPY

company_relations.csv records that C-002 is a subsidiary of C-001.

from_customer_id,to_customer_id,relation
C-002,C-001,subsidiary_of

Each operation uses different files, attributes, and operators. Retrieving the company with customer_id = C-001 only requires an ID access path into customer_master.csv. Finding companies containing アソピテック requires an index that turns Japanese and English names into search terms. Calculating the July contract total for C-001 adds two records from contracts.csv, producing 2000000 JPY. Examining the whole corporate group traverses company_relations.csv and adds C-002 to the aggregation scope.

ID lookup, name search, monthly aggregation, and relationship traversal require different fields and operations even though they use the same customer data. The operation that selects properties needed for a use and maps them into a suitable representation is a projection.

2. Projections and sources of truth

2.1 Information retained and lost by projections

Suppose the following purpose-specific files are made from the records in Section 1.4.

normalized_name,customer_id
アソピテック,C-001
asopitechinc,C-001
アソピテック西日本,C-002
asopitechwestinc,C-002

month,customer_id,total_amount,currency
2026-07,C-001,2000000,JPY
2026-07,C-002,500000,JPY

customer_id,related_customer_id,relation
C-001,C-002,has_subsidiary
C-002,C-001,subsidiary_of

Each projection retains only what its use requires. customer_name_index.csv retains search terms and customer IDs, not addresses or contract amounts. customer_monthly_sales.csv retains monthly totals but loses the distinction between K-1001 and K-1002 and their signing dates. customer_relation_graph.csv retains the relationship between two companies but contains neither names nor sales.

A projection keeps required information and discards the rest. Making this information loss explicit explains why individual contracts cannot be restored from a monthly aggregate and why recalculation requires contracts.csv. Omit irrelevant information from a projection; when an operation needs discarded information, use the source of truth or another projection.

2.2 Regeneration from the source of truth

In PostgreSQL, a result corresponding to customer_monthly_sales.csv can be generated by this definition.

CREATE MATERIALIZED VIEW customer_monthly_sales AS
SELECT
  date_trunc('month', signed_at) AS month,
  customer_id,
  SUM(amount) AS total_amount,
  currency
FROM contracts
GROUP BY date_trunc('month', signed_at), customer_id, currency;

Here, contracts, which retains individual contracts, is the source of truth; customer_monthly_sales, which stores a query result, is derived data. Correct K-1001.amount from 1200000 to 1300000, rebuild the materialised view, and the total for C-001 changes from 2000000 to 2100000. Edit only the aggregate 2000000 and the change disappears at the next rebuild, while its supporting contracts remain unexplained.

The ANSI/X3/SPARC DBMS reference model also separates entity definitions, application-specific views, and storage formats and access paths. In a 1985 NBS report, the conceptual schema describes an organisation’s entities, attributes, relationships, and constraints; external schemas present fields and records as views for particular applications; and the internal schema handles storage formats and access paths.

Sources of truth may be separated by data type, such as customers, contracts, and corporate relationships. The three files here are related by customer_id. When the source of truth for each field and the query that generates derived data are known, that data can be regenerated.

Between a source of truth and derived data, define the items the use case requires: input data, output fields and aggregates, lost detail, invariants, freshness, and regeneration method. PostgreSQL materialised views are one example: they retain a defining query, store its result, and replace the contents with REFRESH MATERIALIZED VIEW.

3. Three classification axes and cross-cutting context

Storing customer data in an RDB while building a separate name-search index involves different decisions in the table definition and search method. A table design may use customer_id as the customer table’s primary key, retain legal_name and address as columns, and make customer_id in the contract table a foreign key. Search design may use a B-tree index for exact customer_id lookup and build an inverted index that tokenises legal_name and leads from each search term to customer_id.

Outside a database, what counts as one item and which ID identifies it remain design decisions. When an order is loaded from a database into a program, the design must state what one Order record represents and which database order ID its order_id corresponds to. Sending the order to another service requires the same decisions for messages, logs, and REST API paths. File paths, LDAP entries, and custom binary formats add decisions about hierarchy, references, and record boundaries.

Although their surface formats differ, the underlying technique is shared: name an entity, define its boundary, identity, and granularity, and assign attributes, relationships, and constraints required by its use. Concrete representations include database rows, program records, API paths and messages, log events, and records within files.

Before designing any representation, name the entities managed by the system and define their boundaries and identities. If customer, order, and service are managed, those names provide a basis for specifying the extent of one database row, program object, communication message, log event, and binary file record.

To trace data derived from the same entity, IDs in each representation must be mappable. Tracking customers or orders may mean recording customer_id or order_id in database rows, API messages, and log events.

Naming entities and defining their boundaries, identities, and granularity is the starting point of structuring.

Three questions organise design decisions. Is this the definition of an entity, an application specification, or placement into storage? For the same entity, is it deciding attributes and relationships, state transitions, execution units, send and receive units, storage placement, or search methods? Does it belong to a business process, data, an application, or the runtime platform? These questions form the axes of abstraction, concern, and domain.

Each design decision also records the information needed to interpret its representation, selected from identity mappings, time, provenance, versions, and constraints. This information is context.

3.1 Axis 1: abstraction

The abstraction axis has conceptual, logical, and physical levels. The conceptual level organises the people, things, and events the system should manage and decides what constitutes one entity. The logical level carries that entity into a use-case-based specification that the application can identify, search, and update; screen records and API messages are examples. The physical level determines the storage placement and units of storage, search, and update according to storage specifications and product characteristics. Structuring moves between these levels, defining the entities, representations, and placements each requires. Section 4 examines them in detail.

3.2 Axis 2: concern

In an order system, a consistent specification must state what the order_id field identifies and whether total_amount includes tax. It must relate orders, order items, and customers and organise rules for moving from accepted to paid and shipped. It must also decide which task executes payment, where retries begin, in what unit an order message is sent, where the record is stored, and how order_id is searched. The axis of concern asks how an established entity should behave and how it should be handled.

The order schema, relationships, state transitions, task definitions, message format, storage placement, and indexes are separate design artefacts. Concern distinguishes what a design decides about the same entity. ISO/IEC/IEEE 42010:2022 also defines requirements for architecture descriptions and uses viewpoints and model kinds to address stakeholder concerns.

3.3 Axis 3: domain

For an order, organise the business process of acceptance, payment, and shipping. Define data that manages orders, order items, and transaction history, then carry use cases, services, commands, and APIs into the application specification. At runtime, place them into processes, containers, databases, networks, and storage.

These four design scopes are the business, data, application, and technology domains. Domain distinguishes whether the same order is being designed as business flow, managed data, application functionality, or runtime infrastructure. The Open Group likewise identifies four architecture domains in its explanation of TOGAF and ArchiMate.

3.4 Context crossing classifications

Supporting information crossing abstraction levels or domains must be recorded as context: ID mappings, time data, permissions, data entry points and process origins, versions, and format constraints. To trace a customer across domains, the business rule that assigns customer IDs must integrate with, or be translatable to, the rules mapping data entities, API identifiers, and audit logs. Debugging is difficult unless the provenance of who accessed which data under which permission at the time of an error has been retained.

4. Abstraction: conceptual, logical, and physical levels

The conceptual level identifies entities the system will manage from real people, things, and events. The logical level carries those entities into machine-computable specifications. The physical level places logical representations into units that are actually read and written. Sections 4.1–4.3 describe each level, Section 4.4 compares one entity across all three, and Section 4.5 examines how design moves between them until the use case is feasible.

4.1 Conceptual level: names and boundaries of managed units

The conceptual level decides what the future system treats as one business matter and in what units it manages people, things, and events. A managed unit created as a container for attributes and relationships is the business entity used in requirements and problem definition. Its boundary, identity, granularity, and relationships to other entities are design decisions.

Give each managed unit a name that distinguishes its scope. Examples include customer, corporation, and corporate group; contract, order, and payment can denote other units. A name such as transaction data, which may include several entities, leaves it unclear whether one item means an order, payment, or refund. Names and managed units are referenced by tables, APIs, messages, and logs, so choose them carefully enough to remain stable for a long time.

After determining the unit, assign attributes and values observed in real forms and system outputs. For the four opening CSV files, retain the names 株式会社アソピテック, （株）アソピテック, and ASOPI TECH Inc., together with each address and telephone number, on the corporation with their sources and observation times. Accumulate observed values as facts regardless of whether a particular search or aggregate uses them, and keep them separate from normalised names and inferred identity. A corporate number or billing address from another form can be added as a new attribute of the same entity.

Determining managed units at the conceptual level applies to customer management, programs, logs, directories, and custom binary formats. Customer management may treat corporations and corporate groups as separate entities and define the relationship in which one corporation holds several contracts. A program must decide what counts as one waiting job; a log, what counts as one event. A directory or LDAP system may separate people, organisations, services, and groups. A custom binary format must consider whether a record stores one measurement, image, or transaction.

A conceptual entity should not change every time an attribute is added. For customer or contract, observed values can be added and their assignment to name or address fields can vary by use while the name and managed unit remain stable. Table names, fields, data types, path names, and byte positions are made concrete at later levels.

4.2 Logical level: granularity of specifications and tests

The logical level derives from conceptual business entities the principal entities managed by the business system or application specification. When writing that specification, consider the identifiers, attributes, relationships, constraints, and operations required by the use case. Carry the selected elements and operations into a logical specification so the program can validate, search, compare, aggregate, and transition them.

The same observed facts can yield different logical representations. Customers and contracts can be represented by the relations customer_master(customer_id, legal_name, address) and contracts(contract_id, customer_id, signed_at, amount), by a document whose customer contains contract references, or by a graph whose nodes and edges represent corporations and contracts.

Logical representations change with the use case. To display contracts for each customer, split customers and contracts into tables and JOIN them on customer_id. To aggregate monthly sales by customer, project contracts into a view selecting month, customer_id, total_amount, and currency. To search customers by company name, project them into a name-search representation containing normalized_name and customer_id. Table decomposition, join conditions, and projected attributes are logical-level decisions based on entity granularity.

For example, a use case that searches a customer list by company name could specify:

input: name_contains = アソピテック
sort: legal_name ASC
return: customer_id, legal_name, address

Here the search string, ordering, and returned fields are behaviour observable by the user. If ordering is not required, legal_name ASC does not belong in the logical specification. Whether SQL, application code, or a search platform performs search and sorting is selected at the physical level according to performance and data volume.

The entity granularity established at the logical level becomes the basis for system-test scenarios, test data, and expected results. If customers and contracts are separate entities, a contract-registration test can use these units:

given: customer C-001 is registered
when: contract K-1001 is registered for customer C-001
then: one contract K-1001 is created and can be referenced from customer C-001

Changes to contract amounts, contract-state transitions, and contract search by customer also express preconditions and results in units of contract and customer. Aligning specification entities with test granularity permits consistent verification of which entity was created, updated, or retrieved.

A logical specification for waiting jobs can include identifiers, attributes, operations, and state transitions. It might use job_id as the identifier and contain state, priority, retry_count, and scheduled_at, then describe submission, cancellation, retry, priority changes, and the resulting permitted state transitions.

The job’s placement is a physical-level decision; queues and storage are selected according to persistence, processing order, and concurrency.

4.3 Physical level: placement and access methods

The physical level places logical representations into concrete units in memory, files, storage, and networks. Possible design items include I/O units, byte order, alignment, compression, partitions, replication, indexes, caches, and node placement. Which matter depends on requirements for performance, capacity, freshness, recovery, and regeneration time.

Choose storage from the downstream access method. An RDBMS suits repeated random access and short transactional reads and updates of order and payment states. PostgreSQL likewise uses MVCC to manage consistency under concurrent access.

If application logs or JSON are written once or infrequently and later retrieved by file or processed in bulk, storage such as Amazon S3, which identifies objects by bucket and key, can serve as their repository.

For large-scale search and processing of JSON logs, keep raw logs in S3 and project attributes used for search and aggregation into search or analytical platforms. Separating retention and search into different physical placements better suits the use case than placing large logs in a transactional RDBMS and scanning them repeatedly.

A use case determines physical-placement requirements as well as logical entity attributes. The same logical representation may belong in an RDBMS when downstream processing requires transactional random access, or in S3 when low-frequency writes are handled by file. Considering the physical level for each use case avoids making an RDBMS the default destination for all data.

Physical decisions vary with storage format. A custom binary format selects whatever reading and compatibility require from magic numbers, versions, byte order, field offsets, record lengths, and checksums. A database may require B-tree indexes, pages, partitions, row- or column-oriented layout, and replica placement. Records with identical logical fields can require different numbers of I/O operations and recovery procedures under different physical layouts.

4.4 Comparing one entity across three levels

The three levels differ as follows for the same entities.

The logical level represents operations performed by users and downstream processing as well as entity fields and relationships. Customer-list sorting and job retry can be included when required. The physical level decides where and through which access method those operations run.

4.5 Design moving among the three levels

Design moves among the conceptual, logical, and physical levels while checking whether the use case can be realised. Organise business entities and observed facts conceptually, establish specification entities logically, and write system tests at their granularity. Physically implement search, update, and storage and measure response and write performance. If testing reveals a missing managed unit, return to the conceptual level; if performance requirements fail, revise the logical representation or physical placement.

If one customer entity contains both corporations and corporate groups, it may support group-level sales aggregation but cannot uniquely manage corporation-level contracts and billing. Return to the conceptual level, name corporation and corporate group as separate managed units, and define their membership. At the logical level, carry their IDs and relationship into record specifications; at the physical level, rebuild indexes used for search and aggregation.

Physical choices depend on I/O frequency and permitted processing time. An object written once and processed later in bulk can go to object storage. Frequently updated orders and payments requiring transactions make an RDBMS a candidate. A requirement to return results within 0.5 seconds may call for a search index or precomputed projection; a batch permitted to run for three hours may scan the source of truth or columnar files. If measurements miss the requirement, revisit not only storage and access but also the logical representation used for search or aggregation.

New observations and attributes can be added while retaining the managed unit. Adding logical fields or changing a physical index can preserve the conceptual entity. Returning to the conceptual level only when the use case does not fit the managed unit keeps entity names and boundaries stable while allowing iterative system design.

When one screen combines full-text and ordinary conditional search, which fields enter the full-text index and how the two queries are composed depends on the selected product or database. Test result quality, response time, and usability in the implementation, moving between the logical search specification and physical index until both are settled.

5. One entity, multiple projections

Design multiple projections of one entity by locating them on the three axes of abstraction, concern, and domain, and recording each projection’s context.

Projection attributes and operations follow from use cases. ID lookup, full-text search, relationship traversal, aggregation, and screen display require different attributes and operations, so record the source of truth and generation method of each representation separately. Combining everything into one format introduces irrelevant attributes and processing for each use and tends to produce an intermediate form suited to none.

For multiple projections of the same customer, record corporate identity, address observation time, contract validity, and each value’s source as common context. Apply the same customer ID and time conditions and map source-of-truth fields to derived-data fields.

Multiple representations do not justify uncontrolled proliferation. Add addresses and contract amounts to customer_name_index.csv, then corporate relationships and display text to customer_monthly_sales.csv, and duplication and update paths for the same customer information multiply. Update each file independently and it becomes unclear which name, sales amount, or membership is correct and which changes disappear on regeneration. Avoid this by narrowing each projection’s concern—name search, monthly aggregation, or relationship traversal—and mapping its queries and retained attributes to a source of truth and regeneration method.

The three axes and context help locate a design question and the data supporting it. A query to display contracts for the same corporate group together involves at least these design subjects.

Each row can have a separate design artefact. When corporations and corporate groups are separated, define their names, boundaries, and membership. Choose a graph database or index type during physical design after confirming the logical representation and use case. A logical graph of membership may still be stored as relations. The company-name index and contract aggregate are separate physical projections made from the same source of truth.

The ID, time conditions, and sources used when combining artefacts into one query depend on the query and each artefact’s origin. To display active corporation-level contracts from this table, one possible design maps membership, search results, aggregates, and responses by customer ID and uses contract validity as an aggregation condition. If inferred corporate groups are also displayed, recording observed corporate information separately from the source of the inference rule makes the basis of the inference traceable.

Structuring names the entities managed by a system and determines their boundaries, identities, granularity, and relationships. It selects attributes and operations for each use case and carries them into logical specifications, then designs physical placement, access paths, analytical tables, and display views. If required search or aggregate results cannot be generated, inspect the definitions of managed units, logical representations, and physical placements and update the design at the relevant abstraction level.

This part established entities, examined the abstraction axis, and carried purpose-specific logical specifications through to physical placement. Part 2 organises relationships, behaviour, communication, and storage through the axes of concern and domain.