2

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?

  • 2
    wtf is this getting close votes. seriously disappointment. – Evan Carroll Feb 1 '18 at 18:01
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.

  • 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 Feb 1 '18 at 19:41
  • BTW, love the "They're puzzles to confuse working class folks so rich capitalist...". – Code Poet Feb 1 '18 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. – Evan Carroll Feb 1 '18 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 Feb 1 '18 at 19:58
  • @CodePoet provide some sample input sample output, and explain. – Evan Carroll Feb 1 '18 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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