Help: Database Design For Music Lesson tracking system

[User]

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?

 

[TonyT]

Hi,

how about;
tblStudent {StudentID,{Firstname}, other student info {Balance}***
tblTutor {TutorID}, {TutorName}, other Tutor only info.
tblLessonType {LessonTypeID}, {LessonName}, {Lesson Description}
tblTutorRate {TutorRateID}, {TutorID}, {LessonTypeID}, {TutorRate}<per lesson
tblLessonBooked {LessonBookedID}, ,{StudentID}, {TutorRateID}, {Date},
{NumberOfLessons}, {Cost} <- not required for anything more than accounting
purposes to track price paid after a Tutor's rate changes.

***The tblStudent.Balance field can reduce by the value of
tblLessonBooked.Cost each time a course is booked, and a calculation on it
can be made to determine which tutor/course/duration can be afforded with the
balance.

Adding the tblTutorRate table allows 1 tutor to charge different rates for
different courses, but assumes the tutor will charge the same to each
student, if this is also a variable rate dependant say on grade studied, then
another field in tblLessonType of Grade could be added.

Hope that helps,

TonyT..

 

[User]

Thanks for the reply. Is having a balance field inside the student
table a good idea? 1. It seems like a calculated field and 2. It seems
like it's not related to the entity "student".

 

[TonyT]

Hi again,

The balance field is the amount of money they deposited, as yet un-assigned
to a course/tutor & available to spend on future courses, so cannot be
calculated by other means, and it still *belongs* to the student until
spent/allocated.
Obviously I'm making assumptions about how your setup might work on very
limited information, you will have to edit as you see fit depending on your
exact requirements.

hope that makes sense,

TonyT..