Re: Normalizing vs. Denormalizing
Date: 1996/04/26
Message-ID: <4lrc2e$dat_at_wormer.fn.net>#1/1
Austin Moseley <\"moseba_at_audv55.aud.alcatel.com'> wrote:
>>In article <4km5s7$4pl_at_maverick.tad.eds.com>
>>>: to spend time and effort down the road (probably very soon)on your existing
>>>: system. RDMS such as Oracle are designed to handle normalization very
>>>: well even with the basic configuration and with a help of a good DBA you
>>>: can design a 3rd or even 4th deg. normalize system without any significant
>>>: performance problem. Consider demormalization only when the performance
>>>
>>>Bull. Accepting an answer like this requires little thought from the
>>>acceptor, of course. Unfortunately, as a result, little thought will have
>>>gone into the use for which the database is being designed. Normalizing
>>>just because "it's what you should do" is really stupid. I wouldn't make
>>>my design decisions based on what Oracle (et al) are supposed to do, but
>>>rather on what use my customers will be putting their data to.
>>
Here are some of the reasons to normalize:
1) It makes the database able to be used to answer unanticipated needs
for information.
2) it makes the database able to be expanded upon, without much impact
to re-write existing code.
3) It more accurately represents the truth of what you are storing
information about. By putting your database 'on the solid rock' of
truth you will allow it to better withstand any new wave of technology
that comes along.
4) It makes the database 'internally integral' - meaning that no
contradictions of data fact would exist within the database itself.
5) It is useful for replication
6) It makes the database more navigible to the uninitiated (in other
words, it just makes common sense )
7) It allows the database to be more stable over time than if it was
denormalized. Fewer changes would be needed to the database structure
because the database structrue would be more based on impartial truth
than on a particular solution.
8) it reduces the size (number of colums times the number of rows) of
data tha needs to be stored. This is true even if it actually does
increast the number of tables and thereby the number of colums as
well. This is because it reduces the knowledge about the outside
world into single stored facts. Storing a fact about the outside
world only once is the minimal amount of 'fact storing' you can get
away with and still be able to answer questions about the outside
world. The less you store in a database, while still being able to
answer the needs for information, the more efficient your database is
(as a general rule).
9) a normalized database can take advantage of more of the facilities
of the DBMS, thereby making the overall system more efficient (such as
referential integrity enforcement). The less that needs to be done in
applications code, the more efficient the system will be (as long as
you still get all the work done that needs to be done).
>Performance is the number one reason to normalize. If a table is read
>only and is fairly small, then a denormalization scheme with the
>appropriate indexes is ok.
>But for all other tables, especially those that are updated often, that
>have lots of related data, then the tables should be normalized.
>Otherwise you will waste alot of cpu, memory, and disk space to hold and
>manipulate duplicate data and needlessly large indexes, while fighting
>a fragmentation, and the whole problem will get exponentially worse over
>time.
>To see this, create two normalized tables with one xref table between
>them, add in one-many and many-many rows, then denormalize the data into
>one table - you will have a lot of duplicate data. Your indexes will
>increase in size, then, as will the rate of fragmentation.
>-Austin
'Keystroke'
KeystrkTX_at_AOL.COM
Received on Fri Apr 26 1996 - 00:00:00 CEST