Help with Scout Camp Database design PLEASE!

[Guest]

Hi!

I am working on a tracking system for our local boy scout camp. I have a
registration system in place, but I now need to add payment processing to it.
I need to be able to track how much a troop owes for scouts and leaders, how
much individual scouts owe and have a history of all payments made.

Scouts and troops register for camp. Units make a reservation to hold a site
and pay for scouts and leaders. Scouts can also make individual payments.

I have the following tables...


UnitRegistration
Adults
Scouts

You can not have scouts or adults unless the unit has registered. This all
works. I am not clear on how to apply payments to this.

Payments can come from several sources...

Individual scout payments(Full or partial) in advance
Troop paying for their scouts (full or partial) in advance
Troops or scouts making payments at camp check in. (This needs to be kept
track of seperatly, but included in the total paidby scout and unit)

Does anyone have any suggestions on how I should structure this?

Thanks!

Joe

 

[Guest]

Unless you really need to keep track of individual payments (like check #s)
then you don't need to worry about who makes the payment, just what
registered camper the payment applies to. A table with camperID, amountPaid,
date should do it. You could add payment method or check# if you need. You
would have a record for each full or partial payment for each camper.

You might consider keeping Adults and scouts in the same table and
sub-typing them. This would probably make some queries easier.