How to store taxes in database?

Question

I need to add per-province/state taxes to my project.

I'm debating whether I should add a many-to-many relationship between Provinces and Taxes, or just add a tax1_name, tax1_rate, tax2_name, tax2_rate to each province. I don't think any place has more than 2 taxes?

I will also need to store the tax rate at the time of purchase with each invoice.

So my options are add 2 many-to-many tables, or add 8 fields. Which would you go with, and why?

---

Or I could just have 1 combined tax. I don't think it would be too bad if it showed on the invoice as "GST + PST". This would solve the issue with stupid Quebec which charges QST on top of GST (tax on tax!).

---

Or I could just have 1 many-to-many table and store the start and end date of each tax and then when I generate the invoice I could look it up based on date.

Answer 1

What you really need is something like this:

The problem with combining the total tax into a single field is that while you may or may not get away with showing it that way on the receipt to a customer, the various jurisdictions are going to expect you and your accountants to track the tax collected and payable separately. Blended rates therefore won't do because you need the amounts broken out so you can pay the tax man and satisfy their auditors.

In my suggested solution to your problem, note that the TAX_RATE table includes an intersection between the tax and the jursidiction. This is so the rate can change, not only from jurisdiction to jurisdiction, but so that it can change over time.

Note too that the tax rate table includes a calculation_order value. This can be anything you like which can be sorted and compared. I've used integers before. The idea is that you apply the taxes in order starting with the first one. If you have two taxes that are applied to the same base amount, then these taxes have the same calculation order. If you pay tax two on top of tax one, then tax two has a higher calculation order.

Lastly, I've included a table that allows you to keep track of tax exemptions over time. You can handle this different ways and the way I've drawn it may not be the best or easiest. The point is to include a mechanism for saying "product X does (or doesn't) get tax Y (over a given date range)".

Answer 2

First, you must decide what the controlling dimensions for the taxrate are: state/province (does seller's or buyer's location apply ?), and time (year?) of transaction. These fields must become the keyfields for the tax-table lookup.

Also see the discussion here: How can I properly implement commerce data relationships in a sql database?