On 4/16/07, Greg Smith <gsmith@xxxxxxxxxxxxx> 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
