Re: Normalization and Derived Information

In the last exciting episode, jcelko212_at_earthlink.net (--CELKO--) wrote:
>>> I'm wondering about the precise status of derived columns in terms
> of 1nf,
> 2nf and so forth. <<
>
> Tom Johnston did a series of articles on other forms of redundancy
> than normalization problems that would be worth pulling up.
>
> It is bad if you materialize the computed column because it can get
> out of synch with the columns used to compute it. We used to have
> that problem all the time in punch card systems.

The flip side is also true.

If the price changes, that probably SHOULDN'T change the price charged on sales that have been completed.

In a payroll system, for instance, you probably want to keep the values computed for each paycheque. That way, if rates change, you have some hope of actually noticing this, and being able to issue an adjustment.

Looking at the data as a set of predicates, having the price in a common table would indicate...

  "$45.00/unit is our standard price at this time for Product X."

The value recorded on an invoice would be a separate predicate/fact...

  "$45.00/unit is what we charged for product X on invoice 12341."

The "standard price" might change, but once the invoice has been issued, the price used on invoice #12341 probably DOESN'T change retroactively...

-- 
(reverse (concatenate 'string "gro.gultn" "_at_" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/postgresql.html
Eagles may soar, but weasels don't get sucked into jet engines.