RE: over-normalized?
From: Saira Somani [EMAIL PROTECTED] Date: Thu, 23 Jan 2003 10:59:33 -0500 Subject: over-normalized? 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, Following sites might provide some info of use: http://www.fmsinc.com/tpapers/datanorm/ http://cpcug.org/user/access/Presentations/Normalization/index.htm http://www.cc.utexas.edu/cc/dbms/utinfo/relmod/normal1.html http://www.tdan.com/i001fe02.htm http://www.doxa.ro/DOCS/SY_PERFORMANCE/1088.htm - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: O'Neill, Sean 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).
RE: over-normalized?
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.comcc: 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
RE: over-normalized?
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 [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.comcc: 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
RE: over-normalized?
Title: RE: over-normalized? Like one column per table?? I'd like to name this phenomenon as Fanatic Form Of Normalization Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Friday, January 24, 2003 10:40 AM To: Multiple recipients of list ORACLE-L Subject: Re: over-normalized? Of course, you can get carried away and call something normalized that has gone far beyond the requirements for normalization. Jared This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
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 [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.comcc: 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
RE: over-normalized?
If done properly, this would require two columns. One for the data and one for the PK. This is on parent tables only. Children would of course require FK columnns. :) Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/24/2003 07:54 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: over-normalized? Like one column per table?? I'd like to name this phenomenon as Fanatic Form Of Normalization Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Friday, January 24, 2003 10:40 AM To: Multiple recipients of list ORACLE-L Of course, you can get carried away and call something normalized that has gone far beyond the requirements for normalization. Jared This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
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.comcc: 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).
RE: over-normalized?
Title: RE: over-normalized? From: Saira Somani [mailto:[EMAIL PROTECTED]] Is there such thing as an over-normalized database design? Sure. But usually that would be in the case of doing olap-type reporting in a transactional app. What defines over-normalization? And what are its consequences? (Other than the obvious degraded database performance and lots of tuning) What kind of problems are folks talking about? 99 times out of ten ;-) it's developers who consider joins weird. I hear rumblings that our ERP system is over-normalized. Could be; I'm working on a planning app that is just about as normalized as I've ever seen in a database I didn't create ;-). The problem is, of course, that there are a lot of olap-type queries that are needed, and hence we have some of the squirreliest-looking code in our report engine. The OO guys don't care that much about it, as they have a pretty efficient relational-to-Object engine that does all the roll-up stuff for them. So, what are the kinds of problems you are running into?
Re: over-normalized?
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.comcc: 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: 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).
Re: over-normalized?
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.comcc: 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).
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@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.comcc: 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).
