Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?enter image description here

share|improve this question

1 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.
share|improve this answer
    
The calculations should be stored in an invoicing application because they represent what are, essentially, representations to the customer as to how much was owed. In theory they could be checked from elsewhere, but in practice this makes everything more robust and provides for clearer, more transparent audit trails. Otherwise, agreed on all the points, +1. –  Chris Travers Jan 29 '13 at 5:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.