Re: over-normalized?

Of course, you can get carried away and call something normalized that has gone far beyond the requirements for normalization.

Jared

On Friday 24 January 2003 03:43, April Wells wrote:
> Funny... I aruged against tables called international_phone, us_phone,
> international_address, us_address, primary_email, secondary_email...
>
> My director told me I couldn't kill her... just mess her up real good.
>
> =)
>
> April
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> Sent: 1/24/2003 4:59 AM
>
> 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: Jared Still
  INET: jkstill_at_cybcon.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).