Entity identity is the first data-model decision, not a surrogate-key afterthought

I think entity identity is the first data-model decision, not a surrogate-key afterthought. A generated integer or UUID can distinguish one stored row from another. It cannot decide which real-world thing the row represents, whether two records refer to the same thing, or whether the thing remains the same after its attributes change. Those are conceptual decisions, and every downstream key inherits them.

The three-model hierarchy exists for this reason. The conceptual model asks what entities the organization recognizes and which relationships matter. The logical model turns those commitments into entity types, attributes, keys, cardinality, and participation constraints. The physical model chooses tables, partitions, indexes, and generated identifiers. When teams jump straight to the physical layer, an auto-increment column creates the appearance of identity before anyone has defined sameness.

Consider “customer.” It might mean a legal contracting party, a billing account, a person using the product, a household, or a tenant in a SaaS platform. Each is a valid entity at a different grain. If one table quietly mixes them, no surrogate key repairs the category error. The system will count customers inconsistently, join activity to the wrong owner, and train models on labels whose unit changes from row to row. This is why AI-ready data must be modeled before it is embedded or queried.

Natural and surrogate keys solve different problems. A natural candidate key expresses a business claim about uniqueness — a jurisdiction plus registration number, for example. A surrogate key gives the platform a stable internal handle even when business identifiers are long, mutable, composite, or disputed. I usually want both: the surrogate for storage and joins, the natural candidate keys as governed constraints and matching evidence. Dropping the latter turns duplicate detection into an application convention that every pipeline implements differently.

Relationships expose identity errors early. Cardinality asks whether one account can have many users, whether a user can belong to many accounts, and whether participation is optional. An associative entity is not merely a join table when the relationship carries role, validity dates, or authority; it has acquired operational identity of its own. A weak entity depends on its owner for identification. Fan and chasm traps appear when those commitments are vague, producing plausible joins that double-count or silently omit entities.

Cross-system identity needs an explicit resolution record rather than a heroic join. Source identifiers should map to a canonical entity through evidence, confidence, method, effective time, and provenance. Merges and splits must remain reversible because identity decisions change. A household can divide, companies can merge, accounts can be reassigned, and two “duplicate” people can turn out to be distinct. The model needs history of the resolution decision, not only its latest result.

This is also a contract question. A data contract should state the entity grain, candidate keys, permitted identifier changes, cardinality, and semantics of unknown or not-applicable identities. Without that declaration, a schema can remain structurally compatible while the producer changes what one row denotes. Consumers keep parsing successfully while their joins and metrics become wrong.

Normal forms help once identity is clear because functional dependencies can place each attribute with the key that determines it. They cannot choose the entity for you. If the key names the wrong grain, a perfectly normalized schema preserves the wrong meaning with impressive consistency.

There is one precise concession: not every dataset needs durable real-world identity. Append-only telemetry, transient staging rows, and immutable event envelopes may be correctly identified by an event ID plus source and time. The boundary is reuse — once records are joined across systems, used to describe a continuing subject, or drive consequential action, row identity is no longer enough.

I would therefore begin data modeling with identity questions: what thing persists, what makes two observations about the same thing, which relationships carry their own meaning, and how can a mistaken match be reversed? Choose surrogate keys after those answers exist. A key should implement an identity decision, never impersonate one.