View Single Post
Old 07-14-09, 10:01 PM   #2
Join Date: Mar 2004
Posts: 15,487
Default Re: Timetable in MySQL

Originally Posted by Tuork View Post
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"
ViN86 is offline   Reply With Quote