Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Normalization

Re: Normalization

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Thu, 29 Jul 2004 12:09:23 -0600
Message-id: <>

I recall a quotation to the effect that no database will perform adequately in 3rd Normal Form. To me this defines the difference between theory and reality. I have had the 'pleasure' to deal with 2 separate 'datawarehouses' that were exact copies of the OLTP structures. They work fine for a few months, then performance *rapidly* degrades to the point where the system is basically useless.

To me, for any given system, the proper design achieves the following:

  1. The continuing performance of the business's essential tasks is acceptable
  2. Modification of the data does not introduce integrity problems (including issues related to business integrity)
  3. Data changes do not require structural changes (except very unusual circumstances, e.g. changing US zip codes to 100 numbers)

If a company's sales data from an OLTP system is to be used by different departments, should the designs be adapted to each department's usage? If the sales department's queries include salesperson_name, region_name, company_name, product_name for 80% of the queries of the order records, is there a compelling reason why the names (and not ids that must be looked up in another table) should not be included into the order_detail_report structure? If the business rule is that the company_name is never updated and if a company changes names, a new company is created so as to preserve historical integrity, should the company_name be included in the order data structures?

Ah, but what about the storage argument put forth by the unnamed 'expert'? Perhaps disk space was a practical consideration, perhaps not...perhaps some of our elder statesman can address this particular issue (or Madame Cleo since E.F Codd joined the bleedin' choir invisible). However, it seems that this is a red herring. Consider the hypothetical...

If we store the company_name in the order_detail record, we increase the storage required by 1%. This means that we need 1% more blocks and perform 1% more i/o on the table to retrieve said blocks. If joining to the company table would require 5% more i/o what should we do? If joining to the company table would require 0.001% more i/o what should we do?

Bill Sable
Hell, Michigan

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Thu Jul 29 2004 - 13:06:41 CDT

Original text of this message