Merlin Moncure wrote:
> Using surrogate keys is dangerous and can lead to very bad design
> habits that are unfortunately so prevalent in the software industry
> they are virtually taught in schools. ... While there is
> nothing wrong with them in principle (you are exchanging one key for
> another as a performance optimization), they make it all too easy to
> create denormalized designs and tables with no real identifying
> criteria, etc,...
Wow, that's the opposite of everything I've ever been taught, and all my experience in the last few decades.
I can't recall ever seeing a "natural" key that was immutable. In my business (chemistry), we've seen several
disasteroussituations were companies picked keys they thought were natural and immutable, and years down the road they
discovered(for example) that chemical compounds they thought were pure were in fact isotopic mixtures, or simply the
wrongmolecule (as analytical techniques improved). Or during a corporate takeover, they discovered that two companies
usingthe same "natural" keys had as much as 10% differences in their multi-million-compound databases. These errors
ledto six-month to year-long delays, as each of the conflicting chemical record had to be examined by hand by a PhD
chemistto reclassify it.
In other businesses, almost any natural identifier you pick is subject to simple typographical errors. When you
discoverthe errors in a field you've used as a primary key, it can be quite hard to fix, particularly if you have
distributeddata across several systems and schemas.
We've always recommended to our customers that all primary keys be completely information free. They should be not
basedon any information or combination of information from the data records. Every time the customer has not followed
thisadvice, they've later regretted it.
I'm sure there are situations where a natural key is appropriate, but I haven't seen it in my work.
Craig