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:

The continuing performance of the business's essential tasks is
acceptable

Modification of the data does not introduce integrity problems (including
issues related to business integrity)

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_at_freelists.org
put 'unsubscribe' in the subject line.
--