I'm looking to create a new database for a new client (we've finally moved on from our restaurant database!) It is for the music department of our school. However I need help constructing the table structure - I thought I'd created it fine before, but it doesn't seem to be working as I'd hoped.
Can anybody help me?
I've included a list of the elements of the department so that you can get the best idea possible of what I need.
Thanks very much
About the Music Suite
We have a dedicated instrumental section of our school.
Many different instruments are taught by many different teachers, who work part-time and organise payment privately with the students.
About the teachers
Teachers may teach more than one instrument,
e.g. Piano and Flute.
There may be more than one teacher per instrument,
e.g Three teachers might teach Piano.
Teachers come in on set days, according to their individual work schedules,
e.g Wednesdays and Fridays.
A teacher might teach the same instrument on two or more different days,
e.g. Piano, on Wednesday and Friday.
A teacher might teach a number of different instruments across different days,
e.g Piano on Wednesday, Flute on Friday.
Teachers have different time slots, according to their work schedules,
e.g. Mrs Smith might have Flute lesson slots at 8:30, 10:15 and 1:30 on a Wednesday, while Mrs Jones might have Piano lesson slots at 9:00, 12:00 and 3:00 on a Friday.
Teachers have different lunch break times.
e.g. Mrs Smith might have her lunch break at 12:00 on a Wednesday, while Mrs Harris might have hers at 1:30 on the same day.
About the lessons
Each lesson lasts for 30 minutes only.
Lesson slot times are unique according to the combination of tuition, teacher, room and day.
Each week, lesson times for each student are rotated so that they don’t miss the same academic lesson twice in a row,
e.g. Emma Jones might have Flute with Mrs Smith on a Wednesday in room P3. Last week it was at 1:30pm; this week it is at 8:30am.
If a student is in Year 12, they have the option of securing their lesson during a study break.
e.g. Hayley Thompson has her Year 12 study break at 12:00 on a Friday, and so she is permitted to have a ‘permanent’ Piano lesson time at 12:00, so that it doesn’t change from week to week.
The Music Suite officer needs to schedule these lesson times each week, and print a list of them to pin on the notice board. They need to be numbered with the current school week.
Lesson times need to be able to be changed quickly, as students often swap lesson times due to absences or excursions.
The Music Suite officer wants to be able to have a main form for lessons – that is, the combination of teacher, tuition, day and room – and then a subform for the students studying that instrument, with that teacher on that day and in that room.
She also wants to be able to view a drop-down list of lesson slot times unique to each lesson (combination of teacher, tuition, day and room) so she can quickly and easily select student lesson times.
The drop-down list should automatically hide any values already selected for another student, so that no double-booking can occur and she can simply view all remaining times.
e.g. If 8:30, 9:30 and 10:30 slots have already been taken by students for a certain day, then only 11:30 and 12:30 would remain in the drop-down list.
Also, the time slot for a student’s permanent lesson time should not be visible; in fact, that student’s record should be greyed out so that the officer knows she doesn’t have to re-schedule it each week.
About the students
Students might learn more than one type of instrument, e.g. Piano and Flute.
Students will study with just one teacher per instrument.
Students will have only one lesson for one instrument per week, e.g Only one Flute lesson per week.
Students might, however, have two lessons for two different instruments per week, e.g One for Flute, another for Piano.
Students will have only one teacher for one instrument per year, unless changed in special cases.
About the rooms
There are a number of private instrumental lesson rooms in and around the Music Suite, e.g. P2, P3, P4
If an instrumental teacher uses this room to teach their students, it will be booked for the entire day.