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

From: David Portas <>
Date: Fri, 14 Dec 2007 08:13:30 -0800 (PST)
Message-ID: <>

On 14 Dec, 15:49, "Tony Rogerson" <> 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
Received on Fri Dec 14 2007 - 17:13:30 CET

Original text of this message