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.