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

From: Tony Rogerson <tonyrogerson_at_torver.net>
Date: Fri, 14 Dec 2007 17:07:05 -0000
Message-ID: <fjuda5$920$1$8302bc10_at_news.demon.co.uk>


> If the database is correctly updated to reflect any name changes then
> no problem arises. If there is a requirement to reflect the history of
> previous names then that information can also be recorded. In either
> case I don't see any call for anything like an "artificial key".

CREATE TABLE Schedule
(teacher VARCHAR(15) NOT NULL,
 class CHAR(15) NOT NULL,
 room INTEGER NOT NULL,
 period INTEGER NOT NULL,
 PRIMARY KEY (teacher, class, room, period));

Does the above do that? No.

Part 2; in the real world how would the history work?

Perhaps this....

CREATE TABLE Schedule
(teacher VARCHAR(15) NOT NULL,
 class CHAR(15) NOT NULL,
 room INTEGER NOT NULL,
 period INTEGER NOT NULL,
start_date datetime not null,
end_date datetime null check( end_date > start_date ),  PRIMARY KEY (teacher, class, room, period, start_end, end_date ));

Nope, I still cannot find my teacher (SELECT * FROM Schedule WHERE teacher = 'Ms Fred'); all I know now is that my teacher no longer is a teacher which is wrong.

How would you store the history given that the natural key changes WITHOUT using an artificial key?

> I don't understand what would prevent you from updating the email
> address in the database. The choice of key should have nothing to do
> with it.

The real world gets in the way.

Upto and including Monday the person has the email address 'julie.smith_at_fred.com'

On Tuesday she gets married and changes her email address to 'julie.jones_at_fred.com'

Do you not see a problem with that if the designer had followed the same approach above.

I keep trying to email julie.smith_at_fred.com only that email address no longer exists so I can no longer communicate with her.

-- 
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]
Received on Fri Dec 14 2007 - 18:07:05 CET

Original text of this message