Calculated/derived fields

All,

We're a food distributor with all 15 standard price brackets stored in the item master. Been that way since the 70's. The maximum price that will fit in a bracket is $999.99. Within a couple of years we will need to have pricing ability above $1000.00. That will be some project for me because we're talking item master, orders, and all sales files. Not to mentioned every printed report, display file, invoices, etc where they appear. I can hardly wait. <g>

When that happens, I think it's time to do some normalization. I'm going to take all price brackets out of the item master and put them into a price bracket master, 1 record per item/bracket. I'm going to take all cost fields (market, last, average, bid, etc) out of the item master and put them into a cost master.

In 90% of the cases, our standard price brackets do not contain a raw dollar amount, but a percent (there is a code field that determines whether the standard brackets are dollar or percent). For example, if a percent item contains 125.00, that does not mean the price is $125.00, but is calculated as (115.00 / 100) * COST. If we change the cost field, all standard price brackets dollar sell are immediately changed, because I have a single called subprogram that calculates the dollar amount when an item master record is read and sticks it right back into the same field for subsequent use (within the program - the database field is not changed).

First off, I'm thinking the price bracket master will look something like this:

ITNBR 6A Item number (part of key)
IBRKT 1A Bracket (part of key)
PCECD 1A Dollar/Percent code (a P or D)
ITPRC7 7,2P Price pct or dollar amt, based on PCECD

ITNBR / IBRKT constitute the primary key.

Something CRPence posted last week about SQL derived fields in V6R1 got me to thinking. Suppose there was an additional field in this file:

ITDLR7 7,2P Actual dollar sell

where, if PCECD was a D, this field would be the same as ITPRC7. If PCECD was a P, this field would contain the result of this calculation: (ITPRC7 / 100) * COST. This would save application programs from having to calculate it upon virtually every read, as well as users not having to calculate it in Queries. IOW, have the database maintain it.

So the question becomes, how does one keep ITDLR7 maintained at all times? Note that ITDLR7 should change when any of the following fields change: PCECD, ITPRC7, or COST. And just to make it interesting, COST will be in another file altogether.

Can a trigger program handle this, and not bring the system to it's knees? Note that there would have to be triggers on both the price bracket file _and_ the cost file to handle this.

What about an SQL view? Can a view reference another file? The problem is a view has no key fields, right? HLL access via key is still required.

This is not an imminent thing, I just need to start getting ducks in a row.

Am I off my rocker even thinking about this?