I am a trucker/transporter and I transport the goods of my customers. I am paid per trip.
Case 1:
One of my Customers pays me 100% freight on the spot. I have a 'ReceivedFreight' field in my 'OrderDetails' table. I subtract the received freight and 'Balance' becomes zero for this record.
Case 2 (A) :
A Customer give me partial payment (as an advance), 'Balance' = remaining amount (for this record)
These types of customers pay the remaining amount after a few days (not fixed). Receiving of the freight has no regular routine.
Case 2 (B) :
Sometimes a customer pays me the remaining balance amount for a single trip (1 record).
CASE 3:
Sometimes a Customer pays a lump sum amount (for example, $2000) for multiple trips without knowing or caring the exact figures of the Invoice. He says that we will adjust it later.
In this case I can not enter the received amount in 'ReceivedFreight' because it's a big amount and not specifically paid for an Order or Orders (invoice)
I think I have to open an account for each customer to deal with these kinds of irregular payments but at the same time I will have to maintain the advance payments received against some orders in the invoice.
But I don't have an idea how to design and relate the required table(s) to deal with this situation.