Entity Identity Versus Record Identity

Data Modeling

Entity identity answers “which business thing is this?” Record identity answers “which observation or row is this?” The mistake is treating every row that mentions a thing as if it were the thing itself. Good models separate the durable entity from the repeated observations, transactions, snapshots, and source-system records that describe it over time.

The core distinction: thing versus observation

Entity identity is about the business object you intend to recognize across systems or over time. A customer, product, vendor, subscription, device, household, or legal organization can all be entities if the business needs to reason about them as stable things.

Record identity is about one stored representation of data. A record may be an order line, invoice row, CRM contact row, website event, support ticket, daily product snapshot, or imported spreadsheet line. It may mention an entity, but it is not automatically the entity.

The practical modeling question is not “what is the primary key on this table?” It is “what does one row represent, and does that row identify a business thing or only an observation about that thing?”

A durable rule follows: an entity can have many records; a record should have one clear grain. If you confuse the two, deduplication, joins, metrics, and history all become harder to trust.

A small hypothetical dataset

Consider a small ecommerce dataset. The rows below are hypothetical and intentionally imperfect. They represent order-line observations, not a clean customer master.

Each row has a record identifier: order_line_id. Each row also includes attributes that appear to describe a customer: email, phone, and shipping name. It is tempting to use one of those attributes as the customer identity. The counterexamples show why that is unsafe.

Counterexamples that break simple identity rules

Identity rules usually look obvious until a counterexample appears. In the hypothetical order-line data, the same business customer may use two emails, one email may be reused by a family, a phone number may change, and a shipping name may be misspelled.

A counterexample does not mean you can never use email, phone, or name. It means the attribute is evidence, not automatically the entity identity. The model needs to say whether the attribute is a candidate key, an alternate key, an identifier observed at a point in time, or just a descriptive value.

What belongs to the entity and what belongs to the record

A clean model asks which facts are true of the entity itself and which facts are true only of a record. In the example, an order quantity belongs to an order-line record. A shipping name belongs to a particular order or shipment observation. A customer’s durable internal identifier belongs to the customer entity.

This separation does not require a large architecture. It starts with naming the grain of each table and refusing to overload one table with conflicting meanings.

• Customer entity: the business-recognized customer identity, such as an internal customer_id assigned after identity rules are applied.
• Customer identifier observation: an email, phone, loyalty number, or source-system customer number observed from a source at a time.
• Order-line record: one purchased product line on one order, with quantity, SKU, and order context.
• Customer attribute history: changes in customer status, segment, preferred email, or other attributes that may vary over time.

The same idea applies beyond customers. A product entity is not the same as a catalog snapshot row. A vendor entity is not the same as an invoice remittance line. A device entity is not the same as a telemetry event.

Use relational semantics, not just column names

Column names can suggest identity, but relational semantics define it. A table’s key should determine the facts in that row. If one key value can legitimately have multiple values for another column at the same time, that column is not functionally determined by the key at that grain.

For example, if email can appear with multiple different people, then email alone is not a reliable customer entity key. If order_line_id determines product_sku and quantity for one order-line observation, then order_line_id may be a valid record key for that record grain.

This is why entity identity and record identity should be documented separately. A record key can be perfectly valid while still being the wrong key for a business entity.

A practical model pattern for separating them

A common pattern is to create an entity table, one or more observation tables, and a mapping or identifier table that records why an observation is believed to refer to an entity.

The goal is not to hide uncertainty. The goal is to store it in the right place. If two source records are matched to one entity because they share a loyalty number, that matching rule should be distinguishable from a match based only on similar names.

1. Define the entity grain: Decide what one entity row means. For example: one business-recognized customer, not one email address and not one order.
2. Keep source record keys: Preserve order_line_id, crm_contact_id, event_id, invoice_line_id, and other source record identifiers.
3. Store observed identifiers separately: Treat email, phone, external account numbers, and usernames as observed identifiers with source and time context.
4. Record matching decisions: If records are assigned to an entity, store the rule or method used where it can be audited.
5. Allow correction: Design for merges, splits, and history. Identity models are rarely perfect on the first pass.

How joins go wrong when identity is confused

Many reporting errors start with a join that uses a record attribute as if it were an entity key. Joining orders to customer attributes on email may look reasonable until an email is shared, corrected, reused, or missing.

The result can be duplicate revenue, lost customers, unstable cohort counts, or a dashboard that changes when a non-business attribute changes. The join did not merely fail technically. It used the wrong semantic relationship.

A safer question is: what relationship does this join claim? If the join claims that two rows refer to the same entity, the join key must be supported by the identity rules for that entity. If the join only attaches an observation, the resulting table should not be treated as an entity master.

Diagnostic checklist for practitioners

Use this checklist when reviewing a table, source feed, or model design.

• What does one row represent? If the answer contains “sometimes,” the table may be mixing grains.
• Is the key identifying a business thing or a stored row? A source primary key often identifies the source record, not the entity.
• Can the supposed identifier change? Emails, phone numbers, names, addresses, and usernames can change or be reused.
• Can two entities share the same value? Shared inboxes, family accounts, business phone numbers, and reused product codes are common counterexamples.
• Can one entity have many values? One customer may have multiple emails; one product may have multiple supplier SKUs.
• Where is the matching rule stored? If the rule exists only inside a join, spreadsheet, or dashboard filter, it is not a governed identity rule.
• Can you reverse a bad merge? Entity models should support correction when two things were incorrectly treated as one.

Common failure modes

Using a source-system ID as a universal entity ID. A CRM contact ID may identify one CRM row, not the customer across billing, product usage, support, and marketing systems.

Using a descriptive attribute as identity. Name, email, phone, address, SKU text, or vendor name may help identify an entity, but each can fail as a durable key without additional rules.

Collapsing history into the current entity row. If changing attributes overwrite previous values without history, old records may appear to belong to today’s state rather than the state at the time of observation.

Deduplicating records before defining the entity. Removing duplicate-looking rows can destroy useful evidence. Some duplicates are repeated observations; others are distinct records that happen to share attributes.

Building metrics on unstable identity. Customer count, repeat purchase rate, churn, product adoption, and lifetime value all depend on what the model treats as the same entity.

Operator rules for better identity modeling

These rules help keep models readable and correct enough to operate.

• Name tables by grain: Prefer names that expose meaning, such as customer, customer_identifier, order_line, product_snapshot, or account_status_history.
• Do not promote evidence to identity too early: Treat emails, phones, names, and external IDs as signals unless the business has accepted them as keys.
• Keep raw record IDs: Even if you create a clean entity ID, retain source record IDs for audit, troubleshooting, and lineage.
• Document merge and split behavior: Decide what happens when two entities are merged or one entity is split after a correction.
• Test with counterexamples: Before accepting an identity rule, create examples for shared values, changed values, missing values, and conflicting values.

The aim is not perfect identity. The aim is a model that makes identity assumptions visible, testable, and repairable.

Key takeaways

• Entity identity identifies the business thing; record identity identifies one stored row or observation.
• A valid record key can still be the wrong key for customer, product, account, or vendor identity.
• Emails, phone numbers, names, SKUs, and source IDs are often identity evidence, not identity by themselves.
• Counterexamples are the fastest way to test whether a proposed identity rule is too weak or too strong.
• Good models preserve source records, store observed identifiers, and make matching rules auditable and correctable.

Next step

Pick one important table in your environment and write its row grain in one sentence. Then list every column currently used as an identity key and test each one against four counterexamples: shared value, changed value, missing value, and conflicting value.