RE: normalization
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "Oracle-L Freelists" <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 1 Nov 2005 15:37:20 -0500
Jared >> Calculated fields are for reporting. eg. data marts and/or
data warehouses. <<
Jared has a point, but there are potential exceptions. Sometimes a
business requirement exists to calculate a value and the time to
calculate is just too costly to handle on the SELECT. In such a case
you may want to store the calculated value. However, it is a violation
of normalization rules and should only be done if the time to calculate
the value really is too expensive.
For a simple colA + ColB * Col7 = calc_col value the cost should not be
that high. But what if you have to extract 50,000,000 rows in one shot
then the calculation time cost adds up quickly. So it will depend
somewhat on business requirements. Storing calculated columns is
something to avoid doing if at all possible, but doing so will generally
not cause DML activity anomalies like failing to normalize normal
columns can.
If the reason to store the calculated value is so the customer does not
have to calculate in their front-end tools then a view which has the
calculation defined in it is a potential work-a-round. For more extreme
requirements a materialized view might be a solution.
IMHO -- Mark D Powell --
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Tuesday, November 01, 2005 2:53 PM
To: cstephens16@xxxxxxxxx
Cc: Oracle-L Freelists
Subject: Re: normalization
3rd normal form: attributes must depend ONLY on the the key attribute(s)
Calculated attributes violate that.
To preempt the question "What if the calculated column is calculated
from the PK attributes?": if that is the case, then questionable
choices
were made for the key attributes.
Calculated fields are for reporting. eg. data marts and/or data
warehouses.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
On 11/1/05, Chris Stephens <cstephens16@xxxxxxxxx> wrote:
There is a discussion going on at work concerning calculated
fields.
I am claiming that any calculated field in a table is a
violation of
at least 3NF if not 2NF. I can find all sorts of references on
the
web that justify my position but nothing that directly says this
violates normalization rules.
The person who i disagree with is claiming that 'technically',
calculated fields do not violate 3NF. They are just not
recommended.
I am unable to find anything on the web coinciding with this
argument.
Anyone know of a site with a direct statement that calcualted
fields
violate 2NF/3NF?
thanks,
chris
--
//www.freelists.org/webpage/oracle-l
Other related posts:
