Originally Posted by Tuork
I'm about to start development on a project where I need to store a timetable for a series of users. Basically, I'm talking about Tutors and Students, where I need to store the Tutor's available hours, as well as the classes they are teaching.
How can you go about storing a timetable in MySQL? I don't need the specific code, but something more like the logical idea behind it.
So far what I've come up with would be a table of "free periods", where each period would consist of a Start time/date, an End time/date and the ID of the person that has that time period free.
Similarly, "Occupied periods" could consist of Start/End times, user ID, and Student ID.
The problem I would see with this is when it comes to assigning a new class to the tutor. It would be slightly complicated to know which fields I want to modify on the "free periods" table.
I would very much appreciate any suggestions!
That is exactly what I was about to recommend. You can use the Date/Time field (for the column data type) and use a Start/End time.
Add a class ID column as well in the "free periods" table, that way you can search for the start/end time of a specific tutor in a specific class
Then you can do "SELECT `start_time` WHERE `userid`=num AND `classid`=num2"