0

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

  • 2
    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 '17 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 '17 at 4:32
0

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.

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.