RE: over-normalized?

There are several good reasons to not use full normalization. Take a customer table, which contains address and phone numbers. To satisfy 3NF, you have to move city & state out and join with a zip code table. If you keep more than one phone number, you probably would move them out to a phone number table and include the type (home, work, mobile, fax, pager). In this case, the tradition wastes space, but probably improves query time.

Of course, the real question is...what is the BCHR for 3NF?

-----Original Message-----
Sent: Thursday, January 23, 2003 2:55 PM To: Multiple recipients of list ORACLE-L

A valid point. But say, what if an primary key, such as, employee number has to be changed, or reused? Aaaah!!!

Forget it. Typed that in just for arguments sake ;-)

Thanks
Raj  

                    Jared.Still_at_r

                    adisys.com           To:     Multiple recipients of list
ORACLE-L <ORACLE-L_at_fatcity.com>        
                    Sent by:             cc:

                    root_at_fatcity.        Subject:     Re: over-normalized?

                    com

 

 

                    January 23,

                    2003 01:40 PM

                    Please

                    respond to

                    ORACLE-L

 

 

> An update could end up
> having to write to multiple tables. So, I guess, you have to walk the
tight
> rope between these issues, and having a perfectly normalized database.

You might want to rethink that statement. The goal of a relational database is to have no redundant data.

If you have to update multiple tables in a transaction, so what?

That is certainly preferable to being required to ferret out all the tables that store the same information, and must therefore be updated together, as in a denormalized database.

Jared

Rajesh.Rao_at_jpmchase.com
Sent by: root_at_fatcity.com
 01/23/2003 09:15 AM
 Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:
        Subject:        Re: over-normalized?

How many join table operations do you perform, in most of the queries? As more tables are added to the join, you take a performance hit? Plus, all the space for the indexes on the additional tables? An update could end up having to write to multiple tables. So, I guess, you have to walk the tight
rope between these issues, and having a perfectly normalized database.

To quote George Koch "No major application will run in third normal form".

Raj

                    "Saira Somani"
                    <saira_somani@        To:     Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
                    yahoo.com>            cc:
                    Sent by:              Subject:     over-normalized?

                    root_at_fatcity.c
                    om


                    January 23,
                    2003 11:00 AM
                    Please respond
                    to ORACLE-L

Is there such thing as an over-normalized database design? What defines over-normalization? And what are its consequences? (Other than the obvious degraded database performance and lots of tuning)

I hear rumblings that our ERP system is over-normalized.

Just curious,

Thanks!

Saira Somani
IT Support/Analyst
Hospital Logistics Inc.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).