This is the great synthetic-vs-natural key debate.
Truly. But what the heck!
Surrogate keys are not evil, and they do have value. I see no value in proclaiming "surrogate keys are evil, do not use them".
Surrogate keys do have advantages:
- Disassociation of natural data to other referential tables (which can also be "confusing")
Imagine a social security number, drivers license number, or any other natural key. Now imagine that
key value has changed for a specific person, and you have used it as a natural key throughout your data
structures. (and they do change)
- Reduced storage requirements (yields better performance)
It is cheaper to store a 50 byte field + a 4 byte surrogate key once, then it is to store it a million times:
(surrogate key) 54 bytes + (4 bytes * 1 million) = 4MB
vs.
(natural key) 50 bytes * 1 million = 50 MB
Natural keys are not evil either, and they have their own advantages. But when your modeling very large databases (around 10TB range) then you absolutely have to consider every single decision, and natural keys (in my opinion) is not always a good one as a single natural key could result in another 100GB of storage requirements.
There should be some thought when you are modeling and these are some of the things to consider. I don't see a 10 table join being a major performance penalty, especially when 8 of the tables may be a few MB in size.
--
Chad
http://www.postgresqlforums.com/
