Chapter 02 · Entities, Attributes, and Identifiers
Natural Keys Versus Surrogate Keys
Compare natural and surrogate keys using stability, size, privacy, integration, and lifecycle tradeoffs instead of treating either strategy as universally correct.
Learning outcomes
After discovering candidate keys, designers still need to decide how entities should be referenced internally. Some systems use a meaningful business key directly. Others introduce a database-generated or application-generated surrogate key. Neither choice is automatically correct. The decision depends on stability, width, privacy, integration, lifecycle, and operational requirements.
Define natural and surrogate keys without treating them as mutually exclusive.
Compare stability, size, privacy, distribution, and integration tradeoffs.
Preserve business uniqueness when using surrogate primary keys.
Choose a defensible key strategy for WorkshopHub entities.
Natural keys
A natural key is an identifier that already has business meaning outside the database implementation. Examples can include an ISO country code, a stable catalog code, a vehicle identification number, or a manufacturer-scoped serial number.
Natural keys can be excellent when they are genuinely unique, mandatory, compact, stable, and safe to expose. A two-character country code is very different from a mutable email address.
Surrogate keys
A surrogate key is introduced primarily for internal identity rather than business meaning. Common forms include integer sequences and UUIDs.
asset_id INTEGER PRIMARY KEYThe value “38192” does not tell a user which asset it represents. That is intentional. It provides a stable internal handle that can remain unchanged when business attributes change.
Surrogate primary key plus natural alternate key
The most common practical pattern is not “natural or surrogate.” It is surrogate internal key plus enforced natural/business uniqueness.
CREATE TABLE part ( part_id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, description TEXT NOT NULL);part_id is convenient for foreign keys. sku remains protected because duplicate SKUs would represent a business error.
Tradeoff matrix
| Factor | Natural key | Surrogate key |
|---|---|---|
| Business meaning | Meaningful to users/integrations | Usually none |
| Stability | Depends on business policy | Designed to remain stable |
| Width | Can be composite or long | Often compact, though UUIDs are wider than integers |
| Privacy | May expose sensitive identifiers | Can avoid embedding private data in relationships/URLs |
| Integration | May match external systems directly | Requires mapping external identifiers |
| Duplicate detection | Enforced by the key itself | Requires separate unique constraints on business identifiers |
When natural keys work well
Natural keys are attractive when they are:
- small;
- guaranteed unique in the correct scope;
- required for every instance;
- stable for the entity's lifetime;
- not sensitive;
- already authoritative across integrated systems.
An ISO currency code such as USD is a good example for a Currency reference entity. Replacing it with an integer and forcing every query to join merely to discover “USD” may add little value.
When surrogate keys are useful
Surrogates are useful when business identifiers are mutable, composite, long, privacy-sensitive, optional, assigned late, or controlled by external organizations whose policies may change.
For WorkshopHub Asset, an internal asset_id is reasonable because manufacturer + serial number may be corrected or may not exist for every asset type. The business uniqueness rule can still be enforced when applicable.
Integer versus UUID surrogate keys
Even after choosing a surrogate strategy, the physical form remains a separate choice.
| Property | Integer sequence | UUID |
|---|---|---|
| Size | Compact | Larger |
| Human readability | Short | Long |
| Decentralized generation | Usually database-coordinated | Easy across distributed producers |
| Predictability | Often sequential | Usually difficult to guess |
| Index locality | Sequential inserts are friendly to B-trees | Depends on UUID version/generation strategy |
This is a physical design decision. Do not allow a UUID-versus-integer debate to distract from the more fundamental question: what makes two business entities the same or different?
Never use a surrogate key to avoid defining business uniqueness
This anti-pattern is common:
CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, email TEXT);If the business says one verified email may belong to only one active account, the schema should express that rule. The generated primary key does not make two rows with the same email represent two valid customers.
Key changes and cascading updates
If a mutable natural key is used as the primary key and many foreign keys reference it, changing the business value can require widespread updates. Relational databases can support cascading updates, but the operational cost and semantic consequences still deserve consideration.
A surrogate key can isolate relationships from mutable descriptive identifiers:
Asset asset_id = 491 -- stable internal identity serial_number = 'SN-9081' -- correctable business attributePublic identifiers and security
Sequential integer keys are not secret. If exposed in URLs such as /work-orders/1001, users may infer record volume or try adjacent IDs. Authorization must never rely on IDs being hard to guess.
Some systems use separate public opaque identifiers while retaining compact internal integer keys. This is a security/API design decision, not a substitute for authorization.
WorkshopHub key decisions
| Entity | Recommended internal key | Business uniqueness to preserve |
|---|---|---|
| Customer | customer_id surrogate | Depends on account policy; email may be unique only if explicitly guaranteed. |
| Asset | asset_id surrogate | (manufacturer_id, serial_number) where known and guaranteed. |
| Part | part_id surrogate | SKU if organization guarantees catalog uniqueness. |
| Technician | technician_id surrogate | Employee number if authoritative. |
| WorkOrder | work_order_id or internal surrogate | Human-facing work-order number may be an alternate key. |
Practice: choose a strategy
Decision exercise
Choose natural primary key, surrogate primary key + unique natural key, or surrogate-only identity for each case. Explain your reasoning.
- Country identified by ISO alpha-2 code.
- User account whose login email can change.
- Product catalog with stable organization-controlled SKU.
- IoT events generated independently on thousands of offline devices.
Review guidance
ISO country code is a strong natural key candidate. Mutable email usually argues for a surrogate user ID plus a uniqueness rule if emails must be unique. Stable SKU can be a natural key or an alternate key depending on integration and foreign-key width. Distributed offline event generation may benefit from UUID-like surrogates, while any source-system event identifier should still be uniquely constrained if duplicate ingestion must be prevented.
Summary and next lesson
Natural keys encode business identity; surrogate keys provide implementation-controlled identity. The best design often uses both: a stable surrogate for relationships plus unique constraints for real business identifiers. The next lesson examines special entity types—weak, associative, and reference entities—that often cause confusion in beginner schemas.
References
- C. J. Date, Database Design and Relational Theory.
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
- PostgreSQL documentation on identity columns, UUID types, and unique constraints.
- SQLite documentation on row identifiers, primary keys, and uniqueness.