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 disasterous situations 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 wrong molecule (as
analytical techniques improved). Or during a corporate takeover, they discovered that two
companies using the same "natural" keys had as much as 10% differences in their
multi-million-compound databases. These errors led to six-month to year-long delays, as each of
the conflicting chemical record had to be examined by hand by a PhD chemist to reclassify it.
In other businesses, almost any natural identifier you pick is subject to
simple typographical errors. When you discover the errors in a field you've
used as a primary key, it can be quite hard to fix, particularly if you have
distributed data across several systems and schemas.
We've always recommended to our customers that all primary keys be completely
information free. They should be not based on any information or combination
of information from the data records. Every time the customer has not followed
this advice, 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
