Re: Basic Q on superfluous primary keys

On 4/16/07, Greg Smith <gsmith(at)gregsmith(dot)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