0

Product

id
name
price

Tax

id
name
rate

Product_Tax

Product.id
Tax.id
effective date
expiry date

Is this a good design?

How to store Products that are tax-exempted?

Also, how to handle the case when the value of Tax changes for example VAT rate changes from 20% to 30% --> Should I store the value of rate in the Product_Tax table in that case

1

Don't put tax exempt entries in the Product_Tax table.

Design is good (already endorced by me).

  • may be I should use a flag in the Product table for tax_exempt? – j10 Jun 18 at 3:46
  • 1
    why bother? What does it gain? How would you use it? Do the query, it will hint if its a good idea. – danblack Jun 18 at 3:51
  • 1
    With no entries for tax exempt products, you'll need to LEFT JOIN against Product_Tax rather than JOIN and keep in mind that the resulting entries will be NULL on the Product_Tax side of the join. That may have some effect on the result of some aggregate functions. e.g. Look at the IFNULL in this fiddle as SUM({all null values}) is NULL – danblack Jun 18 at 5:24
  • Thank you @danblack. Will take care of the NULL case. – j10 Jun 18 at 5:43
  • Let's say the tax name is "VAT" and the current rate is 20% and now the value of this VAT changes to 30 % after one year. So, in that case, I should not allow changing the value of rate once it has been defined right so that the earlier invoices are not affected? Admin should add a new value "VAT30" and update the value for all Products? – j10 Jun 18 at 11:03

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.