Search code examples
phpmysqldata-structurestimetable

Multi-class, interlinked school timetable as a MySQL database

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.

Solution

  • 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.