theory question re: storing calculated data

[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

 

[KARL DEWEY]

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.

 

[KARL DEWEY]

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.

 

[Fred]

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.

 

[Fred]

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.

 

[dan dungan]

Thanks for your reply Fred.

 

[dan dungan]

Thanks for your reply Fred.

 

[dan dungan]

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.

 

[dan dungan]

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.

 

[dan dungan]

Thanks for your reply, Karl.

 

[dan dungan]

Thanks for your reply, Karl.

 

[Tony Toews [MVP]]

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

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.

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.

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