theory question re: storing calculated data

  • Thread starter Thread starter dan dungan
  • Start date Start date
D

dan dungan

Hi,

From reading the newsgroup messages, it seems that most people regard
storing calculated data in a table field is not good design practice.

So I'm not clear how to proceed.

I'm designing a database to use for generating quotes.

There will be lookup tables used to generate the prices for each part
number. I'm imagining that I'll calculate the different prices and
save the calculated total in the quote details table. I don't want to
recalculate the quote if someone needs to see it later. The underlying
prices could change and all the quote details would change, but that
would not be what we sent the customer.

I'm not clear how to design this functionality.

Any suggestions are welcome and appreciated.

Thanks,

Dan
 
In some cases it is acceptable to store calculated data. But you can have
prices in a table with the As_Of_Date so when you recalulate you can retrieve
the price that was.
 
In some cases it is acceptable to store calculated data. But you can have
prices in a table with the As_Of_Date so when you recalulate you can retrieve
the price that was.
 
In my opinion, yours is one of the rare exceptions to that rule and you
should store the result.

The difference is that your result is not merely, the result of a
calcualtion, it is a record of what price you told the customer.
 
In my opinion, yours is one of the rare exceptions to that rule and you
should store the result.

The difference is that your result is not merely, the result of a
calcualtion, it is a record of what price you told the customer.
 
In some cases it is acceptable to store calculated data.  But you can have
prices in a table with the As_Of_Date so when you recalulate you can retrieve
the price that was.
Click to expand...
 
In some cases it is acceptable to store calculated data.  But you can have
prices in a table with the As_Of_Date so when you recalulate you can retrieve
the price that was.
Click to expand...
 
dan dungan said:
From reading the newsgroup messages, it seems that most people regard
storing calculated data in a table field is not good design practice.
Click to expand...

Correct. Most of the time.
There will be lookup tables used to generate the prices for each part
number. I'm imagining that I'll calculate the different prices and
save the calculated total in the quote details table. I don't want to
recalculate the quote if someone needs to see it later. The underlying
prices could change and all the quote details would change, but that
would not be what we sent the customer.
Click to expand...

Now this is a good example of when that rule doesn't work. Or rather
what you are storing is the price at that moment in time. So it
does follow in the rules.

Also what happens if the person doing the quote phones up the supplier
and asks if they can get a good deal due to a large volume and so you
want to put in a slightly reduced price good for just that order?
I'm not clear how to design this functionality.
Click to expand...

In the After Update event of the part number combo box on the quote
number screen you would insert the current price in the quote details
table. I would put the price as a hidden field in the combo box and
use that column to simplify logic and keep the number of disk/network
reads down.

Now what I also do is, if the user overrides the price, is have a
second locked field which I set to O (letter Oh). If the user clears
the price field I fetch the price from the hidden field in the combo
box and clear the over ride field.

I will also, usually, put the markup % field on the form as well but
that is computed in the query on which the subform is based.

Tony
 
Back
Top