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

From: Bob Badour <>
Date: Thu, 13 Dec 2007 13:30:42 -0400
Message-ID: <47616c45$0$5286$>

David Cressey wrote:

> "raylopez99" <> wrote in message

>>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).
>>Or, is there ever a time that you want redundant keys (that is, the
>>same keys in many different tables, that obviously are not linked (in
>>a relationship) between two tables?).  Having redundant attributes and/
>>or keys seems to me a very lazy way of designing a database that
>>doesn't require lots of initial thought, but of course you have to pay
>>for it by meticulously "synching" all redundant keys to one another
>>everytime there is a change in one of the redundant keys, so the keys
>>don't drift and have different values.
>>But is there ever a time you want to do this?
>>THanks in advance

> The answer is yes, there are times when a design is a good one, even if
> less than fully normalized. For each normalization form, there is a known
> set of anomalies that come up when you insert, update, or delete data in
> that form. If you are willing and able to program around those anomalies,
> and if the design yields benefits that justify that effort, it can be the
> right thing to do. Learning when to normalize is more subtle than learning
> how to normalize.
> There is a particular form of database design, called "star schema" that
> yields good results when used in a data mart or data warehouse situation. A
> star schema mimics a multidimensional database in relational (or SQL) form.
> A star schema follows design rules of its own, and those rules sometimes
> contradict the rules of normalization. The up side of star schema is that
> it's very easy to use with report generators, or with OLAP tools like
> Cognos or Business Objects. The down side of star schema is that the
> process of keeping the data current involves some fairly intricate
> programming, and heavy use of computer resources.
> Star schema, and other unnormalized or denormalized designs almost always
> cost more than they are worth when used in a high transaction operational
> setting, like OLTP.
> Unfortunately, most deviations from normalization occur due to blunders, and
> not due to well considered design decisions. Many deviations from
> normalization occur because the designer is unfamiliar with some of the
> normal forms. Back when I was building databases, I only really knew 1NF,
> 2NF, and 3NF. Update anomalies due to deviations from BCNF and beyond were
> rare, but my design process would not have obviated them.
> Another major cause of deviations from normalization is failure to
> understand the data. In particular, the functional dependendencies inherent
> in the data are not discovered during data analysis, and the design
> unknowingly violates normalization rules. By the time this is discovered,
> there is usually a large body of application code that is dependent of the
> bad design.
> Sometimes, denormalized design is the reult of sheer pigheadedness.

Don't listen to a work David says. Star schema was sold by Cognos and Business Objects so their customers would have to do the work they should have done in the first place.

I seldom see anyone 'denormalize' who is aware of the actual costs of doing so. On the other hand, I have seen plenty of ignoramuses 'denormalize' when physical clustering for the same performance characteristics was an available option. Received on Thu Dec 13 2007 - 18:30:42 CET

Original text of this message