Re: Newbie question about db normalization theory: redundant keys OK?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 14 Dec 2007 17:21:25 -0400
Message-ID: <4762f3d9$0$5278$9a566e8b@news.aliant.net>

raylopez99 wrote:

> On Dec 14, 9:36 am, David Portas
> <REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
>

>>I genuinely am having trouble understanding what any of this has to do
>>with saying that Joe's design is "wrong". None of the problems you
>>have mentioned are the usual ones given for using "artificial" keys -
>>not that I've seen anyway. I don't doubt that you have some real
>>issues in mind but I don't think you are explaining them very
>>precisely: "The real world gets in the way" tells us nothing about why
>>it would be a problem to update email address X to become email
>>address Y. I just don't see what you are getting at.
>>
>>For methods of recording the history of change Date, Darwen and
>>Lorentzos have a book "Temporal Databases and the Relational Model",
>>which discusses the issues and solutions at length.
>>--

>
> Don't feel obligated to answer in any way, but if you recommend a
> single book for somebody who is coding for fun but has a science
> background that gets into this theory please feel free to state so--if
> it's Date et al let me know.
>
> Also perhaps (and I'm just literally reading this stuff for the first
> time, I absolutely no clue otherwise) the changing of a primary
> compound key is a problem because if you change the key, and it's at
> the top root of a tree of relationships, then the cascades and
> triggers that are affected by changing the primary key are so
> extensive that as a practical matter, for a *** Terabit dB, then
> you'll have to spend a week of maintanence downtime to have the new
> primary key "perculate through" the database (just a thought).
>
> RL

You are confusing logical and physical issues. For example, if all of the 'dependent' or 'downstream' relations are physically clustered with the 'parent', there is no need to propagate anything. Or even any need to store the key in multiple locations regardless how many relations the key logically appears in.

I am not necessarily recommending such clustering. I merely point out the irrelevance of performance with respect to a logical issue. Received on Fri Dec 14 2007 - 15:21:25 CST