From: Merlin Moncure <mmoncure@gmail.com>
To: Craig A. James <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Basic Q on superfluous primary keys
Date: Wed, 18 Apr 2007 10:44:44 -0400
Message-ID: <b42b73150704180744h28d99ebflebe4405b3af5e44d@mail.gmail.com> (raw)
In-Reply-To: <46259937.7030502@modgraph-usa.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>
<b42b73150704170917n33ebcdc8wc852a3fa02fd8bb5@mail.gmail.com>
<46259937.7030502@modgraph-usa.com>
On 4/18/07, Craig A. James <cjames@modgraph-usa.com> wrote:
> Merlin Moncure wrote:
> > 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.
>
> On the contrary. You create a new record with the same part number. You mark the old part number "obsolete". Everything else (the part's description, and all the relationships that it's in, such as order history, catalog inclusion, revision history, etc.) is unaffected. New orders are placed against the new part number's DB record; for safety the old part number can have a trigger that prevent new orders from being placed.
>
> Since the part number is NOT the primary key, duplicate part numbers are not a problem. If you had instead used the part number as the primary key, you'd be dead in the water.
You are redefining the primary key to be (part_number,
obsoletion_date). Now, if you had not anticipated that in the
original design (likely enough), you do have to refactor queries that
join on the table...so what? If that's too much work, you can use a
view to take care of the problem (which may be a good idea anyways).
*you have to refactor the system anyways because you are now allowing
duplicate part numbers where previously (from the perspective of the
user), they were unique *.
The hidden advantage of pushing the full key through the database is
it tends to expose holes in the application/business logic. Chances
are some query is not properly distinguishing obsoleted parts and now
the real problems come...surrogate keys do not remove complexity, they
simply sweep it under the rug.
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, cjames@modgraph-usa.com
Subject: Re: Basic Q on superfluous primary keys
In-Reply-To: <b42b73150704180744h28d99ebflebe4405b3af5e44d@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