I am not sure whether it is right to ask such question here. However, It comes to my mind this would be the best place to start. My needs are quite simple. As a distributor, I want to maintain all invoices that come from my suppliers. So I stored the invoices in like below:

id spid transdate transtype transno debit credit userid
1 2 2017-04-04 Invoice MI09 2500.00 0.00 2
2 2 2017-04-05 Pay B098 0.00 1000.00 2
3 2 2017-04-07 Invoice MI89 5000.00 0.00 2
4 2 2017-04-12 Pay BI09 0.00 4000.00 2

In this table I stored the invoices from all my suppliers. But to make things simple, I only showed one supplier with id=2. The problem here is: if I keep storing invoices and my payment in this way, it will soon be too heavy for my table when I have thousands of invoices. I would like to make a printout every month also. So I want to manage the closing and opening of this transaction every month, but I have no idea and google does not help me much. My question is: How do I manage the closing and opening of this transaction? Do I need another field/table for that? What would that be? Please help.


  • 3
    Are you designing this for your business, or is it homework? If it's for your business I'd recommend not trying to write something financial yourself
    – Philᵀᴹ
    Apr 15, 2017 at 18:15
  • @Philᵀᴹ, If it's for business, how would I go about it? Please give an example for efficient modelling such database. Thanks.
    – theJohn
    Apr 16, 2017 at 4:32
  • This example is for a wine store but is generic enough to act as a good basis for a simple invoicing setup. Jun 23, 2020 at 18:09

2 Answers 2


Don't worry about having too many transactions in your table.

Relational Database Management Systems like MySQL are designed to handle amounts of data that would overwhelm other formats, like a spreadsheet for example.

You can easily store millions of records in your table and MySQL will find you the ones you want quickly, especially if you define indexes on the fields that will be part of your WHERE clauses in your queries. For example, if you want to do monthly reporting, make sure you have an index on transdate.


As said, the number of transactions won't be a problem.

What I do not understand (yet) is how you relate payments to invoices. I assume you want to know which invoices you paid?

As invoices and payments may have different data, you could consider creating seperate invoice and payment tables. The relation between the two depends on your processes. Can one invoice have multiple payment? Can one payment cover multiple invoices?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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