Re: normalization

On 01/11/05, Powell, Mark D <mark.powell_at_eds.com> wrote:
>
> Jared >> Calculated fields are for reporting. eg. data marts and/or data
> warehouses. <<
>
> 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.
>

In contemporary versions of Oracle you could possibly use a Materialized View instead of calculated fields in the table itself. Obviously there's a whole bunch of other issues that come along with MVs but they're probably worth looking at as an option.

Calculated fields are a breach of 3NF. But then, like most things in life, business applications don't always fit into nice mathematical models so every rule has to have an addendum something like "...unless there's a really good reason to do otherwise." I think it's important to be aware that you are breaching a rule and to spend some time thinking about the implications.

e.g. You might need a before update trigger to make sure that when any field that is used in a calculation is changed the fileds thaqt are calculated from that are recalculated. Actually you might be best to put the calculation in a trigger and make it a before insert or update if you can.

Stephen

--
It's better to ask a silly question than to make a silly assumption.
--
http://www.freelists.org/webpage/oracle-l