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]

.

Relevant Pages

  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... room INTEGER NOT NULL, ... PRIMARY KEY (teacher, class, room, period)); ... The simple matter of the fact is that on revisting the database with the natural key you obtained has changed and points to completely different data. ... Tony Rogerson, SQL Server MVP ...
    (comp.databases.theory)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... changes - at least not as far as the data model is concerned. ... school year while on a term break the teacher gets married and is now called ... If the database is correctly updated to reflect any name changes then ... These are all problems with using the natural key - the natural key changes! ...
    (comp.databases.theory)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... Tony Rogerson, SQL Server MVPhttp://sqlblogcasts.com/blogs/tonyrogerson ... You didn't specify any reason why you thought Joe's design was wrong. ... changes - at least not as far as the data model is concerned. ... the data need a way to identify their teacher but how would an ...
    (comp.databases.theory)
  • Re: name of field is in variable: how to use in SQL?
    ... > but you are not my teacher and i'm not your student. ... >> Did you try my suggestion of response.writing the sql variable and ... >> quicker response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... room INTEGER NOT NULL, ... PRIMARY KEY (teacher, class, room, period)); ... in the real world how would the history work? ... Tony Rogerson, SQL Server MVP ...
    (comp.databases.theory)