Database structure for advance/due payment

There is no appropriate bucket to hold that information. Advance is given by the customer and cannot be allocated to anything.

 

So it does makes sense to EITHER have a separate table to hold that information (I prefer this approach, however much inappropriate / complicated / unnecessary / heavy on surface it may sound to you) OR store it in the customer table itself.

 

There is no third option.

Thank you sir for your kind response.

 

Yes, I have seperate table for Invoicing, the reason of having BillAmount in this payment module is for something of better performance (I need not have to loop everytime when showing to user). I am modifying the BillAmount whenever there is a change in invoice.

 

If customer has any due/advances it should be depicted to user. I am little bit confused for creating a table for this situation. It would be helpful, if you had advised.

If you have created the tables shown here, please always remember on thing, DO NOT EVER duplicate the information. For example, there is absolutely no need to have BillAmount column in above table at all. You already have Invoice No. and (I hope) you have separate table for Invoices. If not, this is really a very bad db design. Anyway!

 

What you can do is write a stored procedure, to which you will pass customer Id and the amount.

 

In stored procedure write a cursor which will loop through all the invoices and you keep on making them paid. In the loop keep checking if amount is becoming 0 or negative. Do not ever make it negative.

 

After custor is over and you still have amount in hand, that is excess received from customer (5K in example given by you). Then keep that entry (preferably!) in a separate table against the customer ID. Next time when invoice is made, see if amount exists and adjust that amount against the invoice. Because customer has paid the advance. Of course, I would ask customer if he wants to adjust the advance against next invoice.