Help with design

[SF]

Hi, I have a small homemade inventory database that work fairly good in
keeping track of my customers purchase'and payment. Recently I face a
situation like below and did not know how to structure my tables to
accomodate this changes.

My customers come to buy stuff on credit which cost about $200. Later he
come to pay $500 and take another puchase of $650. In this case I didn't
know how to structure my table to record this transaction. I have record
information on the follwoing:

Information on the amount purchase
Information on the amount of payment

What I am looking for is a way to link (liquidate) the purchase and payment
amount

Hope someone would shed some advice

SF

 

[Carl Rapson]

Hi, I have a small homemade inventory database that work fairly good in
keeping track of my customers purchase'and payment. Recently I face a
situation like below and did not know how to structure my tables to
accomodate this changes.

My customers come to buy stuff on credit which cost about $200. Later he
come to pay $500 and take another puchase of $650. In this case I didn't
know how to structure my table to record this transaction. I have record
information on the follwoing:

Information on the amount purchase
Information on the amount of payment

What I am looking for is a way to link (liquidate) the purchase and
payment amount

Hope someone would shed some advice

SF

I recently set up an application that does something similar. What you need
to do is store with each purchase the payment type and amount. Each customer
should have a field showing the current outstanding balance. Finally, you
need another table to record customer payments. Here's how it works:

When a customer makes a purchase, if any amount is paid at the time of
purchase an entry is made into the Payment table. If the exact purchase
amount is not paid (customer paid either more or less than the total), the
customer's Outstanding Balance field is updated with the unpaid (overpaid)
amount. Any time the customer makes a payment, either with or without a
purchase, an entry is made in the Payment table and that amount is added
to/subtracted from the Outstanding Balance.

The drawback to this is that specific payments are not linked to specific
purchases. For my purpose, this wasn't a problem. Linking specific payments
to specific purchases is more complicated, because you have to look at each
purchase in order and apply payments to each one until it is paid off. You'd
need a table storing the payment amount and which purchase it was applied
to.

Carl Rapson