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

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@xxxxxxxx'

On Tuesday she gets married and changes her email address to 'julie.jones@xxxxxxxx'

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

I keep trying to email julie.smith@xxxxxxxx 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]

.

Relevant Pages

  • Re: Student tapes teacher proselytizing in class
    ... A Kearny High School student has accused a history teacher of crossing ... Junior Matthew LaClair, 16, said history teacher David Paszkiewicz, who ...
    (talk.origins)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... room INTEGER NOT NULL, ... PRIMARY KEY (teacher, class, room, period)); ... in the real world how would the history work? ... For methods of recording the history of change Date, ...
    (comp.databases.theory)
  • Re: CA Court ruling
    ... a history teacher violated a student's rights under the First ... he said that creationism is "religious, superstitious nonsense"? ... one exception of the remark about creationism. ...
    (talk.origins)
  • Re: CA Court ruling
    ... that a history teacher violated a student's rights under the First ... Amendment when he said that creationism is "religious, ... argument about religion. ... the allowed statements were all made in the context of a valid history ...
    (talk.origins)
  • Re: Student tapes teacher proselytizing in class
    ... A Kearny High School student has accused a history teacher of crossing ... Junior Matthew LaClair, 16, said history teacher David Paszkiewicz, who ...
    (talk.origins)