How do we store cascading taxes in a database? or database structure for storing taxes
Hi i have to create a database structure for storing tax details.
I have an itemDetails
table which has the details of the item like name and price.
The problem is for each item there are two taxes service charge (10%) and VAT (4%) but the taxes are cascaded i.e after I apply servicecharge, then on the new total I have to apply I apply vat.
I want to store this in a database; I can acheive this with hard coding it but I want it in a database so that in the future if the customer wants he can store more taxes by specifying which order the taxes apply and weather they cascade or not. And each item may have a different tax structure.. (Ex. one item may have the above mentioned tax structure, another item has only vat, another item has a completely diffenet structure etc)
I have a tax category table where the user can store a tax structure 开发者_开发技巧and each item will belong to a tax category i.e the itemDetailsTable
has TaxCategory_id
in it. I need your help figuring it out from there.
You could add another table that has a many-to-one relationship with TaxCategory that uses a ranking column to define what order the taxes are applied in.
TaxCategoryRates
taxCategoryId taxRate taxRank taxDescription
------------- ------- ------- --------------
1 10 1 Service Charge
1 4 2 VAT
2 3 NULL Local Sales Tax
2 4.5 NULL State Sales Tax
Your logic then applies the taxes in order of taxRank. If you have other tax categories where order doesn't matter (as in taxCategoryId 2), you can just leave the rank NULL and have your logic sum the tax rates and apply them.
精彩评论