RE: normalization

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Tuesday, November 01, 2005 2:53 PM To: cstephens16_at_gmail.com
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_at_gmail.com> 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
	--
	http://www.freelists.org/webpage/oracle-l 
	
	
	






--
http://www.freelists.org/webpage/oracle-l