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

On 14 Dec, 15:49, "Tony Rogerson" <tonyroger..._at_torver.net> wrote:
> > 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?

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".

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

--
David Portas