Best way to store sales tax information

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:

Unit price excluding tax
Unit price including tax
Tax per item
Total excluding tax (rounded to 2 decimals)
Total including tax (rounded to 2 decimals)
Total tax (rounded to 2 decimals)
Tax Percentage
Fk link to the Tax % (and not store the tax amount)

Currently the most reasonable solution so far is storing down (roughly) item, quantity, total excluding tax (rounded), and the total tax (rounded).

Is there a better way of storing these details for a generic system?

Given the system needs to be robust, what should be done if there were multiple tax values which might need to be separated (e.g. state and city)? In this case a separate table would be in order, but would it be considered excessive to just have a rowID and some taxID mapping to a totalTax column?

To clarify: Asking how to store the data about individual transactions and that side; not so much the details about the tax specific rates.

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 difference tax rates a shop would deal with eg.

TaxFree - 0%
Vat - 17.5%
DiscountedVat - 15%
etc...

and then your stock table fields with be

ItemId
UnitPrice
fk_TaxRate

your invoice row table will be

fk_OrderId
fk_ItemId
PerItemPriceCharged (denormalized)
TaxRateCharged (denormaized)
NumberOrdered

your invoice will be

OrderId
fk_CustomerId

Where fk_denotes a foreign key. Note that OrderId will not be unique in your invoice row table.

EDITS: Heads all over the place today.

You need to denormalized 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 historial invoices.

Interesting approach, I hadn't really considered not storing the tax amount details at all with the transactions, and if your transaction should have multiple tax rates (if it was needed for some reason) you could just have a fk table to link between the main table and the various tax rates.

Seph 2010-03-08 12:53:51

In that last edit I added a missed row TaxRateCharged which should be denormalized in case a Tax_Rate changes.

runrunraygun 2010-03-08 12:56:12

Yes you've also got a bit of flex in that a custom could have a fk_ tax rate based on location, like STEVE in USA has a default TaxCode USA1.

runrunraygun 2010-03-08 12:57:37

another option would be in the Tax_rate table add a start and end date with autoincrementing ID field that can be used as a FK in the Invoice Row Table...that way when the rates change you are still linked to the correct tax rate at that time.

Leslie 2010-03-08 16:17:33

great idea, would be very handy for future rate changes at ungodly hours of the night also.

runrunraygun 2010-03-08 16:21:06

Trust me on this, store the tax rate charged in the order record. This is historical information (what was done at the time) and not really denormalization. You do not want this changed if anyone ever changes the tax rate table (even if you store effecive dates and were correcting an error). The tax rate you charged is what you charged whether it was correct or not.

HLGEM 2010-03-08 16:46:02

ansaurus

tags:

views:

answers:

Best way to store sales tax information

related questions