Reject a smart key
Recognize why encoding business meaning into a primary key is an anti-pattern.
Concept
surrogate-keys
The primary modeling idea this problem reinforces.
Requirements
1
Business needs the model must satisfy.
A team proposes a product key like "ELEC-2026-0042" that encodes category, year, and sequence, planning to parse it in queries. Categories get renamed and products get recategorized over time.
Smart keys couple identity to mutable business meaning. When the encoded facts change, the key either lies or has to change, both break referential integrity. A meaningless surrogate stays stable.
- Pick the key strategy that keeps identity stable as business meaning changes.
- The answer chooses a meaningless surrogate key.
- The explanation moves the encoded facts to attributes.
- Keys should identify, not describe.
- What happens to "ELEC-..." when the product is recategorized?
- Parsing meaning out of a primary key in queries.
- Changing the key when the encoded category changes.
- Treating a smart key as both identity and description.
Meaningless surrogate key
Use a meaningless surrogate primary key and keep category, year, and sequence as descriptive attributes that can change without touching identity. A Kimball surrogate key.
Optimizes for
- • Stable identity
- • Safe recategorization
- • Referential integrity
Trade-offs
- • Human-readable codes move to attributes
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.