-1

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

  • The simple solution is to store each tax as a line item that links back up to a product and you can calculate the total as needed when you need to display the total taxes while still being able to get back to the individual amounts easily – Joe W Jun 25 '19 at 16:44
2

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.

  • but together they are one tax and I need to show in UI as a single tax as well – j10 Jun 17 '19 at 8:53
  • 1
    SELECT product.id,SUM(tax.rate) * product.cost FROM product JOIN prod_tax on product.id = prod_tax.product_id JOIN tax ON prod_tax.tax_id = tax.id GROUP BY product.id. As I said, write up the queries and see what they look like. – danblack Jun 17 '19 at 21:40
  • 1
    should be (1 + SUM(tax.rate)) * product.cost but you get the idea. – danblack Jun 18 '19 at 1:48
  • thank you for the query. But how do I still show that Intra-State Tax = SGST + CGST I need some way to just store in DB that Intra-State Tax is made up of SGST + CSGT – j10 Jun 18 '19 at 3:47
  • this does not help because I still need to represent Intra-State Tax = SGST + CGST in my datbase – j10 Jun 18 '19 at 6:10
0

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.

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.