I worked on a project a few jobs back where the data modelers really
tried for fully normalized tables. When the "address" table ended up as
5 (or was it 6?) different tables, because address was defined as email
or US snail mail or other country snail mail or office building (with
the associated "floor" and "room" information) and we had tables named
"address_format_in_format", I made an executive decision and said that
no matter what the model said, in the PHYSICAL design we were going to
put the main snail mail address into the customer table.
There is a fully-normalized design and then there is the real world. If
you need to make 5 joins just to get an address, and then there is
other information you need in other associated tables, you end up with
queries that are impossible to read, impossible to tune and impossible
to debug.
- "Fink, Dan" <Dan.Fink_at_mdx.com> wrote:
> 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).
>
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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).
Received on Fri Jan 24 2003 - 04:59:16 CST
