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

From: David Cressey <cressey73_at_verizon.net>
Date: Fri, 14 Dec 2007 13:07:20 GMT
Message-ID: <cev8j.6953$xd.6782_at_trndny03>


"raylopez99" <raylopez99_at_yahoo.com> wrote in message news:d89f626e-436d-45b1-8b9d-07e790ba8afe_at_i29g2000prf.googlegroups.com...
> On Dec 13, 2:10 pm, mAsterdam <mAster..._at_vrijdag.org> wrote:
> > raylopez99 schreef:
> >
> > > With a few hours of theory under my belt, I'd like to ask if there's
> > > ever a time that you don't want a completely normalized dB, that is, a
> > > normalized database being a dB that has no redundant information (my
> > > understanding of what a normalized database is).
> >
> > This question is very well put.
> >
> > First, about your understanding.
> > The goal of normalizing is not to get rid of redundancies but to get
> > rid of update anomalies. If, when in the real world your database is
> > modeling one thing changes and you have to change several pieces of
> > data in your database to keep it up to date, this means you have an
> > update anomaly.
> >
> > Several (mostly increasingly strict) normal forms exist to check if
> > your set of relations suffers from some anomaly with recepies of
> > changes to it to make it comply. Just google for normal form if you
> > didn't already.
>
> Thanks; I did Google it,and learned a bit about 1NF, 2NF, 3NF here:
> http://defiant.yk.psu.edu/~lxn/IST_210/normal_form_definitions.html
>
> While I didn't really understand the differences between the three
> types of NF (for one thing, it seems that they were dealing with a
> situation where the primary key was a 'compound key' that depended on
> several fields, and since I like to use GUIDs as the primary key
> almost exclusively, I don't really suffer from this problem*), it also
> seems that even with a fully normalized database (in 1,2 or 3NF), you
> will always have an UPDATE problem. The 'solution' is to minimize
> your hierarchy of UPDATES by employing a "star" configuration" or
> "cluster", as suggested or implied by David Cressey. This is where
> there is never more than two tables linked by the same key, which
> makes programming easlier (that is, you don't have to program the
> UPDATE anomalies recursively, but simply program the almost trivial
> parent-child UPDATE case). But, as a practical matter, sometimes it's
> hard to not have the same key propigate to a third table, so even as I
> type this I realize this advice is not practical.

A few comments here:

2NF applies only to compound keys. A table that is in 1NF and has only a simple key is in 2NF necessarily. 1NF and 3NF can apply in situations where the key is a simple key.

Be aware that the definition of 1NF changed rather profoundly since the 1980s (when I learned relational databases). By the definition used by Date & Darwen a relation is always in 1NF. The same is true of a relvar. Hence if your model is a relational one, the issue of 1NF is moot, in the D&D framework. The D&D framework is widely accepted.

I never claimed that star schemas would be free from UPDATE anomalies. I am not sure of the case you outline above, but I most certainly can come up with update problems that affect star schemas, but do not affect fully normalized databases. My claims of star schema have to do with ease of access, not ease of update. And the "ease" I'm talking about is largely a matter of perceived difference rather than logical difference.

1NF, 2NF, and 3NF are not the end of the story. In addition, there are BCNF, 4NF, 5NF, and a final normal form called domain-key normal form. I've een references to 6NF in this newsgroup. As far as a can tell, 6NF is the same thing as domain-key normal form.

>
> Anyway, thanks to everybody who replied, it seems that there's a lot
> to learn in dBs.
>

You bet.

> RL
>
> * as a further practical consideration, one dB I use, personally more
> than anything else, is Access, which doesn't have compound keys to
> begin with
>

I beg to differ. MS Access has supported compound keys since Access 97, and maybe earlier. (I'm just coming up to speed on Access myself, having used more classical SQL products.) If you buy the design of either the "Northwind" database, or any of the databases that the wizards can create for you, you are going to learn several unfortunate design habits.

The absence of compound keys is just one of them. Received on Fri Dec 14 2007 - 14:07:20 CET

Original text of this message