Best for a calculated field: Table or Query?

[Coach Ty]

Hello,
I have what may be a very basic question.

When designing a software program, if you are calculating an amount, using the values of several fields in a table, would it be best to do a calculated field within an additional field in the same table, or to design a query with a calculated field to achieve the same result?

The table information will be stored in a seperate table, using an append query and be based on the date the query is ran.

So, I'm wondering if I should simply ad another field to the existing table that will calculate the value automatically, every time the data is modified in the table, or if I should make the calculated values a part of the new query that the table, where the information will be stored, will be based on?

I guess what my question is, when designing a software program is it best to do your calculations in a table or in a query, when it comes to stability and the speed of the application?

Thanks for your help ...

 

[Galaxiom]

Storing derived values is against the fundamental principles of database design.
Never derive a field and store it in any table unless it is a summary of archival records that are to be purged.

Do not store derived current information in a table unless it is a temporary table required because of the nature of the subsequent queries (eg results of totals queries that are joined to other tables or queries to form an updateable recordset.)

Use a query for any other derived data every time you need it.

 

[gemma-the-husky]

but it depends exactly what value you are storing

eg - you should be able to recalculate an invoice value (or even the VAT value on an invoice) from stored information, but I am sure most people would STILL store THESE values.

 

[The_Doc_Man]

A further elaboration on when you store a computed field.

1. When you do not (for whatever reason) store the information on which the calculation is based, store the computed field. (This sometimes happens in an environment where the tax rate is structured and the parts are variable.)

2. When you are archiving records and want to create a summary record to stand in for the archived records, store a computed field. (This happens in an inventory system when you pick a date on which all counts are known to be rectified, so you create a "rectified count" record to show on-hand for each part, then replace all transactions earlier than that new count record.)

3. When pre-processing a temporary table prior to storing its content in a more permanent home, it may be permissible to store computed fields, but only if case #1 also applies. If you are storing enough to recompute later, purists would say to not store the computed field in the permanent table anyway.

4. SPECIAL CASE: Strictly for pragmatic reasons, if a particular number is computed a LOT and never changes ever in a gazillion years no matter how often it is computed, you can save yourself the computation step. If ANY CHANCE exists that the number is not constant, do not store it because then you violated normalization rules.