Re: Surrogate Key vs Production Key

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u655hmml9.fsf_at_standardandpoors.com>...

> Maybe someone needs to define a "natural key". Seems to me, it
> is a key that some other system has already defined.

The actual choice of a natural key can be in fact a very nasty problem. And by no means easy. Some times what appears to be a sensible choice turns up to be a disaster a few months down the line.

Joe actually had some good ideas about this a while ago, I do recall reading something about it in c.d.t if memory doesn't fail me. But do a search on google on the keywords I mentioned in another reply and you'll see a lot of examples \ of problems that can catch you unaware.

The day Oracle and other databases fully support the r-model and we can lose the surrogates will be the day this problem becomes acute. And a few others also explained in much of the literature on the subject. Not the least of which is how to cope with later changes to a data model with minimal data maintenance.

Not to say that surrogates don't have their twists either. Codd's warnings are ALWAYS pertinent, of course. Specific to Oracle is: which is best, to use one single sequence for all tables or one sequence for each table? I've never made my mind on that one, so I usually ask someone else to make the decision for me. More times than not I get one sequence per table, which I feel is an "overkill". However, I guess that is the most used solution. Not necessarily the best? Received on Tue Oct 12 2004 - 04:37:25 CDT