From: Craig A. James <cjames@modgraph-usa.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Basic Q on superfluous primary keys
Date: Mon, 16 Apr 2007 08:02:24 -0700
Message-ID: <46239000.7000708@modgraph-usa.com> (raw)
In-Reply-To: <b42b73150704160603x1d164134pfd2af7481f802ed9@mail.gmail.com>
References: <c2350ba40704140419r2f3c85dfi60171759b71f4db7@mail.gmail.com>
<20070414075947.e5fbeec8.wmoran@collaborativefusion.com>
<b42b73150704160603x1d164134pfd2af7481f802ed9@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cjames@modgraph-usa.com, mmoncure@gmail.com
Subject: Re: Basic Q on superfluous primary keys
In-Reply-To: <46239000.7000708@modgraph-usa.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox