Tracking overdue fines and payments - table structure advice?

[Fred Boer]

Hello:

I'm an easygoing librarian, and I haven't implemented fines for overdues in
my small library application. But I've had a request from a more ..uh,
severe... librarian, so I'm trying to work out the best way to track overdue
fines and payments. I'm thinking of the following structure and would be
interested in comments or corrections. Currently I have:

tblLibrary

BookID
Title
ISBN
MediaFormat (Lookup to tblMediaFormat)
etc...

tblPatron

PatronID
LastName
FirstName
etc.

tblTransaction

TransactionID
BookID
PatronID
**AmountPaid - New field needed?

So, could I just add a new field to tblTransaction? For example a field
called "AmountPaid"?

I could derive the amount of overdue fines per transaction from
tblTransaction, using a calculation that multiplies the per diem fine
against the number of days overdue. But I *think* I need to store the amount
paid, to track payment. This would also allow me to track partial payments.
Does this seem reasonable?

I know that if AmountPaid=CalculatedFineDue then that would indicate the
account is settled. But what if I want to just write off the account. Would
a logical field something like "AccountResolved" be appropriate?

Also:

I want to generate different levels of fines for different types of media,
for example, DVD movies get higher fines than books. So, I think I need to
add a table which stores the fine rate per media type, correct?

tblFineRates

FineRateID
MediaFormatID
Fine

Thanks for any suggestions and comments.

 

[KARL DEWEY]

You are missing important fields from the tblTransaction table - DateOut,
DateDue, DateIn. You DateDue could be calculated based on out and media
type.

Yes, of course have AmountPaid and FineDue.

 

[Fred Boer]

Dear Karl:

My apologies: I should listed those fields - yes, of course I *do* have
fields in tblTransaction for "TimeOut" and "TimeIn". And yes, I do use these
fields for the calculations I mention...

Thanks for your comments.

Cheers!
Fred

 

[B. Edwards]

Two methods come to mind. First the one you are thinking about with a
tblFineRate to determine fine rates and a tblTransaction with the FineAmount
and AmountPaid stored in tblTransaction.

tblTransaction
TransactionId
BookId
PatronId
DateOut
DateDue
DateReturned
FineAmount
AmountPaid
DatePaid
etc.

tblFineRate
FineRateId
MediaFormatId
FineRate

The other option would be to create a tblFine table that links to
tblTransaction and a tblPayment that has a many to one relationship to
tblFine. The would probably allow more flexibility when it comes to
processing fines and payments, such as being able to track multiple payments
against a single fine, and tracking reasons for fining people other than
late returns. Partial table structures as follows:

tblTransaction
TransactionId
BookId
PatronId
DateOut
DateDue
DateReturned

tblFineRate
FineRateId
MediaFormatId
FineRate

tblFine
FineId
TransactionId
FineAmount
FineReason eg. Overdue, LostBook, DamagedBook

tblPayment
PaymentId
FineId
PaymentMethod
PaymentAmount
PaymentDate

 

[Fred Boer]

Dear B. Edwards:

Thanks for your help; that is interesting and I will be spending some time
thinking about your suggestions!

Cheers!
Fred