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" <[EMAIL PROTECTED]> 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@r > > adisys.com To: Multiple recipients > of list > ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > > root@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 > > > > > > > > [EMAIL PROTECTED] > Sent by: [EMAIL PROTECTED] > 01/23/2003 09:15 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> > yahoo.com> cc: > Sent by: Subject: > over-normalized? > > [EMAIL PROTECTED] > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
