From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Craig A. James <cjames@modgraph-usa.com>
Subject: Re: Basic Q on superfluous primary keys
Date: Mon, 16 Apr 2007 11:24:15 -0700
Message-ID: <4623BF4F.5070308@cheapcomplexdevices.com> (raw)
In-Reply-To: <46239000.7000708@modgraph-usa.com>
References: <c2350ba40704140419r2f3c85dfi60171759b71f4db7@mail.gmail.com>
<20070414075947.e5fbeec8.wmoran@collaborativefusion.com>
<b42b73150704160603x1d164134pfd2af7481f802ed9@mail.gmail.com>
<46239000.7000708@modgraph-usa.com>
Craig A. James wrote:
> 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.
>
> ...chemistry...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.
That sounds almost like a feature, not a bug - giving information
about what assumptions that went into the "natural key" need to be
reconsidered.
And I don't see how it would have been improved by adding a surrogate
key - except that the data would have been just as messed up though
harder to see where the messups were.
> 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.
Hmm... but then do you put a unique index on what the
otherwise-would-have-been-natural-primary-key columns?
If not, you tend to get into the odd situation of multiple
rows that only vary in their surrogate key -- and it seems
the surrogate key is redundant.
> I'm sure there are situations where a natural key is appropriate, but I
> haven't seen it in my work.
I've seen both - and indeed usually use surrogate keys for convenience;
but also find that having to fix incorrect assumptions in natural primary
keys tends to raise business issues that are worth addressing anyway.
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: rm_pg@cheapcomplexdevices.com, cjames@modgraph-usa.com
Subject: Re: Basic Q on superfluous primary keys
In-Reply-To: <4623BF4F.5070308@cheapcomplexdevices.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