CODEWITHSUNDEEP
sqlsql-serveraccounting
Aqeel Haider
Aqeel Haider

Reputation: 633

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.

Upvotes: 0

Views: 2154

Answers (3)

Vinod
Vinod

Reputation: 1

  1. There will be an Invoice & Line Items table to record sales.
  2. There will be a Receipts-Payment table to record payments
  3. There will be customer table to store their balance (credit or debit)

So for every transaction (invoice or payment), you will update the balance in the customer table.

Upvotes: 0

G B
G B

Reputation: 1462

Some sample data might help us understand the problem a bit better.

if I try to read between the lines, you are asking whether it is better to have one record for each invoice which you update when the customer makes a payment, or whether you have a separate record for each payment associated with an invoice which you can then sum or manipulate as you wish. If that is correct then the answer is "it depends" on how granular your transactions need to be.

In my experience accounting packages should be as auditable as possible.

I would suggest that your transactions table have a separate record for each "transaction" where an invoice is a transaction, each payment is a transaction, etc. you could have a column for "transaction_type".

also you talk about having a "table for each customer" the answer to that is no.

Upvotes: 1

Mohammed
Mohammed

Reputation: 313

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.

Upvotes: 1

Related Questions