I need to cater to a complicated system of taxes. Following are the possible scenarios:

1. Simple flat tax rate on net amount e.g. 5%, 12%, 18%, etc.
2. A compound tax (e.g. 5%) which may be composed as below:
   1. State Tax on net amount – 2.5%
   2. Central Tax on net amount – 2.5%
3. There might also be a case of a compound tax, where taxes are applicable not on net amount, but on the previous (net amount + tax). Eg:
   1. Net Amount = 100
   2. Row (1) Plus Tax 1 (5%) = 100 + 5 = 105
   3. Row (2) Plus Tax 2 (5%) on i.e. 5% on 105 = 105 + 5.25 = 110.25
4. Over and above all this, I need to allow the user to add user defined taxes, to cater to those tax situations which my in-built taxes don’t cover.

SOME REQUIREMENTS:

From (4), it is clear there will be some system-defined taxes and some user-defined taxes.

Another requirement is that in the case of a compound tax, I need to keep track of taxes paid for each component individually. That is, in the examples above, I need to keep track of Central Tax, State Tax, Tax 1 & Tax 2 separately.

I was leaning towards a self-referencing many-to-many relationship like so:

taxes (tax_id, tax_name, tax_rate, ...) tax_components (tax_id, fk_tax_id, order, ...) 

The order column would be used to describe the computation order, as in case (3) above.

Example tables for case (2) above:

taxes ---------------------------------------- | tax_id | tax_name         | tax_rate | ---------------------------------------- | 1      | GST 5%           | 5        | ---------------------------------------- | 2      | State GST 2.5%   | 2.5      | ---------------------------------------- | 3      | Central GST 2.5% | 2.5      | ----------------------------------------  tax_components ------------------------------ | tax_id | fk_tax_id | order | ------------------------------ | 1      | 2         | NA    | ------------------------------ | 1      | 3         | NA    | ------------------------------ 

Problem with this design can be best described in one word – Complicated. It will create a complicated hierarchy of taxes with even more complicated queries.

Can anyone offer a simpler design?