6 Jan 2005

Database: natural vs surrogate key

When I fisrt started to use database, my teacher taught me about natural key (like Social Security Number) and then warn us about not making too much use of it (example of bad use: primary key is street + postal code + town + phone number to define a unique address) .
Then he introduced surrogate key (usualy a unique integer like AddressID).

My experience of database lead me to use mainly surrogate keys... until recently! where it seems that surrogate key might be a problem in some case.

Exemple:
  • Problem 1: your application/database need to import/export/communicate data to another application/database for exemple using a flat file between the 2 systems.
  • Problem 2: As always the 2 databases strutures are differents (concepts-features-tables-cardinality).
Question: What can you do? What problems do you have?
  • If you used surrogate keys:
    • You can send your AddressID to the other system but what will it do with it? The other system surly will aslo have PK_Address_Id of his own, it won't be able to insert your Id into his -> not able to keep unicity
    • You can try to send only natural keys (Social Security Number), but since it is not you primary key you can export 2 lines that seems identical but have a different Id...
  • If you used natural keys:
    • It seems easer to export Social Security Number into a database struture with ID + SSN than the other way
Reading the excellent agile data site, about this subject:
[Using natural keys]... The implication is that you want to avoid keys with business meaning because business meaning changes.
I tend to agree with him that surrogate keys are somehow still better since queries are easer and refactoring more flexible.
Also using natural or surrogate keys don't avoid problems about concepts such as:
  • system n°1: n Users owns 1 Order
  • system n°2: n Users owns n Orders
In that case I don't see any solution except changing one of the two system.

Conclusion: I'll still use surrogate keys but try to avoid them whenever possible if there is an easy natural key.

1 comment:

Joe said...

Great, thanks for the nice post!

There's a nice explanation on this page too:

Database natural keys