views:

285

answers:

3

We create point of sale software for the mac, and are looking to revamp our tax engine. It's pretty simple now, with taxes consisting of a name, code and rate that can be applied to every product individually. While this is good enough for some people, we've had lots of requests to handle more advanced situations. Some examples are US City/County sales tax, Canadian compound (stacked) taxes, French ecotax and NYC luxury tax.

We've identified most of the characteristics that these taxes have and are leaning towards a sort of rule-engine based implementation. We don't have to support every case out there, but we want to be able to extend it if needed (to avoid another rewrite).

We're looking for advise from people who built something like this before, or examples of projects that try to solve the same in an elegant way.

+1  A: 

I would recommend a set of database tables and joins.

Example:

  • Jurisdiction: list of states, counties, countries, cities, etc.
  • Product: obvious
  • Store: list of locations you sell from
  • StoreJurisdiction(StoreID, JurisdictionID): the list of Jurisdictions the store is responsible to collect taxes for
  • ProductTaxCode(ProductID int, TaxCodeID int): the type of product for the purposes of taxes: basic, luxury, etc.
  • JurisdictionTaxCodeRate(JurisdictionID, TaxCodeID, InterestRate, RateType): for each applicable combination of Jurisdiction and Tax Code, provide the tax rate to be applied, and the type of rate (compound, simple, etc.).

To find the list of taxes to apply, all you need is an INNER JOIN of the store, its jurisdictions, the jurisdictiontaxcoderates for those Jurisdictions, and the product's tax codes.

You could define ProductTaxCode as a View so all products receive a default TaxCode unless a special one is provided. By abstracting TaxCode, you can have the same metadata about a product ("Food" for instance) apply to different regions in different ways. If a particular jurisdiction has its own definition of "food", you just add a jurisdiction-specific code and apply it to products as needed.

This may require some tweaking for Internet purchases, wholesale purchases, and other situations where the sale is somehow exempt from taxes or the customer is responsible for remitting them. It would also need tweaking for situations where the customer's location, rather than the store, decides the tax rate.

Other tweaks: here in Texas, for instance, we have a "tax-free" weekend where state and local taxes are not collected on some classes of products where the individual item's sale price is less than $100. The idea is to provide cheaper school supplies, clothing, etc. for children heading off to school for a new year. This sort of tweak could be implemented by having a date range table for each JurisdictionTaxCodeRate going off in the future as far as they can be planned.

richardtallent
A: 

Here is an example of a "home rule" city in the Denver, CO metropolitan area:

http://www.c3gov.com/pages/about/division%5Fsalestax.html

You, as a retailer, may also need to send the tax payments to different locations. For cities that are not "home rule" cities (which is a special term that probably only applies to Colorado, but then probably every state has some equally special term like it), you'll send all the tax payments to the state who will then deal them out to the relevant parties. Colorado has a feature where there are "special tax districts" that are permitted to collect sales taxes for certain benefits (on the example link, RTD is the public transportation district, and "Invesco Field" is the stadium where the Denver Broncos play).

To expand upon Mr Tallent's answer on this thread, you'll need to also include in the Jurisdiction table some way of representing that the taxes may go to different places.

Tangurena
Good point... it doesn't the calculation of the tax, but this is the sort of thing you'll need for reports coming out of the system to tell the user where to send the checks, and for how much.
richardtallent
A: 

My suggestion would be to use database tables for what they are good for (storing values) and rules for what they are good for (business logic). I would certainly not put things like tax rates or lists of jurisdictions in rules - those should be in tables. What I would use a rules engine for is defining the logic that determines which rate to apply to which transactions. So, for instance, if I buy a set of products online from a company based in State X that ships from State Y to three different locations, what tax rates apply to which parts of the transaction? This combination of rules and database tables is very common - the rules make sure you look up the right things while the tables aid in reporting etc. For instance, the California DMV did this with vehicle registration fees - all the various fees are stored in a database while the rules that determine which fee applies to which car are managed in a rulebase. If you try and put everything in rules you will not be able to report well and if you try and put everything in database tables you will end up with dozens of tables to manage all the exceptions and corner cases. JT

James Taylor