Multi-class, interlinked school timetable as a MySQL database

Question

I've looked around for a bit now at other suggestions relating to this, but nothing I've seen has quite suited my needs, so here goes!

What I have is a multi-class (Australian secondary school; Years 7-12), multi-day (Mon-Fri) school timetable. What I now want to build is a MySQL database with the following information to be deployed on an account driven website.

• Subjects:
  
  • Running time (as "Period 1 on Wednesday", "Period 2 on Friday", etc. -- multiple values in this column)
  • Instructor (linked to separate database of all teachers) -- This would additionally need to change (temporarily) if a teacher was sick and replaced; perhaps a "replacementinstructor" column to be ignorned when NULL.
  • Location (different, but specifically allocated, rooms on different days) -- As above, change temporarily when room altered.
  • Other obviousnesses: Course name ("Year 7 Health"), Unique ID (Something like "7.HEALTH", rather than just auto-incrementing INT.), etc.

• Teachers:
  
  • First name, last name
  • Courses they take
  • Contact info
  • Other obviousnesses: Unique ID (Auto-incrementing INT), Username (fname.lname), Password for their account, etc.

• Students:
  
  • First name, last name
  • Courses they attend (stored as an individual list for each student)
  • Year level / Form (Year 7, Year 11, etc.)
  • Basic personal info (Home suburb, email, etc.)
  • More obviousnesses: Unique ID (same setup as teachers), Username (same as teachers), password, etc.

Any insight as to how I might design such a data structure would be greatly appreciated, I'm more of a UI fanatic than a MySQL thinker ;-D

Thanks in advance.

Answer 1

I can think of the following tables to use in MySQL:

students
Student information

• id (auto_increment)
• firstname
• lastname
• username
• password
• student_id (I had a student ID but I can't remember if I was given this in yr 7 or yr 10)
• year
• email
• contact_phone
• street
• suburb
• state (ENUM - ACT,NSW,WA,SA,TAS,VIC,NT,QLD)

teachers
Teacher information

• id (auto_increment)
• firstname
• lastname
• title (Dr, Mrs, etc)
• username
• password
• email
• contact_phone
• street
• suburb
• state (ENUM - ACT,NSW,WA,SA,TAS,VIC,NT,QLD)

subjects
All the different subjects

• id (auto_increment)
• subject
• subject_code (eg 7.HEALTH)
• year

locations
Various locations around the school

• id (auto_increment)
• location (eg BLOCK A, ROOM 2, MUSIC ROOM)

subject_teachers
What subjects each teacher teaches

• id (auto_increment)
• subject_id
• teacher_id

subject_students
Which subjects each student takes

• id (auto_increment)
• subject_id
• student_id

subject_timetable
Main Timetable

• id (auto_increment)
• subject_id
• location_id
• teacher_id
• alt_teacher_id (eg substitute teacher)
• duration
• period (number 1-however many periods in a day. 6 if I remember correctly)
• week (number 1-2 or even perhaps 1-52)
• weekday (number 1-5)
• notes (as @Oswald suggested you could add additional notes for when things change)

The notes could be collated and then displayed as footnotes on the timetable.

Answer 2

You will obviously need a table for Subjects, a table for Students and a table for Teachers.

Read up on database normalization. This will tell you (amongst other things):

• Do not put the running time into the Subject table as a comma separated list. Instead use a table for the running time and use a foreign key to map entries from that table to Subjects table.
• Same goes for mapping teacher to courses.
• Same goes for mapping students to courses.
• That Running Time table would also be suitable for holding the location of a course during a specific running time.

Additionally, if you record temporary changes in the database, you are dependent on people changing the relevant information at a specific time. To get around this, you might want to consider a table Lessons, where you record

• Running time
• Week number
• Course
• Instructor
• Location
• Other fields that might be specific to that particular lesson.

This will allow you to schedule changes in advance (Mr. McCachney is sick for the next two weeks, Room 101 is closed for redecoration for a month, etc.)