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

On 14 Dec, 17:07, "Tony Rogerson" <tonyroger..._at_torver.net> wrote:
> > 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.sm..._at_fred.com'
>
> On Tuesday she gets married and changes her email address to
> 'julie.jo..._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.sm..._at_fred.com only that email address no
> longer exists so I can no longer communicate with her.
>
> --
> Tony Rogerson, SQL Server MVPhttp://sqlblogcasts.com/blogs/tonyrogerson
> [Ramblings from the field from a SQL consultant]http://sqlserverfaq.com
> [UK SQL User Community]

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.

--
David Portas