patternsqlMinor
Is this database fine for calculation?
Viewed 0 times
thisfinecalculationdatabasefor
Problem
I am doing an application called as
Now my problem comes when I am taking the
Update
Here is the link for another question little bit similar.So can this question be solved like this?
Invoice Application.For that I have my database is like this.Now my problem comes when I am taking the
tax part in my database.As I have made tax as an option where user can add one or two different taxes in an invoice form.Also I have taken total in invoice_items table to do calculations.So is this database is fine for doing all the calculation or I have to take another extra table for doing calculation and all?Any help regarding to make the database design simpler will be more appreciable.Update
Here is the link for another question little bit similar.So can this question be solved like this?
Solution
Sales/excise/VAT tax rules can be very complicated. There can be different rules about the applicability of different taxes based on things like:
Also, some taxes are calculated on the base amount and others are compounded (applied to other taxes) as well. These are some of the kinds of rules that apply in the jurisdiction where I live. You might have others as well.
I would suggest not limiting yourself to two applicable taxes per item. Instead, I would create an intersection table (many-to-many) that indicates which taxes are applied to each item. This could be driven by a similar intersection table between PRODUCT and TAX to indicate which taxes are usually applicable to each product, if that is an important distinction in your case.
While adding another table may not seem like a simplification, it will actually make your code simpler and easier to maintain because you are normalizing your applicable tax data. The design you have illustrated isn't even in 1NF, which is almost always a recipe for trouble.
Compounding Taxes
To handle the situation where some taxes compound and some don't, I've used a calculation_order attribute, which is just a number that indicates when to apply the tax. Taxes with the same calculation_order value get applied to the same base. Taxes with a higher calculation_order get applied to the net amount of the product and all taxes with lower calculation_order values.
- What kind of item is it?
- How many of the items are being purchased?
- Who is purchasing the item?
Also, some taxes are calculated on the base amount and others are compounded (applied to other taxes) as well. These are some of the kinds of rules that apply in the jurisdiction where I live. You might have others as well.
I would suggest not limiting yourself to two applicable taxes per item. Instead, I would create an intersection table (many-to-many) that indicates which taxes are applied to each item. This could be driven by a similar intersection table between PRODUCT and TAX to indicate which taxes are usually applicable to each product, if that is an important distinction in your case.
While adding another table may not seem like a simplification, it will actually make your code simpler and easier to maintain because you are normalizing your applicable tax data. The design you have illustrated isn't even in 1NF, which is almost always a recipe for trouble.
Compounding Taxes
To handle the situation where some taxes compound and some don't, I've used a calculation_order attribute, which is just a number that indicates when to apply the tax. Taxes with the same calculation_order value get applied to the same base. Taxes with a higher calculation_order get applied to the net amount of the product and all taxes with lower calculation_order values.
Context
StackExchange Database Administrators Q#22527, answer score: 3
Revisions (0)
No revisions yet.