Modelling a Database for Recording Taxes

Question

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?

Answer 1

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 SCHEMA tax;

CREATE TYPE tax.cat AS ENUM ('NET', 'GROSS');
CREATE TABLE tax.authority ( authority_id, authority_name, cat, rate, subject )
AS VALUES
  ( 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,

SELECT
  p.price,
  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
CROSS JOIN (
  SELECT
    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.

Answer 2

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:

Tables:

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.

Example:

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

taxes

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

tax_components

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

Considerations:

• 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