How do you store consumption tax information in a database

Question

What are your experiences with storing consumption tax information in a database (e.g. sales tax, GST etc.).

I am looking for elegant and simple methods that will be suitable for Australia, the US and the UK.

I am seeking answers from people who have dealt with the more complex intricacies of consumption tax : i.e. the cases where products and services may be exempt from some or all of the tax.

Specifically, have you had more success storing the applicable tax in a separate column beside each price, or is it wiser to add on/take off the tax after retrieving the amount from the database? Is it worthwhile using a custom data type?

Answer 1

A set of separate columns is always bound to cause problems. You'll always need another tax. A separate table is the way to go. Or depending on how your sales system is setup the various taxes could all be "sold" as items. That would make the OrderItems behave more like a general ledger account with credits and debits.

US Sales taxes are the most complicated I've seen. City, State, Federal, day of the week, and phase of the moon. With exceptions for products, purchasers, and maybe even the combination.

Answer 2

I'm with robby, the relation between product and tax (consumption or not) is a many to many and should be coded as such. Another gotcha is handling the change in tax rates, when you report for sales before/after the tax rate change you still need everything to balance. So at the very least you need your tax table to look as follows

TaxId, TaxRate, Description, ValidFrom, ValidTo

If ValidTo is null then you're looking at the most upto date tax rate.

BTW Has anyone else experienced the slush fund tax in Italy - I kid you not.