payment system relationships

[d²c]

Hi dear helper!

I am trying to make a payment follow up system for a school.

In my database I have the following tables:

Students (contains all records of students divided in their class).

Payments (contains all payments related on student and class).

Costs (contains all costs based on class, this table is related to payments with class).

What do I need? A system that tells which costs are to be paid by the student (for every class).

These costs should be in the costs table, and automatically created for each student I think.. What else, which costs he has already paid, and most important which costs are still to be paid.

Can somebody point me in the right direction to set up my database? I'm really stuck on this...

thanks a zillion!

greetz

D

[Razumovsky]

You will want some more tables. Here is what I would suggest given the info provided:

Students (contains all records of students).

Classes (contains all records of classes).

StudentClasses (each record is an individual students participation in an individual class- relate to Students StudentID=StudentID, relate to classes ClassID=ClassID) relate to payments PaymentID=PaymentID)

Payments (contains all payments related on student and class).

Costs (contains all costs based on class, this table is related to classes ClassID=ClassID, students total costs would be gathered through the studentclasses>classes>costs relationship sum(Costs::amount) ).

This assumes that payments are applied to individual classes, not to the costs associated from all of a students classes.

-Raz

[Fenton]

There are 2 possibilities. One is to show the Costs per Student-Class in a portal.* Then you'd click on a button in the row, to transfer that item to the Payments table, for that student. Then enter the amount.

However, while it seems efficient, I don't think it's the best way, especially for a beginning developer. Since the assumption is that the student will pay his fees; and since, as you say, you want to easily see if he has not, I'd prefer to create records for all his payments, then fill in the amounts when you get them.

Yes, you'd have a lot of blank records in Payments, at first. And that goes against the grain for developers. But eventually you'd have very few blanks. And the blanks remaining would be meaningful; they are "due" payments.

It is fairly easy to avoid the blanks when you need to (finanicial reports), and find them when you need to (non payments).

To create these 'payments' you would Import the costs for each student-class into the Payments table. That would be multiple cost records per 1 student-class. It would require a loop to do so for many students at once. The loop would need to check that they do not already exist for that student-class, for whatever the time period is (semester? year?). This loop would be by far the most complex operation of this method, but would only need to be done once for all enrolled, then once for each new student (a button on their Student-Class layout).

I can see ways that a skilled developer could present the same interface and functionality without pre-creating the payment records. But it would be more difficult.

You will have some problem with the above "import costs" method if you receive partial payments. In that case you could just duplicate the record, to create another with the field blank. I believe you'd want to have 2 fields in Payments, one for the original cost, and a blank for the payment.

*BTW, you need a Student-Class "join" table.

[d²c]

How can I import the records from that table. Can you provide a little example for me?

That would be very handy since I need to give the application on wednesday... boohoo

Thanks in advance

Greetz

D