Josh Berkus wrote:
> Given: Surrogate keys, by definition, represent no real data;
> Given: Only items which represent real data have any place in
> a data model
> Conclusion: Surrogate keys have no place in the data model
But, once a surrogate key is assigned to a row, doesn't it become a
"real" data? For example, I have a bunch of invoices/receipts and I
write down a unique number on each of them. Doesn't the unique number
become part of the information contained by the invoice/receipt itself
(at least as long as I'm concerned)? Another example is when Adam
(+Eve?) named each animal in the Genesis. At that time the name he gave
for each animal was arbitrary ("surrogate"), but once assigned to each
species, it becomes part of the real data.
> 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and
Change management IMO is perhaps the main reason of surrogate/artificial
key. We often need a PK that _never_ needs to change (because it can be
a royal PITA or downright impossibility to make this change; the PK
might already be printed on a form/card/document, recorded on some
permanent database, tattoed/embedded in someone's forehead, etc).
Meanwhile, every other aspect of the data can change (e.g. a person can
change his name, sex, age, email, address, even date & place of birth).
Not to mention data entry mistakes. So it's impossible to use any
"real"/natural key in this case.
> Now, you're probably wondering "why does this guy regard surrogate keys as a
> problem?" I'll tell you: I absolutely cannot count the number of "bad
> databases" I've encountered which contained tables with a surrogate key, and
> NO REAL KEY of any kind. This makes data normalization impossible, and
> cleanup of the database becomes a labor-intensive process requiring
> hand-examination of each row.
Okay, so surrogate key makes it easy for stupid people to design a
database that is prone to data duplication (because he doesn't install
enough unique constraints to prevent this). But I don't see how a
relation with a surrogate key is harder to "normalize" (that is, for the
duplicates to be removed) than a relation with no key at all. Compare:
street------Green StreetGreen StreetGreen Street
versus:
id street-- ------2934 Green Street4555 Green Street5708 Green Street
They both contain duplicates and/or ambiguous data. They're both equally
hard to normalize/clean.
--
dave