Re: Surrogate Key vs Production Key

wizofoz2k_at_yahoo.com.au (Noons) wrote in message news:<73e20c6c.0410140055.2690e121_at_posting.google.com>...
> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<RYGdnZkHtp-rsfDcRVn-og_at_comcast.com>...
> > big difference -- perhaps the entire reason for apparent disagreement

[lots of stuff deleted.
 A good portion of which I actually agree with you, Noons]

>
> > My comments were specific to the data modeling phase (conceptual model) but
> > discovery does continue during initial implementation as well as ongoing use
> > of the data. No analyst, and no data model, perfectly captures every nuance
> > of a business at the first take -- new discoveries typically clarify earlier
> > incorrect, arbitrary or misconceived ideas.
>
> There is a recommendation then to use surrogate keys if I ever
> saw one. If you can't be 100% sure you have discovered all
> the nuances and possible relationships, how can you then define your
> tables with a potentialy complex relationship tree with natural keys?
> How can you now use PKs based on those natural keys in extensive
> FK/PK trees? What will happen to the FKs first time you need
> to change the PK because your analysis discovered a hidden natural
> key?

I'm puzzled about this point. IF you are not 100% sure about the relationship, how do you change it in a model with surrogate keys. Note: I'm really curious.

>
> > application, not peak during the initial analysis. (I know that last comment
> > leaves me open to criticism of doing insufficient analysis, but the careful
> > reader will see that my point is that more will always be learned as the
> > application is used and evolves, new observations and discoveries are not
> > automatically incorrect).
>
> There is no such criticism. No one can expect a prescient knowledge
> about a complex system of interactions. The maths alone to allow for
> that, would make most systems not viable economically.
> What is expected however is a flexible design that can be adapted
> with minimal impact to new discoveries about that system. And
> surrogate keys lend themselves perfectly to that.

Can you give an example of how you handled this? How does Surrogate keys fit in a flexible design in a greater way than Natural keys? How about this for raw, very high level entities: Customer, PhoneNumber, Address. If you were working on a billing system DB for the phone company how might you make a model that allowed change while using surrogate keys? How would an equivalent natural key model not be as flexible?

>
> >
> > since i presented a hypothetical case, i covered two possible ways the
> > business rule could be viewed by the sponsoring organization. in real life,
>
> You see, this is where I disagree with all the arguments for the use
> of
> natural keys. There is not ONE example put forward by their defenders
> that doesn't start with some assertion involving "poor", or "weak", or
> "incorrect" or words to that effect. Which immediately
> presumes that there should be an easy proof of the superiority of
> natural
> keys.

So turn the tables. Present an example the exposes the real Strength of Surrogate Keys.
>
> Yet when I follow the logic exposed, WITHOUT exception, a fault
> crops up that makes the whole example inconsistent with the
> exclusive use of natural keys. And when I point this out,
> again invariably the reply is: it is only hypothetical.
>
> Well, if even hypothetically no one can provide me with a
> concrete example that simply demonstrates: "here, you cannot
> do this with surrogates, that is why you should be using
> natural keys", I'm sorry but I remain unconvinced.
>

My comments have never been that cases exist where natural keys work and surrogates cannot work. Actually my argument comes more from my embedded programming experience, especially communications. There one key rule I try to follow is: it's not how well it works when it works, it's how well it works when it doesn't work that matters.

So show me the inherent superiority of Surrogate keys. Received on Thu Oct 21 2004 - 13:29:52 CDT