| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Normalization
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:
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?
Regards,
Bill Sable
Advocate
Hell, Michigan
 |
 |