Re: Normalization, Natural Keys, Surrogate Keys

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u8z6ilw51.fsf_at_grossprofit.com>...
> On Fri, 17 May 2002, comedyharris_at_hotmail.com wrote:
>
> >> A natural unique index should suffice. Then a generated key
> >> makes for a much easier to understand and code to model, (in
> >> my opinion). Restrict on the natural fields to restrict on
> >> but join on the generated keys. To me, this just seems a
> >> whole lot easier to understand and code to, which makes for
> >> more successful projects.
> >
> > Would there be anything wrong with doing this the other way
> > around? Making the primary key the natural key, but then
> > creating a surrogate key, which is an auto-number column with a
> > unique constraint on it? This way, you keep the model
> > 'correct', but provide development ease with your candidate key
> > column.
>
> Hm... I think everybody joins tables by looking at the keys.
> This would seem to confuse the issue to me. Also, you lose that
> the database would maintain the key relationships once you've
> created the surrogate keys. Once those keys get there, surrogate
> or natural, well, they better stay.
>
> I know I would never use this manuafactured key because I
> wouldn't be able to depend on it. Is it a child to some other
> table? No. It is just some number. I don't think it would ever
> get used.
>
> You sound like you really want to go with natural keys. Go with
> it. It certainly is a sound design.

Kind of jumping in to the end of a discussion here, but...

the surrogate keys look more and more to me like the data base is no longer relational. It smells a lot like a network model database. I'm not saying we should never use surrogate keys. But using the real data can help avoid a lot of other problems as has already been mentioned, primarily "duplicates".

This topic is dear to me right now, because I'm dealing with the clean up of data that uses ID's and we have all the problems associated with this "network model" style of database design: duplicates, invalid relations (the keys relate, but the "natural" data does not), orphans (child without a true parent).

(And this data is supposed to reflect and interface to what is in several other systems, so we also have out-of-sync conditions. at least one of these interfaces is based on a surrogate key which is currently out of wack.)

Yes I know entering several attributes can be time consuming, but I do not think that is a good arguement for using surrogates instead of natural keys. Ad hoc queries always take longer than we want (we all want the answer yesterday, right?).

I am seeing more and more potential for problems this way. I thought we abandoned the network model decades ago.

Sorry for ranting. Have a nice weekend.

   Ed

Edward J. Prochak --- Magic Interface, Ltd. Ofc: 440-498-3700 Cell: 440-666-9013
on the web at --- http://www.magicinterface.com email: ed.prochak_at_magicinterface.com Received on Fri May 17 2002 - 21:13:10 CEST