How to store tax in the database when tax consists of multiple taxes

Posted on

Question :

We need to design Tax table for Billing System. Lets say we have a SGST (State Tax) and CGST(Central Tax) and

Final Intra-State Tax = SGST + CGST

Tax table

id
name
rate

Tax table

id  |   name      |    rate      |
1   |   SGST      |       5      |
2   |   CGST      |       5      |

How to store Intra-State Tax in the same table?

Should I add a field called ‘taxIds’ which will store a JSON array of other tax Ids like ?

 id  |   name      |    rate      |   taxIds
 1   |   SGST      |       5      | 
 2   |   CGST      |       5      |
 3   |   Intra     |              |  { taxIds : [1, 2] }

Is this a good idea?

Each Product will have tax Id.

Queries mainly include: Calculate Monthly/Yearly Income

Answer :

One (product) to many (taxes) are best done in a separate table (productid, taxid).

Putting multiple values in a single mysql column, especially one that is joined and searched on, will generally be hard to write and perform poorly.

How about Tax Groups?

Create two tables as follows

TaxGroup

GroupId(PK) |  Name
1           |  GST

Tax

TaxId(PK) | Name  | Rate  |GroupId (FK)
  1       | CGST  |  5    |   1
  2       | SGST  |  5    |   1

Then link the GroupId with your product table.

Leave a Reply

Your email address will not be published. Required fields are marked *