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.
- 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).
- 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
[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
Conclusion: I'll still use surrogate keys but try to avoid them whenever possible if there is an easy natural key.