Re: Normalization
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 29 Jul 2004 12:09:23 -0600
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?
Regards,
Bill Sable
Advocate
Hell, Michigan
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
