Next question:

I consider it a general rule to never store anything that you can calcualte. However there are a few consideration I can't seem to work out regarding tax rate and order total.

First I would assume that you would store a tax rate in your customer table, or at least a link to a tax table. If you do not store the actual rate with an order then after a tax rate change when you view an old order the calculated tax and total will be different. How should I deal with this?

Second, if you do not store the order total, how can you search the orders table based on total. Normally I design an interface where there would be a listing of customers, order, products, whatever and you select one. If I was to display a list of orders how can I show the order total without storing that total in the orders table? I supose I could designe a comples sql query that would calculate the totals every time but it just seems simpler to store that total.

What say the masses?

BTW, thanks for all the help guys.

If the tax rate changes that often, then I would say that the amount of tax on something qualifies as something you can't calculate (without going to lengths to store tax rate history). Also, that rule only makes sense for when the calculation can be done relatively quickly. If it's a large, expensive calculation whose result will remain important for the life of the record/data, then storing it in the DB is actually probably a good idea.

Store it. Your situation warrants it. At least you think about it before just doing it!!

Take the tax rate out of the customer table. It will undo any effort on your part to use past sales to predict future sales. It will make difficult the task of making financial statements. The reason being that all attempts to use past sales in calculations will reflect the present tax rate, yielding incorrect results. Make a seperate tax rate table, with each tax rate uniquely identified by some kind of id. Put the tax rate id in the customer table. That way, when sales records are stored, they will include the id for the tax rate from the tax table. Calculations will address the appropriate tax rate. History is preserved. More importantly, you also avoid any possibllity of accusation of business or tax fraud resulting from incorrectly reported tax rates in your records.

You may want to consider the simple fix of having a tax table with the related dates which those tax rates are effective for, then you can use the date that you have stored with the transaction to determine which tax rate to use. With this solution you are more efficient than relating each transaction to a tax table directly with a key.

-Josh