From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Subject: Re: Basic Q on superfluous primary keys
Date: Mon, 16 Apr 2007 11:24:15 -0700
Organization: Hub.Org Networking Services
Lines: 45
Message-ID: <4623BF4F.5070308@cheapcomplexdevices.com>
References: <c2350ba40704140419r2f3c85dfi60171759b71f4db7@mail.gmail.com>	
	<20070414075947.e5fbeec8.wmoran@collaborativefusion.com>
	<b42b73150704160603x1d164134pfd2af7481f802ed9@mail.gmail.com>
	<46239000.7000708@modgraph-usa.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
To: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
In-Reply-To: <46239000.7000708@modgraph-usa.com>
To: pgsql-performance@postgresql.org

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.