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

On 14 Dec, 17:51, "Tony Rogerson" <tonyroger..._at_torver.net> wrote:
> First of all, let's go back to your statement about just store the history -
> can you please show how you would do that with celko schema and still be
> able to reach the rows as per my examples?
>
> > 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.
>
> Here's another...
>
> create table blah (
> emiladdress varchar(?) not null unique,
> who varchar(100) not null
> )
>
> In the year 2006 I have an email address called
> tonyroger..._at_sqlserver.eu.com everybody sends email to that address.
>
> insert blah values( 'tonyroger..._at_sqlserver.eu.com' )
>
> On 1st Jan 2007 I change the email address to tonyroger..._at_torver.net I
> suddenly stop receiving emails because everybody is still emailing
> tonyroger..._at_sqlserver.eu.com.
>
> update blah set emailaddress = 'tonyroger..._at_torver.net' where emailaddress
> = 'tonyroger..._at_sqlserver.eu.com'
>
> How do all the applications disconnected from the database now reach the
> record? They can't - the natural key has changed.
>

You are saying that this is a distributed database but that it lacks a mechanism for accurately propagating changes out to all its nodes? Well in my view such a DBMS would be broken. It surely violates Codd's principle of "Distribution Independence". Let's follow your example to its conclusion though. The solution is to replace whatever copy of the Blah relation exists in the application with the new Blah relation that superceded it. Now all emails reach the correct address and there is no problem that requires a different key.

--
David Portas