Timetable in MySQL

[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!

[ViN86]

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" ;)

[Tuork]

Hmm.. this could work. The only thing I'm worried about is what to do when something isn't exactly half an hour.

I guess I would have to manage 30 minute time slots, but that would mean a lot of records on the "free periods" table.

Oh, and thanks for your help!

[ViN86]

Hmm.. this could work. The only thing I'm worried about is what to do when something isn't exactly half an hour.

I guess I would have to manage 30 minute time slots, but that would mean a lot of records on the "free periods" table.

Oh, and thanks for your help!

Why does it need to be 30 minute intervals? Use a time field and you can set it to hours and minutes.

[Tuork]

Well, let's say Tutor Andy has the following free period: Mondays, 1:00pm - 2:30pm.

Now let's say that little Billy wants Maths classes on Monday, from 1:00pm to 2:00pm.

I'd look up free time periods that match Billy's requirements, which would give me Andy's free period. The problem here is that the time periods don't match exactly, so I would need some way to re-define Andy's free period so that it no longer presents the initial hour. I'd have to mess with time differences, etc, which would make the algorithmic a tad more complicated than if, say, I used 30 minute periods all the time.

Does that make any sense?

[ViN86]

Well, let's say Tutor Andy has the following free period: Mondays, 1:00pm - 2:30pm.

Now let's say that little Billy wants Maths classes on Monday, from 1:00pm to 2:00pm.

I'd look up free time periods that match Billy's requirements, which would give me Andy's free period. The problem here is that the time periods don't match exactly, so I would need some way to re-define Andy's free period so that it no longer presents the initial hour. I'd have to mess with time differences, etc, which would make the algorithmic a tad more complicated than if, say, I used 30 minute periods all the time.

Does that make any sense?

Sure, but you don't expect to have more than 12 hour shifts right? So a simple function to subtract the times should be pretty easy. :)

EDIT: You could also try storing all start/end times as the number of seconds. Then a subtraction would be easy and converting back to the time could be done with a simple function as well.

[Tuork]

Sure, but you don't expect to have more than 12 hour shifts right? So a simple function to subtract the times should be pretty easy. :)


By itself a single shift is no biggie. The problem resides in the number of Tutors/Students that the system will manage. We're talking about something like 200 students and 50 tutors. Take into account the number of days in a week and say 12 hour "shifts", and you get approximately

200*50*7*12 = 840,000 records!!!!! :o

I could index it by user ID, but still, searching though all those records will prove to be taxing for the database manager.


EDIT: You could also try storing all start/end times as the number of seconds. Then a subtraction would be easy and converting back to the time could be done with a simple function as well.

Could do that as well. Guess I'll look into the specifics once I'm developing, but for now it's good to have the general idea of what to do.

[ViN86]

Why do you need a record for all students/tutors at all times? Just add them as you need them.

Don't make a record for empty tutor hours. If we have 200 tutors and 20 classes, assuming each tutor has a time in each class, for all 5 days of the week, then the number of records is only 20000. A large reduction from the system you're proposing. Also, that is the maximum, since most tutors will have a few classes but not every day.

Here's what I was thinking:

Table 1:
List of students and tutors (studentID,Student or Tutor,Name,email,etc.)

Table 2:
List of courses (courseID,name,room,start_time,end_time)

Table 3:
List of tutor times (entryID,courseID,studentID,start_time,end_time)

Then, don't keep an entire record of all times. Just add entries to the tutor times (table 3) as you get them. Then if you want to check if there is a tutor for a class at a certain time, run a query and if you get no rows returned, then there isn't a tutor. If you still think the tutor times will be too large, then split it up by day and create a table for each day of the week (ie MondayTutorTimes, TuesdayTutorTimes, etc.)

[Tuork]

That's pretty much what I was thinking, but I also need to store availability (what I referred to as "free periods" before) seeing as most tutors are Uni students and only have certain hours of the day available.

Maybe for that it would be a good idea to use your suggestion and break up the table into days of the week, that way it'll still be a large amount of records for availability, but it'll be much easier to search through.

[ViN86]

That's pretty much what I was thinking, but I also need to store availability (what I referred to as "free periods" before) seeing as most tutors are Uni students and only have certain hours of the day available.

Maybe for that it would be a good idea to use your suggestion and break up the table into days of the week, that way it'll still be a large amount of records for availability, but it'll be much easier to search through.

I guess I don't understand why it will be so large still.

Make a classID called "Free" and have them registered for that when they are not in another class. Or if you return no rows on a query for the tutor times, just assume they are free.

Also, why are you creating time blocks? Use MySQL's ability to store times in the table and enter complete times (ie 2:34PM or 14:34, whichever you prefer). Then when you make entries in the table for a class, make sure that the time you entered falls within the class's times. If you add some error checking code, you can grossly simplify the DB.

[Tuork]

I'm just thinking about this off the top of my head. Once I get some actual analysis and design done I'll probably have a better understanding of the DB.

The reason why I need to know when a tutor has free time is because if he/she is not schedulled for a tutoring session at a particular time, it doesn't mean that they are free. They might have their weekly lunch with Granny or something of the sort. Consequently, I need to know what times of the day they are available.

[ViN86]

So how's the project coming?

[Tuork]

Well, we're still waiting on the client to make up his mind. It came down to us, and another company. No word just yet :(

[ViN86]

Do I get a consulting fee? :wonder:

[Tuork]

You sure do!

Here you go:

http://z.about.com/d/politicalhumor/1/0/J/9/bush_200bill.jpg


:D

[ViN86]

Sweet, W's the man.

[t3hl33td4rg0n]

Double purpose post here....

What about using time units/points? Cable companies generally have a quota for field technicians.

Example:




instructorPool

uid instructorName slotStart slotEnd totalSeats
1 Smith, Jane 08:15 10:00 5
2 Smith, Jane 10:15 12:00 12
3 Smith, Jane 13:00 15:00 6
4 Smith, Jane 15:15 17:00 9
5 Dover, Ben 08:15 10:00 5
6 Dover, Ben 10:15 12:00 10
7 Dover, Ben 13:00 15:00 4
8 Dover, Ben 15:15 17:00 5
9 Barbaz, Foo 08:15 10:00 12
10 Barbaz, Foo 10:15 12:00 11
11 Barbaz, Foo 13:00 15:00 9
12 Barbaz, Foo 15:15 17:00 7



studentPool

uid studentName status creditHours totalUnits
1 poopiepants, paul active 16 48
2 rocket, johnny active 12 36
3 jenkins, leeroy active 6 18
4 ventura, ace active 8 24
5 mark, marky active 14 42
6 gates, bill active 16 48


registeredSessions

uid slotRegd studentRegd regDate regTime
1 2 jenkins, leeroy 2010-02-08 06:00
2 4 rocket, johnny 2010-02-08 08:30
3 2 poopiepants, paul 2010-02-08 08:30
4 2 ventura, ace 2010-02-09 13:00
5 4 gates, bill 2010-02-08 06:00
6 4 poopiepants, paul 2010-02-09 13:00
7 2 gates, bill 2010-02-10 09:15


So lets say you want to see how many slots are open for Jane Smiths 10:15 - 12:00


myQuery = SELECT * FROM `instructorPool` WHERE `instructorName` = "Smith,Jane" AND `slotStart` = "10:15" LIMIT 0,1

You get:

uid instructorName slotStart slotEnd totalSeats
2 Smith, Jane 10:15 12:00 12

Then:

studentsRegistered = SELECT * FROM `studentPool` WHERE `slotRegd` = myQuery # Get people registered for the class

You get:

uid slotRegd studentRegd regDate regTime
1 2 jenkins, leeroy 2010-02-08 06:00
3 2 poopiepants, paul 2010-02-08 08:30
4 2 ventura, ace 2010-02-09 13:00
7 2 gates, bill 2010-02-10 09:15


Then:

howMany = mysql_num_rows (studentsRegistered) # How many people registered for this slot? (4)

slotsOpen = myQuery[totalSeats] - howMany # How many seats open (8)

if (slots < 1) {

echo 'Sorry, you cant register for this slot <br />';

echo 'Here are the people who are registered.';

echo 'blah blah list of names blah blah';

} else {

Now lets find out if the student has the available units to reg (We will say bill gates is logged in)


getStudentInfo = SELECT `totalUnits` FROM `studentPool` WHERE `studentName` = NameofLoggedInStudent
totalSlotsQuery = SELECT `slotRegd` FROM `registeredSessions` WHERE `studentRegd` = NameofLoggedInStudent


You get:


totalUnits
48

slotRegd
4
2


Now:

studentUsedUnits = mysql_num_rows (totalSlotsQuery) * 3 # Has used 6 of 48 credits

freeUnits = totalSlotsQuery[totalUnits] - studentUsedUnits


if (freeUnits > (slotsOpen * 3)) {

echo "Would you like to register for this class?";

} else {
echo "You dont have enough credit hours to register for this class"

}



I think you probably get the idea at this point.

[Tuork]

Holy thread revivals, batman!

[t3hl33td4rg0n]

Holy thread revivals, batman!

It was more practice than anything... Started working on a new CMS.

But hey, someone might find it useful!

[Tuork]

Thanks for the input. :)

I was just taken aback by this thread's random revival.

[seozest]

I think you need simplified db for this for which you can use error checking code.
As suggested above.Try to make it less complicated.

----------------------------------------
Outsourced product development
www.e-zest.net

[t3hl33td4rg0n]

lol wut?