Help with Payment Database

[Joe Cilinceon]

What I have is 3 tables.

Transactions table
Trans (autonumber)
PaymentDate
PaymentAmount (this is the total amount)
LedgerID (this is the custno & unitno combined)

Payments Table
Trans (long integer)
LedgerID
payamount (could be multiple payments on 1 transaction such as cash
and check)
paymethod (cash, check, credit card, money order etc)
tracking # (check #, MO #, Credit Card approval numbers)

Ledger
Trans
LedgerID
PaidFrom (this point on holds the brake down of what this payment is
applied to like rent and such
PaidThru
Rent
LateFees
..etc

Can anyone suggest a better way of doing this. I would be very grateful

 

[Khai]

Does Trans in Transactions Table = Trans in Payments Table?
Is LedgerID the same in both tables as well? If so, can you just use Trans
to go between the two? How many tracking #'s can actually be identified by
tracking# in Payments? How can you keep more than one tracking # for each
customer?

-very curious-
DPHarr

 

[Joe Cilinceon]

The Trans # is an autonumber done for each complete transaction which can
have a many payment types. The LedgerID is a text field based on CustNo (3
to 5 #) the space that is associated with that customer (they have have more
than one space and each space is a different account) and their start date
such as todays date would be 20050120. The reason for the LedgerID is we may
rent a space 2 times in the same monthly billing span.

I have also dumped the idea of the trans table alone as it seems a little
redundent once I really looked at it. I'm now trying messing with the
following table design.

Ledger Table
Transcode is generated here
LedgerID (Is the identifacation for what space and customer combo this
payment is for) It also ties the Lease information to the Tenants table
PaymentDate
PaymentAmount - Total of all parts of the PayAmount below
PaidFrom (Date field as is below)
PaidThru
Rent
AdmFee
Lock
LateFees
NSFFees
Elec
Water
CreditsApplied
CreditsEarned
PreviousBalanceDue

The Payments Table is a break down of how they paid be it one payment or
several
Transcode
PayAmount
PaymentMethod
Tracking # (this is check number etc.)

I'm converting this from a single table that is hard to post a split payment
too.

The LedgerID is part of the TenantLeases table that has the space number,
date rented, date vacated, special things like autopay, invoice etc.

Thanks for the response Khai, it has been a long time since I worked with
databases and much has changed since Clipper for DOS.

I'm also looking starting to deal with partial payments (saved as a credit
and not posted to the account), Overpayments also kept as a credit, NSF
checks where I have to roll back a previous payment. I hope this is all
clear.