Mysql – Database Design for Maintaining Supplier Invoice

database-designMySQLnormalizationoptimization

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.

http://sqlfiddle.com/#!9/eed9b0

Best Answer

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.

Related Question