Re: Surrogate Key vs Production Key

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<41689eaf$0$23895$afc38c87_at_news.optusnet.com.au>...

>
> It's the last bit I don't get. Are you saying that it would be no problem
> with a surrogate key, because all you'd have to update is a non-key
> "postcode" field?

And the index that guarantees it is a UK. And that's it: end of coding complexity, end of potential source of bugs, end of access contention, end of coding maintenance, end of upgrade complexity. Period.

> I mean, obviously we all agree that updating a primary key is not desirable.
> And if that primary key has been used as a foreign key in many child rows,
> that makes the undesirability worse. But it's the updating of the key
> that's undesirable, not its use in the first place as the primary key.

No, the updating of the key is not undesirable. If it must be updated because the business requires so, then it must be. There is no such thing as "undesirability" in user land. If a design technique makes anything undesirable, then I'm sorry but it is the technique that is the trouble. Not the business requirement to change ANY value.

> If there's a trigger or a procedure that can be invoked to replicate the
> update to all affected child rows, that seems to mitigate the
> undesirability big-time.
> I can't, in short, see that what you are describing is a real argument
> against natural keys. As opposed to a real argument against sloppy coding
> practices that allow sloppy user practices to taint the data in the first
> place.

Arguments, you want? Already supplied a few. Here is more:

One: The overhead they cause. Have you ever tried to write a trigger that cascades for 6 or seven levels down the RI tree? Where you'll be updating PKs made up of the original value plus another 5 or 6 other compound natural keys? Can you imagine the complexity of ensuring locking is done in the right sequence? For the sake of "purity of design", you are telling me that I should increase the risk of contention and potential errors in my database by a very large factor? Exactly and precisely what is the gain here other than a vague "natural keys are better because..."?

Two: You may be a perfect coder that never makes mistakes and is always on call from now on to come and fix the trigger next time I need to add another dependency. I have to make do with coders that live somewhere overseas. And my application design has to be simple enough that any half wit will be able to maintain it. A complex trigger like you describe is no way to do that. The correct way is to use update cascade. Which Oracle does not support. Ergo, the use of a simpler technique. KISS always wins.

Three: Natural keys may also cause a hidden maintenance/upgrade nightmare. Why? Have you ever tried to add an intermediate relationship to a design based on natural keys? Try for example to insert a table/entity to a RI tree of a few levels, somewhere in the middle. Now, all dependents need to have ANOTHER key added to their composite PK, don't they? See what that may cause? The data migration nightmare? Use a surrogate key and the design is dirt easy to modify/add later on. And if you think that designs should be locked in stone and never changed due to the "perfection" of the initial effort, let me tell you: rude awakening coming your way. Ever heard of the next revision? Name one product in the market that doesn't get upgrades and I'll give you a dead product. Make it hard to change the design or increase the complexity of data migration after a change and you're half way there.

You asked for arguments: I just gave you three very major ones, recognised by the industry. In addition to the previous ones. These are not my arguments, they are taken from information freely available on the Net.

You'll have to do a bit more than blanket characterise as sloppy widely used and proven industry practices. Remember: I did NOT invent surrogate keys nor am I their crusader, you got the wrong target right there. Don't confuse pragmatism with evangelism.

I use them because they are effective, get the job done with minimal intrusion and there is a huge body of knowledge that demonstrates to my satisfaction they are the way to go when the database engine does not support the full relational functionality. Even "His Coddness" accepted them as valid, albeit conditionally. Check Joe's quote - if you manage to stay awake until you get to the juicy bit.

You may chose to label anything not relationaly "pure" as sloppy, but to do so you have to demonstrate a better alternative than a complex coding technique and a maintenance/upgrade nightmare. And you still cannot counter why surrogate keys are so widely used other than with adjectives like that one. Even Oracle's own applications use them extensively. Wonder why: "sloppy" design again?

Finally, if you're game to walk into a user site and tell them their practices are "sloppy", you're most welcome. On your own time, though: don't get me involved in it. ;)

One last point: there is no such thing as "tainted" data. There is only data. Relevancy is up to its user. And they (not us) also have the nasty habit of deciding what is pure or not...

> I see you go on to say that cascading updates or deletes would be a
> performance problem. All I can say is, I never had a problem with natural
> keys. Practically and really. The need for cascading updates and deletes
> was rare enough for it not to be a performance issue. And if it wasn't a
> rare occurrence, I would still say that's an application design and coding
> issue, not a data modelling one. (Or perhaps a user training issue).

Sorry, the overwhelming evidence freely available on the Net does not support your contention. You never hitting a problem with natural keys does not mean the problems aren't there. All it means is you haven't hit them yet. They are very real and well documented. You may wish to argue if M$'s IDENTITY is an exceedingly long surrogate key, or if Oracle's sequence should be contiguous or not. Joe certainly seems to be perturbed by these things, even though they got nothing to do with surrogate keys or relational design. But you shouldn't dismiss as sloppy a widely used, documented and proven technique just because you have never hit a problem that justifies its use.

>
> The Royal Parks in London got their grass mown and their shrub beds mulched
> on the basis of the use of natural keys. I think Her Maj might have been a
> synthetic key supporter by now if they hadn't been up to the job.
>

I think Betty Windsor the Corgi breeder, knows as much about natural keys and relational purity as my cat knows about motor vehicles: she wouldn't be able to appreciate the subtleties if one hit her between the eyes...
<g,d&r> Received on Sun Oct 10 2004 - 03:29:03 CDT