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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
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:
Here are some additional thoughts, from somebody who has designed an invoicing subsystem: