From: Merlin Moncure <mmoncure@gmail.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Basic Q on superfluous primary keys
Date: Tue, 17 Apr 2007 12:17:54 -0400
Message-ID: <b42b73150704170917n33ebcdc8wc852a3fa02fd8bb5@mail.gmail.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0704162239300.7058@westnet.com>
References: <c2350ba40704140419r2f3c85dfi60171759b71f4db7@mail.gmail.com>
<20070414075947.e5fbeec8.wmoran@collaborativefusion.com>
<b42b73150704160603x1d164134pfd2af7481f802ed9@mail.gmail.com>
<46239000.7000708@modgraph-usa.com>
<b42b73150704160855s676a8c43n7ef4282f5ef175c9@mail.gmail.com>
<Pine.GSO.4.64.0704162239300.7058@westnet.com>
On 4/16/07, Greg Smith <gsmith@gregsmith.com> wrote:
> I think the point Craig was trying to make is that what you refer to here
> as "extraordinary cases" are, in fact, rather common. I've never seen a
> database built on natural keys that didn't at some point turn ugly when
> some internal or external business need suddenly invalidated the believed
> uniqueness of that key.
I don't think it's so terrible to add a field to a key...I too have
worked on a ERP system based on natural keys and was quite amazed on
how well organized the database was. When the company decided to
re-number all the items in the database, it was a minor pain.
Extending a critical key would be a project any way you organize the
database IMO. Natural keys are most common in manufacturing and
accounting systems because of the COBOL heritage, when natural keys
were the only way to realistically do it. Unfortunately SQL really
missed the boat on keys...otherwise they would behave more like a
composite type.
> The last really bad one I saw was a manufacturing database that used a
> combination of the customer code and the customer's part number as the
> key. Surely if the customer changes their part number, we should switch
> ours to match so the orders are easy to process, right? When this got fun
> was when one large customer who released products on a yearly schedule
> decided to change the bill of material for many of their parts for the new
> year, but re-used the same part number; oh, and they were still ordering
> the old parts as well. Hilarity ensued.
In the context of this debate, I see this argument all the time, with
the implied suffix: 'If only we used integer keys we would not have
had this problem...'. Either the customer identifies parts with a
part number or they don't...and if they do identify parts with a
number and recycle the numbers, you have a problem...period. Adding a
integer key only moves the confusion to a separate place, unless it is
used by the user to identify the part number and then *becomes* the
key, or a part of it. If you hide the id from the user, then I claim
the data model is pretty much busted.
merlin
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: mmoncure@gmail.com, gsmith@gregsmith.com
Subject: Re: Basic Q on superfluous primary keys
In-Reply-To: <b42b73150704170917n33ebcdc8wc852a3fa02fd8bb5@mail.gmail.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