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.


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:

| 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_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?

  • 2
    wtf is this getting close votes. seriously disappointment. Commented Feb 1, 2018 at 18:01
  • I would also search for how others design the tax rates in their systems - as to do it solidly is not easy. Take a look how very popular ecommerce platforms are doing it. To get additional point of reference look on open-source code of WooCommerce Tax Rates to get some examples.
    – Egel
    Commented Feb 4 at 18:02

2 Answers 2


You've specified four types of taxes, but

  • one of them is user defined unfortunately that probably work. You'll have to define the types of taxes users can apply. How will they design the logic for a tax? Unless your providing a logic system, it seems unlikely.
  • a compound tax is really just two simple taxes paid to different taxing authorities, so we'll drop the designation of simple and complex and normalize that away.

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.

The purpose of taxes is quite clear. They're supposed to be complicated. They're puzzles to confuse working class folks so rich capitalists can force their surfs to foot the bill for their own private thugs, armies, and corporate bailouts.

As far as a schema critique. I don't think the self-referencing design makes much sense here, and the constraints are impossible without triggers. I'd ditch it.


CREATE TABLE tax.authority ( authority_id, authority_name, cat, rate, subject )
  ( 1, 'CA',      'NET',   CAST(0.025 AS real) ),
  ( 2, 'NATIONAL, 'GROSS', CAST(0.025 AS real) );

Now you link them together in a linking table. You can easily calculate the 0.050 using sum() in a subselect. To change the modes of application just do something like,

  p.price * t.gross AS gross_tax,
  (p.price - p.cost) * t.net AS net_tax
FROM product AS p
-- should be an inner join if you have a method of going from
-- products to tax authorities
    sum(rate) FILTER (WHERE cat='NET') AS net,
    sum(rate) FILTER (WHERE cat='GROSS') AS gross
  FROM tax
) AS t;

Taxes are usually a function of locality, so I would use GIS or allow people to pick their own taxing authorities and what should be collected.

  • By user-defined taxes, I meant to cover surcharges or cess, that some regions will levy over and above the regular taxes for a region. This usually comes as a tax on tax. e.g. 2% Educational Cess on 5% GST. This would increase ones GST liability by 2% to 5.1% and would need to be tracked as the amount payable to "Education Cess" authority. I want to provide, when defining the "Education Cess" tax an option for specifying it's rate as 2% and what it applies to for eg. GST. From there on, the logic would be generic.
    – Code Poet
    Commented Feb 1, 2018 at 19:41
  • BTW, love the "They're puzzles to confuse working class folks so rich capitalist...".
    – Code Poet
    Commented Feb 1, 2018 at 19:42
  • @CodePoet you'd need another category for cess, or what not. All the same schema though. It's just data. The querying method is what gets more complex. Feel free to simplify the query with a function, even a simple SQL one that just produces the result from the input rows. Commented Feb 1, 2018 at 19:47
  • Sorry, but I'm having trouble understanding how this is taking care of my case (3.3) where a tax is being applied on Net + Tax computation of the previous row?
    – Code Poet
    Commented Feb 1, 2018 at 19:58
  • @CodePoet provide some sample input sample output, and explain. Commented Feb 1, 2018 at 20:10

given the complexity of your requirements and the need for a flexible, yet simple, database schema to handle various tax scenarios, I propose a slightly different approach that maintains flexibility while potentially reducing complexity. This approach involves creating a more straightforward relationship between taxes and their components without requiring a self-referencing many-to-many relationship. Here’s a revised design:


  1. taxes

    • tax_id (PK)
    • tax_name
    • is_compound (boolean to indicate if the tax is compound or not)
    • applies_on_net_amount (boolean, true if the tax applies on net amount, false if it applies on amount + previous taxes)
  2. tax_components

    • component_id (PK)
    • tax_id (FK to taxes.tax_id)
    • component_name
    • component_rate
    • order (to indicate computation order in case of compound taxes)
  3. user_defined_taxes (optional, depending on the requirement for user flexibility)

    • user_tax_id (PK)
    • user_defined_name
    • details (Could be a JSON field to store various attributes of user-defined taxes)

Simplifications and Advantages:

  • Direct Relationship: Each tax can directly relate to its components without the need for a tax to reference another tax, simplifying queries.
  • Flexibility: This design still allows for both system-defined and user-defined taxes, and can easily accommodate compound taxes and their computation order.
  • Compound and Non-Compound Distinction: The is_compound flag in the taxes table helps in quickly identifying if a tax is compound, and the applies_on_net_amount flag helps to determine the base for tax calculation.
  • Easy Tracking of Components: Each component of a tax is a separate record in tax_components, making it easy to track and report taxes paid for each component individually.


For the compound tax scenario mentioned (5% GST composed of 2.5% State Tax and 2.5% Central Tax), the entries would be:


| tax_id | tax_name | is_compound | applies_on_net_amount |
| 1      | GST 5%   | true        | true                  |


| component_id | tax_id | component_name | component_rate | order |
| 1            | 1      | State GST 2.5% | 2.5            | 1     |
| 2            | 1      | Central GST 2.5%| 2.5           | 2     |


  • Scalability and Performance: While this design is simpler and more direct, performance and scalability considerations should be taken into account, especially with the potential for a large number of records

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.