Search code examples
sqlsql-serveraccounting

Credit debit and balance of a customer in a SQL Server database

I'm working on a Point of Sale System developing in Java, and for backend I am using a SQL Server database. As I'm new to database and confuse that I'm doing write in database.

For ever sale I'm making invoice, the table design is like that

  1. Invoice table has columns InvoiceID, InvoiceDate, CutomerID, MethodOfPayment, Comments

  2. Line Item table has columns InvoiceID, LineitemNo, ProductID, QTY, Price

  3. Transaction table has columns TransactionID, InvoiceID, Debit, Credit, Balance, InvoiceStatus

Walking customers is always cash customers, so I have no problem with them. The InvoiceStatus flag will be close to them because no payment left from them.

For other customers they take material on credit also.

I need to to save there Credit Debit and Balance history in a very effective way.

My problem is that for every customer do I need separate table for record of there credit debit and balance history or all in one table, because in future the table size will grow very big if I use only one table.

Or if someone have better database design which I'm using now it will be very helpful.

Solution

  • Its recommended to make a separate table for the pending debits, and the the pending debits will be dynamic which means when ever the invoice collected completely the delete its record. And your invoices table will be as its.