Results 1 to 4 of 4
Sep 17, 2012, 08:37 #1
- Join Date
- Apr 2005
- 3 Post(s)
- 0 Thread(s)
Whether to save calculated values in table, or just source values
I'm just wondering whether or not it's advisable to store in the database values which are calculated from other values in the table. This specific instance involves tax calculations.
eg I'm storing price and tax rate, what I've been doing is also storing the tax amount (eg unit_price, quantity, tax_rate, tax_amount). Do other people take this approach? I seem to remember from uni being taught not to store calculated columns. However there are different ways to calculate tax, and different points to apply rounding (eg 3 units @ £11.53 x 20% tax could be round(11.53*0.2,2)*3 = 6.93 or round((11.53*3) * 0.2,2) = 6.92), so it may be advisable to have a concrete record of each line of tax charged (for audit purposes, perhaps). I'm also storing the product's net amount (eg 11.53 * 2) and gross (11.53 * 2 * 0.2), and these are probably overkill but sometimes it saves me a couple of seconds by not having to calculate these columns. Pretty much pure laziness, also at the time I was trying to emulate the layout of the sage accounts package (though I don't know what data is actually stored at their DB layer).
Another issue is storing a product_total column in the orders table, to represent the total charge for products in that order. Easily calculated with a join, but storing this calculated field saves a join.
So how far to go? raw data only, or liberal use of calculated columns? Perhaps calcualted and aggregated columns should go in a different table?