transaction and Payment relation

[SF]

I have develop a small database that would assist me in maintaining a

small shop. I have 4 tables that seem to work perfectly

tblItems
ItemID Number (PK)
ItemDescription Text
SalePrice Currency
...

tblContacts
ContactID
ContactNames
ContactAddress
...

tblTransactions
TransactionID
TransactionDate
TransactionNumber
ContactID
TransactionType (Purchase, Sale, Return Product)

tblTransactionDetails
TransactionID (FK)
ItemID (FK)
Qty
SalePrice

The above 4 tables would help me to maintain inventory. my problem is

how to track payments from each Costumer since the process of payment

are as follow:

a/ Costumer pays a lumpsum for several open transactions
b/ Customer pays several installment per open transaction

I create 2 tables (see below) for this, but I cannot figures out how to
relate these 2 tables to the transaction (mark each transaction as completed
once the payment has been received)

tblPayments
PaymentID (PK)
PaymentDate Date/Time
ContactID (FK)
PaymentAmount Currency

tblPaymentDetails
PaymentID (FK)
TransactionID (FK)
AmountPaid Currency

Hope the NG would advice.

SF

 

[Allen Browne]

This is always a messy one, and I would be very glad to see what others have
to suggest here as well.

I suspect that payments do not actually relate to transactions at all.
There's just a sum of transaction amounts, and a sum of payment amounts in
any period. The difference carries over as the balance due at the start of
the next period. I'm not an accountant, but it seems to me that this is how
accounting systems work. If you don't do it that way, you start to run into
problems with prepayments, where a client pays up front before there is any
transaction to credit the payment against.

For one client who really wanted to tie payments to transactions, I designed
a structure where the PaymentDetail.TransactionID foreign key in the
PaymentDetail table can be null, to handle prepayments. It does work, but
they found it very confusing to use, so I would probably refuse to do that
again. Later on, they decided that they wanted to lock the transactions at
the end of the period (no more editing), so now they cannot even go back and
reassign a prepayment to a particular invoice, so it has to stay a null
foreign key even after the invoice is later created that the client intended
this prepayment to apply to.

In short, if the client considers an payment to be for a particular invoice
or invoices, that can go in comments but it is not a valid way of relating
the data itself.

HTH, or that someone can give you a better idea.

 

[Tim Ferguson]

a/ Costumer pays a lumpsum for several open transactions
b/ Customer pays several installment per open transaction

I create 2 tables (see below) for this, but I cannot figures out how
to relate these 2 tables to the transaction (mark each transaction as
completed once the payment has been received)

What happens when a customer has several open transactions, and makes a
couple of partial payments? Do you have a rule that pays up the oldest
transactions first; or is the money split proportionately; or does the
customer get to choose ("I'll pay for the diary and the calendar, but not
for the pen because it doesn't write properly")?

It may well be that you need a new table called Contributions, which will
list how much of each Payment contributes to each Transaction. But it's a
lot of overhead given that most of the time a single customer will buy a
single thing and pay for it with a single payment!

An alternative approach would be to separate the outgoings and incomings
completely: the amount people owe you is the difference between the total
of the transactions and the total of the payments. As you can tell, I
don't know enough about business to know whether you _have_ to attach
payments to sales, or just to people who owe you stuff.

B Wishes


Tim F

 

[TC]

I've only taken a quick look at this, so I will not presume to answer
to the level that Allen or Tim have done. This is just a simple thought
on your requirement.

If your customers can pay an amount that covers several bills (if I can
use that term) - or conversely, they can pay a single bill in several
installments - or an arbitrary combination of those two things - I
think that you will have to do two things.

(1) Record each payment in a way that clearly identifies which part(s)
of that payment, apply to which bill(s). (So, a month down the track,
you have to be able to say to th customer: "No, your payment of $100 on
1/2/06 was $30 towards bill #123 and $70 towards bill #456". Clearly,
you have to keep that level of detail.

(2) Every time you enter a payment for a customer, the form will
probably have to /recalculate/ the completion status for every bill to
which that payment applies. And in my opinion, a "paid" bill might
become unpaid! The customer might say, "Hey, that payment of $100 that
I gave you last week for bill #123 - I really meant for you to take
only $80 of that for bill #123, and $20 for bill #456". So, IMO, you
have to recalulate the payment status of /all/ the relevant bills for
that customer, /each time/ you get a payment /or payment adjustment/
from them.

HTH,
TC

 

[SF]

Allen, Tim and TC,

Thank you very much for all the valuable advice. I will try to accomplish
this work and may come back to you all for advice again.

Thank you

SF