About
This article talks specifically about the choice of primary key between a
- and natural key
TLDR
surrogate key are the way to go for a primary key
Quick definition
- A surrogate key is a key that your system generates. You will find in the form of a Sequence (generated by database) or guid (generated outside the database)
- A natural key is a key that is not generated by your system such as SSN, VIN, VAT. All business key are natural key
Pros/Cons of using a surrogate key
Pros
- Gain on IO - a surrogate key is way smaller to store than a natural key. The gain will be in:
- the internal table
- in the external table (outside a database or just via foreign key)
- Immutable: As a surrogate is immutable and have no meaning, it will never changes. Therefore, if the unique key of a (external|dimension) table changes, you don't need to modify it
Cons
- You can't search directly on the business key in an foreign/external table.
- You need to do an extra action to get the primary key (join in SQL or show the information on the screen)