Re: Newbie question about db normalization theory: redundant keys OK?
Date: Fri, 14 Dec 2007 00:26:21 -0800 (PST)
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.
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.
- 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