When designing a stock management database system for sales and purchases what would be the best way to store the various taxes and other such amounts?
A few of the fields that could be saved are:
The problem with approach is if the tax changes, VAT (sales tax) in the UK has changed twice in the last 12 months.
When I worked in ECommerce websites we had a table
Tax_Rate which held the different tax rates a shop would deal with eg.
and then your stock table fields may have
your invoice_detail row table will be
your invoice table will be
Where fk_denotes a foreign key. Note that OrderId will not be unique in your invoice row table.
EDITS: Head's all over the place today.
You need to denormalize the invoice row total and the tax rate total because you don't want future changes in an item price or tax rate to affect the historical invoices.