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

Bob Badour wrote:
David Cressey wrote:

"raylopez99" <raylopez99@xxxxxxxxx> wrote in message
news:15b312ea-1f66-4f22-abbb-63581e0eca73@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

RL

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.

Me too. The inability of a large number of "dba"s to distinguish between
logical and physical and to know which is which is trult astonishing - and
quite sad, given the fundamental nature of this distinction and its
relevance to relational databases.

This forum is full of examples of people who just have not understood it,
and propose bizarre ideas as "performance" enhancing, essentially because
they haven't got a clue what an optimizer is, why it is there, and just what
forms of optimization are in fact possible within any specific product.
Throw in physical proximity and other physical placement solutions and one
quickly realises that most problems are solvable without dicking around with
the logical model.

Dr. Dweeb
(an occasional lurker and OracleRdb guru)


.