Help: Database Design For Music Lesson tracking system

[overflowing]

I'm creating a database for a small school that offers music lessons. Currently the contact information and records for the students' lessons and payments are all on paper.

The requirements of the database as I understand them so far are:

• track the contact information of the students
• track lessons taken by students (which could be private or small group but I'll ignore that complication for the moment)
• track payments

The part that I'm having difficulty with is coming up with a flexible and accurate payment system that relates to the lessons they've taken. Initially I was thinking that simply:

1. a student pays for x number of lessons and that adds to a calulated total of "total number of lessons paid for"
2. a student takes a lesson so that adds to the "total number of taken lessons"
3. create a lesson "balance" = totalpaidfor - totaltaken

So the schema that I first thought of was this:

STUDENTS{StudentID, FirstName, LastName, Address, City, etc....}
PURCHASES{PurchaseID, StudentID, Date, NumberOfLessons, Price}
PRIVATE_LESSONS{PrivateLessonID, StudentID, Date}
​

However, this design won't work because some teachers have different rates and students can take from different teachers. Students can pay for lessons one at a time or they can purchase packages of lessons. Students can have a package of lessons with one teacher and a different package of lessons with another teacher.

So then I was thinking of creating some kind of "contract" table, which stipulates x number of lessons for y price and then including a reference to the particular contract in the private lesson table. Or maybe instead I could just keep track of total money paid and then add a "rate" field to the lesson table, so that as students take lessons, I subtract the rate, which could be different for each lesson, from the balance. Anyway, I could go on, but any ideas for how to best do this?

 

[lightray]

Hi, You seem to have left the Teachers out of your schema, your PURCHASES seems to be your contract. Try and keep it simple.
Student goes to Teacher (who has the contract rate) if a teacher has multiple contract rates then you may need a contracts table.
Instead of tblPrivateLessons (note wording) maybe make it a tblLessons table where they can be private, group, anything else.. This will indicate a lesson took place. Multiply that by the Teachers Rate
hope that helps, lightray

 

[Dreamweaver]

I think I've seen a wizard Database for something like this on the ms site check out the wizard databases I'm sure there's one that will give you a very good start.

mick