How Should I Design TAX Table?

Question

I have a db with many tables. Some tables have unit price column, no tax, gst and such columns. What should i do now? Should i create GST table, HST table and PST table separately. In other words, what is the standard schema of designing the tax table?

Answer 1

I'd say depends on the amount of columns. If you're only talking about 3 columns then i'd add them into the table and leave them empty if not used.

If however you want to expand this further and further, create a single table to hold the value, description etc.

Then maybe have a lookup table called unitGST and unitHST etc. Each of these tables contains the unit id and the id to your single table holding the value.

Answer 2

You only need one table for tax percentages:

CDN_TAXES

• tax_id (primary key)
• tax_name (GST, HST, PST)
• tax_percentage

Because HST covers both PST and GST, I'd model this as three columns in the PRODUCT/etc table and use a CHECK constraint to enforce that either the HST column be associated, or at least one of PST/GST is associated to the product.

The amount of tax per sale, should be stored in a SALES table. Tax tables in Canada are updated twice a year, and the percentage can change. Which means if you want to reprint a receipt that is accurate, you have to capture the tax amounts at the time of sale.

Answer 3

I think it depends. For storing the type of tax and a rate, sure, you can normalize it in a table - and even capture it changing over time with a start and end date (although the actual tax charged on a sale would always need to be stored).

However, typically you will need more tax configuration. In the US, for instance, we have some things which are never taxed (milk), tax free days when nothing (with some exceptions like cars) is taxed temporarily, and special days when certain things are not taxed (hurricane preparation items like batteries and generators).

So your product table would certainly not hold a tax amount, but may hold some tax flags. Other tables may link the product to a jurisdiction which has taxes. If you have a multi-store company, some jurisdictions may tax things differently, yet you may have a common product master file and pricing might be the same across stores, but taxes would vary.