Data Modeling - Surrogate (Sequence, GUID) vs Natural key (Business Key)

About

This article talks specifically about the choice of primary key between a

• surrogate
• and natural 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)
  • in join operation
• 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)

Documentation / Reference

• what-should-i-choose-for-my-primary-key