Is this database fine for calculation?

Question

I am doing an application called as 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?

Answer 1

Sales/excise/VAT tax rules can be very complicated. There can be different rules about the applicability of different taxes based on things like:

• 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.

Answer 2

You don't want simpler, you probably need more complex. Invocing is a complex field that requires a good bit of accounting knowledge (knowledge of internal controls to prevent fraud in particular and how those would be implemented in your database).

But lets start with a simple example, customers do not have only 1 address (or ship to only one address), so you should have a separate address table. You should have a separate phone table. You should have lookup tables for phone types and address types. You probably need product lookup tables.

I can't say for sure in mysql, but float is generally an inexact datatype and should not be used for any number you intend to do calclutions with. Not unless you like dealing with rounding errors. Or losing money. Accountants tend to hate that kind of thing.

Shipping address should be linked to the invoice not the customer. I may ship one order to a customer in Maine one day and another to a differnt person in Washington the next. I may ship to any one of my three offices or to my home or to my parent's home.

How are you planning to maintain those updated by and updated date fields? They shoudl be in a trigger or they are useless.

Normally products have some sort of product details that can vary such as color or size or no of items in the package. You have a description field for this but 80 chazractesr seems way too small to me.

Truly you need to learn normalization before you even attempt to design a database, let alone one with serious legal implications.

Taxes can be quite difficult depending on how many states or countries you are selling to and the typesof products you sell. Without knowing more detatils it is imposooible to design a good set of tables for taxes. There is no way under the sun that I would consider deigning such a thing without consultations with a good experienced accountant. Preferably one with auditing experience.

There are many many professionally designed accounting packages that do invoicing, it would be cheaper and far better from a legal standpoint to buy one.

Answer 3

One note on the float issue. Yes it is an issue. Use DECIMAL instead which gives you arbitrary precision on the math. MySQL does this internally strings of 9-digit, 32-bit ints. Not quite as cool as PostgreSQL's base 1000 mapping but it should work well enough.

DECIMAL are arbitrary precision, while FLOATs are fixed precision. This means that DECIMAL won;t give you the round-off errors regarding money that FLOATs will. This is a big deal, and a very classic newbie mistake. If you get to production with FLOATs representing money, people will react with justified disdain.

A second point is that once you start getting into this you will end up moving into accounting stuff. I would highly recommend picking up some used or new books on the topic and getting familiar with the practices as they are done first on paper.

Finally you might consider joining an open source accounting app community to understand integration better. That might allow you to do something more contained and integrate with someone else's full solution, and the knowledge you will gain from doing so will be helpful. LedgerSMB is a good one but there are others.