2

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.

nhahtdh
  • 55,989
  • 15
  • 126
  • 162
Mr. Sanders
  • 51
  • 2
  • 7
  • 1
    You should consider working on a tool like MySQL Workbench. Lets you design the tables and the connections visually. What you're asking for is pretty straightforward – JohnP Apr 23 '11 at 08:43
  • I would make periods its own table and link it to subjects – JohnP Apr 23 '11 at 08:44
  • Would this be to create individual timetables for each student or a general timetable for each grade? Because for example you might have 3 different English classes running at the sametime - (General, Extension 1, Extension 2 or whatever they call it now) - So would you be displaying this as just "English" or the particular variant for each student - if that makes any sense. – Ben Apr 23 '11 at 09:10
  • For extra coolness you could make it into an iPad/iPhone/Android app. Or just optimise your website for iPad/iPhone/Android screen sizes. – Ben Apr 23 '11 at 09:21

2 Answers2

7

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.

Ben
  • 1,382
  • 10
  • 14
  • ``If you're using a Mac you might consider using Sequel Pro (http://www.sequelpro.com/) to setup your database.`` Otherwise if you need a web-based solution, then phpmyadmin would be the most popular. – Ben Apr 23 '11 at 09:13
  • Thanks very much! Sequel Pro looks amazing, but I run my server on Linux ;-) Your instructions worked beautifully, though! Thanks again. – Mr. Sanders Apr 24 '11 at 01:52
  • u are a star this is what we call an answer – Humphrey Jul 09 '19 at 19:34
3

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

Oswald
  • 31,254
  • 3
  • 43
  • 68