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

> If teachers are permitted to change their names, then names are not
> appropriate keys for teachers. They are (excuse me if I get the
> language wrong) mutable attributes of these entities. "Ms. Fred"
> *isn't* exactly the same person as "Mrs. Sid" -- she's married now,
> she has a fancy new diamond ring, new taxable status, etc., etc. She
> shares history with Mrs. Sid -- but an omelet shares history with an
> egg, too.

From my perspective as a student she is the same person, to say otherwise is actually changing the scenario modelled but you are heading in the right direction.

>
> If marital status is not significant to your model of teachers and
> classes, then you should choose a key that is not affected by a change
> of marital status. As another example in the USA, the SSN is not
> affected by a change of name, but the driver's license number
> generally is!

SSN is illegal so you can't do that in the USA.

So, what would you use if that is not and nothing else is available to you?

Would you a) tell the university that the data cannot be modelled and you can't hold it in a database or b) use an artificial key?

My choice is b) and it's what I've been talking about.

-- 
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]