I need to cater to a complicated system of taxes. Following are the possible scenarios:
- Simple flat tax rate on net amount e.g. 5%, 12%, 18%, etc.
- A compound tax (e.g. 5%) which may be composed as below:
- State Tax on net amount – 2.5%
- Central Tax on net amount – 2.5%
- 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:- Net Amount =
100
- Row
(1)
PlusTax 1
(5%) =100 + 5
=105
- Row
(2)
PlusTax 2
(5%) on i.e. 5% on 105 =105 + 5.25
=110.25
- Net Amount =
- Over and above all this, I need to allow the user to add
user defined
taxes, to cater to those tax situations which myin-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?