Re: Normalization, Natural Keys, Surrogate Keys

In message <e51b160.0205171113.6ba24063_at_posting.google.com>, Ed prochak <ed.prochak_at_alltel.com> writes

>> 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).

The surrogate key has its uses (others disagree on that) but you do have to recognise that there are risks. Any move away from the logical data structure has a risk. It's perfectly possible to take two copies of the same datum and attach two different surrogate key values. If you allow this to happen then your data has been corrupted, and corrupted in a way that's not immediately obvious.

Creating a surrogate key does not absolve you from the need to identify a true key if it exists, and to check its uniqueness in your database.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti