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

> You didn't specify any reason why you thought Joe's design was wrong.
> There is no obvious reason for confusion just because a teacher's name
> changes - at least not as far as the data model is concerned. Users of
> the data need a way to identify their teacher but how would an
> "artificial key" help? I never knew any of my teachers as 1234.

I thought I had.

On my printed off time table it states 'Ms Fred', now, half way through the school year while on a term break the teacher gets married and is now called 'Mrs Sid' - how does my timetable now link back to the original entity - it can't, now, I return back from my holiday and wander around campus looking for a teached called 'Ms Fred' but to no avail.

Now, a new teacher starts towards the end of the school year, called 'Ms Fred', this is a completely different person from the one at the start of term, in fact they teach cooking rather than engineering; now, my timetable correctly links up and I can find my teacher - 'Ms Fred', only, the problem is I'm at the wrong class - I no longer recognise the subject content - what breaking eggs and cooking cakes has to do with engineering?

Do you see my point yet? and no, don't start talking about it being identified by classroom - I remember when I was at FE the room we where taught in, in fact the building sometimes often changed from week to week

Another one is somebody I do business with in Microsoft for the community, she recently got married - she cannot change her email address because if she did when we emailed her it would bounce because we would be using the old one.

These are all problems with using the natural key - the natural key changes! You need to create an imutable key; never changes so these real problems can not and do not occur.

I'm interested - how would you solve this problem in the real world? Would you prevent the teacher from changing their name?

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