Keep total transaction in invoice application

Question

I want to make an application called as Invoice Application with MySQL database. Now I have designed this database. Now I want to know is this database good for Invoice Application. I will use MySQL with Yii framework(php). I am really confused in calculation table. In calculation table I want to store every individual invoice's total tax amount, sub total and total. So is this daabase good for all that purpose or should I keep total in InvoiceItems table?

Answer 1

The first question is: why do you want to store these values?

Typically this is done for performance, so you only have to recalculate them when edits are made. If that's the case, I would also suggest caching the line item values in ia_invoice_items, so you can SUM those to compute the values in ia_calculations.

Usually, I see the ia_calculations values in the ia_invoices table. Having them in their own, smaller table would make updates a little faster, but...

Managing them with triggers (rather than app code) will also protect you from changes made via any app (including the mysql command line tool, etc.)

And some comments that you didn't ask for:

• Don't use INT as your id in your large tables, e.g. ia_users. Use UNSIGNED INT instead.
• Don't use INT to store dates, e.g. ia_users.createtime. Use DATE or DATETIME instead.
• Don't use INT to store booleans, e.g. ia_users.superuser. Use TINYINT instead.
• Don't use INT as the primary key in your small tables. SMALLINT may be a better choice.
• I wouldn't leave the length and precision of your DECIMAL columns to chance. Define them explictly as DECIMAL(5,2) (or whatever length you need).
• ia_invoices.updated_by only allows you to track the most recent update. I would suggest an audit system, where you can track multiple edits, each with their own user_id, datetime, and notes.
• Why don't you have product_id in ia_invoice_items?
• Don't use VARCHAR(45) for postal code. Nobody in the world has a 45-character postal code. Since all customer records should have a postal code, I like to use CHAR here.