Re: Normalization, Natural Keys, Surrogate Keys

"Ed prochak" <ed.prochak_at_alltel.com> wrote in message news:e51b160.0205171113.6ba24063_at_posting.google.com...
> 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:
> >
> > 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?).

Hi Ed,

Seems like the issues that you're seeing could have been caused regardless of whether surrogate keys were used or not. Seems like what should have been done are the following items:

• UNIQUE index on the natural key
• Implement RI to ensure no orphaned rows and ensure valid relationships

Perhaps I'm missing something obvious.

Thx!

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts