Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re: Database Design Issues - OT

by cforde (Monk)
on Jul 13, 2001 at 03:28 UTC ( [id://96230]=note: print w/replies, xml ) Need Help??


in reply to Database Design Issues - OT

By "normalized" I assume you mean 3rd normal form. (There are higher levels.) As a general rule 3rd normal form is a very desireable thing to have because it reduces the amount of duplicate data. But this comes at the expense of additional tables and the joins to create the result sets of interest and maintenance of the additional tables.

As you might expect, opinions vary on 3nf's benefits. Typically, Data Administrators like it, Database Administrators don't. The reason for this is that Data Administrators want to model the "world" in a way that exposes all the underlying relationships in the data and don't care about performance (that's an implementation issue, not a model issue). The Database Administrator cares about presenting the data in the most "useful" way while giving acceptable performance (how the data will be used will affect this greatly). Clearly these two perspectives are in conflict.

My suggestion is to start with 3nf and denormalize as necessary for performance. If this application grows, inevitably other tables will need to be created and the closer to 3nf the original model is the easier the integration of these new tables will be.

Having said that, I don't think it is necessary to normalize things like addresses. eg. name, city, state and zip could be split into multiple tables because zip refers to the city and a city can have multiple zips.

For those who don't know, 3nf can be summarized as: the content of a row is about the key, the whole key and nothing but the key. (so help me Codd) The "key" of course is the primary key. Notice how that is violated in the address example.

disclosure: I'm a former DB2/VM DBA & system programmer.

Have fun,
Carl Forde

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://96230]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others about the Monastery: (6)
As of 2024-03-28 11:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found