Re: Normalization, Natural Keys, Surrogate Keys

   > * we can efficiently join across columns (assuming a NUMBER(16) in
> Oracle or an INT in Sybase), we're looking at four bytes, and
> * our implementation is independent of the business requirements
> without losing the business requirments
> * our index density is very high so index range scans/'covering an
> index' is extremely efficient
>
> What's key (pun intended! <go>) is that surrogate keys and natural
> keys can peacefully co-exist. Kumbaya, kumbaya ...

I suppose there's 3 things I'm concerned about here:

1. By making the parent id a foreign key, it seems like you're losing information in the design. More specifically, a child is related to a parent, but not dependent on it for it's existance. So here, the business requirements are not well comunicated through the design?
2. Everywhere I go people are trading off performance over everything else. Ok, I admit I'm a little touchy on the subject, but 2 of your 3 points were directly related to performance. In my limited experience, I've seen more projects fail due to 'understandability' than performance.
3. Hymns in database discussion groups. Now that's definately violating some integrity consraints!?

I'm keen to learn more, so how can your 'surrogate key' version communicate the same thing as a composite primary key? I now have to examine the parent/buesiness rules to realise what it's 'real ' key is, since you've replaced it with a surrogate key. BTW, I'm not going to argue about the performance issues, although database vendors could probably create their own surrogate keys internally to replace the composite key. Which would remove the performance probs. (I'm thinking on the fly here, so be gentle)

Tobin Harris

> Pablo Sanchez, High-Performance Database Engineering
> mailto:pablo_at_hpdbe.com
> http://www.hpdbe.com
> Available for short-term and long-term contracts
>
>
Received on Thu May 16 2002 - 18:03:02 CDT