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.533) * 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?
I think you should look at this slightly differently. I have encountered similar issues in the past especially when it comes to finance data. If the taxable amount is fixed at 20% then this is something that you could in theory calculate each time. But what happens if the tax rates goes up to 21%? Should your data still be taxed on 20% or at the new level of 21%? If old data should be taxed at 20% then I would store the calculated amounts to avoid any confusion.
If on the other hand the values can always be calculated based on the base table, then have a look at creating a view which does the calculations for you against the base table.
The rule of thumb I have always used, is if you want the data displayed back to the user exactly how it was shown the first time, it is likely best to store those values somewhere.
Yes, you can use tax tables, product price tables, etc (in case the tax changes, or the product price changes) and associate an id that must be joined to each order, but usually it is easier and far less complicated to just create a order invoice table that stores the values you need to display.
I’ve never really decided if the better technique is to store an OrderInvoice object in the database (one that is serialized from your code, so you can deserialize it back), or to have a column for each property. I don’t know that it matters much one way over another, as there are advantages and disadvantages to each.