What is the best practices for storing sales tax rates?

In Canada we have a federal general services tax (GST) of 5%. Some provinces have a provincial sales tax (PST). Some provinces have a hybridized sales tax (HST) instead of a separate (GST) and (PST). Some provinces have a tax on Insurance (RST). Extended Warranty on vehicles is taxed GST but only one province taxes it for PST. One province (BC) varies the PST on passenger vehicles where the rate is 7% for = $55000 AND = $56000 AND = $57000. There is a definition of what

In the states I read that you don't have a Federal Sales Tax but you do have separate state, county and city sales taxes. Looking at Wikipedia there are many details to the taxes in the states. https://en.wikipedia.org/wiki/Sales_taxes_in_the_United_States

I will likely need to support taxes in other countries as well at some point.

What is the best practice for storing all these tax rates so that applications can use them effectively in real time to compute tax amounts?

What is the best practice for storing the tax amounts for different transactions?

Sales taxes are almost infinitely complicated, so you might want to consider using a rules engine like Drools for this.

Sales Tax is a rate or an amount that is charged on the value of an item, or by unit (per day, per litre, per each). An item can have zero, one or many sales taxes applicable to it.

Warning: Some sales taxes are recursive! Ex: there is GST on top of the Vehicle Rental Tax in BC.

The sales tax rates on an item is a function of the following:

• Location of the buyer

• Product category of the item being sold (gas, liquor, books)

• Value of the item (ex higher rate on higher priced luxury goods)

• Category of the buyer (ex charity, First Nations individual, industrial camps)

• Category of the seller

• Intended use of the item (for export, for manufacturing)

• Duration of the service (car rentals > 8 hours and

• Whether it's a gift or not

I think what you ultimately want is a large look-up table (generated by a materialized view) like the below. It would include default tax rates as well as exemptions (represented by null) or zero-rated (with a rate of zero)

This is only a sample of taxation rules for hotel accommodation in British Columbia:

name | area | product cat  | price range | buyer cat | service duration | rate | unit  | seller cat
-----------------------------------------------------------------------------------------------
GST    BC     Accommodation  null          null        null           0.05   Percent null
PST    BC     Accommodation  null          null        null           0.08   Percent null
MRDT   BC     Accommodation  null          null        null           0.02   Percent null
MRDT   Vanc   Accommodation  null          null        null           0.03   Percent null
PST    BC     Accommodation  [,30]         null        null           null   null    null
MRDT   BC     Accommodation  [,30]         null        null           null   null    null
PST    BC     Accommodation  null          null        [30,]          null   null    null
MRDT   BC     Accommodation  null          null        [30,]          null   null    null
PST    BC     Accommodation  null          1st Nations null           null   null    null
MRDT   BC     Accommodation  null          1st Nations null           null   null    null
PST    BC     Accommodation  null          Gov Canada  null           null   null    null
PST    BC     Accommodation  null          null        null           null   null    Industrial Camp
MRDT   BC     Accommodation  null          null        null           null   null    Industrial Camp
PST    BC     Accommodation  null          null        null           null   null    Charity
MRDT   BC     Accommodation  null          null        null           null   null    Charity

Then you'd have a crazy query that would figure out the default rates, replacing them with the most appropriate exemptions or zero-rates.

• 5% GST always applies

• 8% PST unless price is 30 days or buyer is First Nations or Government of Canada or seller is an Industrial Camp or Charity

• 2% Municipal and Regional District Tax unless price is 30 days or buyer is First Nations or seller is an Industrial Camp or Charity

• 3% Municipal and Regional District Tax if accommodation is in Vancouver unless price is 30 days or buyer is First Nations or seller is an Industrial Camp or Charity

StackExchange Database Administrators Q#132215, answer score: 6