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


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

Here are some additional thoughts, from somebody who has designed an invoicing subsystem:

  • What is apply_tax1 and apply_tax2?
  • Why is late_fee a decimal? Why is it stored on the invoice? How will you keep track of updating the late_fee? Allowance and charge instructions, including late fees, typically belong to an Order.
  • You do not have the concept of an Order. Order-to-Invoice is a very universal business workflow cycle.
  • You have payment_method, but you do not have Payment Terms. How will you allow the Buyer to pay on a schedule? e.g., net10, net30, etc. Breaking this out will also then allow you to design a data warehouse where you can robustly report on buyer payment delinquency.
  • How do you plan to support rounding? There are two general ways to do rounding - VAT (Line Item) rounding, or Subtotal Rounding. You don't store the Invoice Total on the invoice header, nor do you store the line item total on the line item, nor do you store the rounding method.
  • Some invoicing schemas store quantity, unit, and pricePerUnit. This is more common for hospital billing systems, where you have a drug that is administered at 15/cc's but you administer 40/cc's, and so quantity = 3.
  • Once you get into thinking about Orders, beware: Orders have their own life cycle. They may only be valid for a certain period, and may only be invoiced under certain conditions (e.g., Amazon.com invoices upon handing the package off from its warehouse to the UPS delivery truck). Orders can be rejected, changed, or canceled.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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