With PHP and MySQL I’m building a scheduling tool for my friend who tutors students. I’m in doubt in how to set up my database design.

Desired functionality:

• Students submit their unavailabilities for each school day.
• Teacher is presented with a graphic overview of the week, showing availabilities for each student for each 75m time slots.
• Teacher schedules accordingly (clicks on available time slots and assigns to students)
• His schedule is stored in a database.
• (In the future, I’d like to have the tool schedule automatically.)
• Within a school year, every week is the same. Teacher basically just makes a 1 week schedule.
• Though the duration of a lesson is fixed (75m), lessons can start at any minute of the day.

(Note: there are just 15 students or so per school year)

Done so far:

Using fake/manually inputted data, the graphic overview and showing available time slots both work.

But after reading here about never storing array data in a mySQL table, I’m in doubt on how to store the data correctly, how to set up my database design.

Current assumption database design:

• Table “students”, with id, contact details, preferences, etc.
• Table for each student, containing student’s unavailabilities.
• Table for each school day, containing every blocked time slot because of student’s unavailability or because of a scheduled lesson.

Tables would look like this:

Table "Students"
+----------+------+-------+-----
student_id | name | email | ...
+----------+------+-------+-----
331        | John | ...   | ...
145        | Pete | ...   | ...

Table "Student_John"
+-------+------------------+------------
weekday | time_unavailable | student_id
+-------+------------------+------------
2       | 13:00-14:00      | 331    // on Monday, John is unavailable from 13:00-14:00
2       | 16:10-17:00      | 331    // on Monday, John is also unavailable from 16:10-17:00
3       | 08:00-13:00      | 331    // on Tuesday, John is unavailable from 08:00-13:00

Table "Student_Pete"
...

Table "Schoolday_Monday"
+---------+-------------+----------------+------------+--------
record_ID | time        | status         | student_id | weekday
+-------+---------------+----------------+------------+--------
001       | 13:00-14:00 | unavailability | 331        | 2 
002       | 16:10-17:00 | unavailability | 331        | 2 
003       | 16:10-17:25 | lesson_booked  | 145        | 2   // Teacher scheduled a lesson with Peter on Monday 16:10-17:25

Table "Schoolday_Tuesday"
...

Is this the right way to store all my data? And I can use INNER JOIN for presentation?

Thanks for your advice!