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

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 tonyrogerson_at_sqlserver.eu.com everybody sends email to that address.

insert blah values( 'tonyrogerson_at_sqlserver.eu.com' )

On 1st Jan 2007 I change the email address to tonyrogerson_at_torver.net I suddenly stop receiving emails because everybody is still emailing tonyrogerson_at_sqlserver.eu.com.

update blah set emailaddress = 'tonyrogerson_at_torver.net' where emailaddress = 'tonyrogerson_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.

-- 
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]