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 Street Green Street Green Street versus: id street -- ------ 2934 Green Street 4555 Green Street 5708 Green Street They both contain duplicates and/or ambiguous data. They're both equally hard to normalize/clean. -- dave ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend