Some architecture debates never really disappear. They simply become less visible.
Natural keys versus surrogate keys is one of them.
When I started working with databases and enterprise applications, I encountered natural and composite keys much more frequently. An account had an account number. A branch had a branch code. A product had a product code. In many data models, the identifier was closely connected to what the entity actually represented in the business.
Over the years, I have seen the centre of gravity move strongly towards surrogate keys.
Create a table. Add an ID. Make it the primary key. Let the database, ORM or application framework generate it. Move on.
There are good reasons why this approach became popular. I have designed systems using both models throughout my career, and for many large transactional systems I too would generally lean towards a surrogate identifier today.
But I also think the pattern is sometimes applied too automatically. The question I would rather ask first is a little more basic:
Those two questions are related, but they are not always the same.
And now that the same data routinely travels across ERP systems, CRMs, APIs, analytical platforms and AI applications, that distinction matters more than it did when the database itself was the centre of the application.
Natural Keys — or What I Sometimes Think of as “Organic Keys”
The accepted database term is, of course, natural key.
But I sometimes like to think of natural keys as organic keys. Not as a formal term — simply as a way of thinking about them.
A natural key already exists in the business domain. We discover it; we do not create it purely for the database.
CurrencyCode = INR
AirportCode = DEL
EmployeeCode = EMP-10482
ProductCode = PRD-7281
CurrencyID = 17
AirportID = 3842
EmployeeID = 583921
ProductID = 7291842
The values on the left tell us something about the entity even before we look at another table. The values on the right usually tell the system a lot and the user almost nothing.
That makes natural keys intuitive. It does not necessarily make them the better architectural choice.
The Problem With “Natural” Is That Business Reality Changes
A natural identifier often looks permanent when the system is first designed.
The problem is the word permanent.
An employee code may contain an organisation or location code. The employee moves. A customer number may follow one company's numbering scheme. Two companies merge. A product code may change after an ERP migration. A supplier identifier may be unique only inside one source system. A booking reference may be unique within a provider, but not necessarily across every provider with which an enterprise integrates.
I have learned to be cautious whenever a design discussion contains the sentence, “this value will never change.” In enterprise systems, never is a very long time.
This is where surrogate keys are powerful. The persistence identity of an entity can remain stable even when its business identifiers change.
CustomerID can remain untouched while the identifiers around it evolve.
But a Surrogate Key Does Not Remove the Business Key
This is the part that is sometimes missed when teams adopt the “every table gets an ID” convention.
The table now has a perfectly good primary key. But suppose the business rule says that CustomerCode must be unique.
What stops this?
The surrogate key is happy. The business is not.
We still need to model the business rule:
That is why I do not see a surrogate key as a replacement for the natural key. More often, it is a separation of concerns: one identity for persistence, another set of attributes defining business uniqueness.
The “Every Table Needs Its Own ID” Fallacy
One side effect of modern frameworks and code generators is that developers can start treating a separate ID column almost as part of the syntax of creating a table.
Consider a genuine one-to-one extension of an Employee entity:
The profile shares the identity of the employee. That is perfectly legitimate.
Yet it is common to see:
Now we generate another identity and then add a uniqueness constraint to establish that only one profile can exist for an employee.
There are cases where this is absolutely justified — perhaps the profile will later have its own lifecycle or relationships. But if it will not, the additional ID may simply be ceremony.
The lifecycle of the entity should drive the model, not the default template of the ORM.
Natural Keys Can Make Relationships More Expressive
One thing I still like about natural keys is that a relationship can sometimes explain itself.
A dependent table carrying the same columns makes the business relationship visible in the data model.
Composite natural keys can therefore be elegant from a domain perspective. Relational databases support them perfectly well.
The problem starts when that identity has to travel everywhere.
If CompanyCode + CountryCode + BranchCode is repeated through twenty transaction tables, APIs, DTOs, event messages and ORM relationships, the logical elegance starts creating practical friction.
A single BranchID is much easier to propagate.
This is one of the strongest practical arguments for surrogate keys in large systems.
Storage, Indexes and Joins: There Is No Universal Winner
It is tempting to say that a surrogate key takes more storage because we are adding an extra column. That is sometimes true, but it is only half the picture.
If a natural key consists of three sizeable character columns, every foreign key may have to repeat all three. Replace that with a single BIGINT and the dependent tables — and their indexes — can become substantially narrower.
On the other hand, the parent table may now need both:
so the database is maintaining technical identity and business uniqueness separately.
The actual storage and indexing trade-off depends on the width of the natural key, the number of child tables, the indexes being maintained, the workload, and the database engine.
Joins follow a similar pattern.
versus:
The second is simpler to write, simpler for an ORM to map, and often cheaper to index and compare when the surrogate is a compact numeric value.
I would still avoid the blanket statement that surrogate-key joins are always faster. Cardinality, data types, statistics, indexing, query plans and workload all matter. But when the natural alternative is long or composite, a narrow surrogate usually gives the database and application a simpler join path.
Application Frameworks Changed the Equation Too
Database design today sits inside a larger software ecosystem: ORMs, REST APIs, caches, generic repositories, code-generation tools, event streams and distributed services.
A single immutable identifier fits these patterns very naturally. Object identity is easier. Generic CRUD code is easier. Cache keys are easier. API paths are easier.
That convenience is real, and it is one reason surrogate keys became so common.
But it is worth keeping one guardrail in mind:
Frameworks change much faster than enterprise data does.
Surrogate Does Not Mean Auto-Increment Integer Anymore
For years, surrogate key almost automatically meant a sequence such as 1, 2, 3, 4...
That is still a perfectly good choice in many systems.
Distributed architectures have simply added more options. UUIDs and other generated identifiers can be created without relying on one central database sequence, which is useful when identity has to be generated across services or nodes.
That creates another design decision. A UUID has different generation and distribution properties from a sequential BIGINT, but it is also wider and can behave differently in indexes and operational debugging.
So even after deciding to use a surrogate key, the work is not finished. We still have to decide what kind of surrogate key makes sense for this system.
Where I Would Still Happily Use Natural Keys
I do not believe every entity requires a fabricated identity.
For small reference entities with a short, standard and genuinely stable identifier, a natural key can still be the cleanest model.
Would adding CurrencyID = 47 make this design better? Possibly, if the surrounding architecture has a reason for it. But I would not add it simply because every table is expected to have an ID.
I am most comfortable with a natural primary key when the identifier is:
Where I Strongly Prefer Surrogate Keys
At the other end are long-lived enterprise entities: customers, employees, suppliers, orders, invoices, assets, accounts, and products that may exist across multiple systems.
A customer can easily accumulate several identifiers during its lifetime:
Which one should define the physical identity of that row for the next fifteen years?
Often, none of them.
That is exactly where an internal surrogate identity earns its place.
And Then Came the AI Layer
This is where an old database-design discussion becomes unexpectedly current.
AI does not change relational theory. A primary key is still a primary key, and a foreign key is still a foreign key.
What AI changes is the amount of context we try to assemble from different systems at the same time.
That is an enterprise identity problem, and it sits underneath a surprising number of AI use cases.
A retrieval system may find three customer records. An AI agent may pull transactions from an ERP and interaction history from a CRM. A customer-service assistant may combine profile, booking, payment and communication data.
If identity resolution is weak, the AI layer may receive incomplete, duplicated or conflicting context.
The AI layer may be new. The data problem underneath it is not.
The Hybrid Approach Has Usually Served Me Best
Having worked with both approaches, I do not see natural versus surrogate keys as an ideological choice.
My preference is usually hybrid.
For a long-lived business entity, I am comfortable using a surrogate key as the stable technical identity. At the same time, I still want the natural or business key to be explicitly identified and protected.
For a small reference entity with a truly stable and meaningful natural identifier, I may simply use the natural key.
The decision should follow the domain and the lifecycle of the entity — not an ORM default, a database fashion, or a blanket architecture rule.
A Few Questions I Would Ask in a Design Review Today
And one question I would add today that I would probably not have asked in the same way twenty years ago:
Closing Thought
Natural keys appeal to me because they emerge from the business. Surrogate keys are powerful because they allow system identity to survive changes in that business.
I have used both approaches over the years and still see good reasons for both.
What I would avoid is turning either approach into a rule that no longer requires thought.
The primary-key decision determines how a database identifies a row.
The bigger architecture decision is how the enterprise identifies the thing that row represents.
In increasingly connected, data-driven and AI-enabled enterprises, the second question is becoming harder — and more important — than ever.
A condensed version of this article is available on LinkedIn. For more insights on data architecture, AI system design and enterprise technology leadership, visit kmchronicle.com.
