Re: normalization
- From: stephen booth <stephenbooth.uk@xxxxxxxxx>
- To: mark.powell@xxxxxxx
- Date: Tue, 1 Nov 2005 21:31:24 +0000
On 01/11/05, Powell, Mark D <mark.powell@xxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
