Go Back   nV News Forums > Software Forums > Software Development

Newegg Daily Deals

Reply
 
Thread Tools
Old 07-14-09, 03:12 PM   #1
Tuork
Official pain in the ass
 
Tuork's Avatar
 
Join Date: May 2003
Location: 127.0.0.1
Posts: 1,197
Send a message via MSN to Tuork
Default Timetable in MySQL

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!
__________________
My avatar: "An obscure and non sensical pattern made to get people to post what they think it is" - Vamp

Dimitri:
Dell XPS M1530
Core2Duo T5550 1.86Ghz | 3GB DDR2 | 8600M GT 256MB | 250GB Hdd | BenQ FP202W 20" LCD | MX1000 mouse | Inspire T5400 5.1
Tuork is offline   Reply With Quote
Old 07-14-09, 10:01 PM   #2
ViN86
 
Join Date: Mar 2004
Posts: 15,486
Default Re: Timetable in MySQL

Quote:
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
Old 07-14-09, 10:58 PM   #3
Tuork
Official pain in the ass
 
Tuork's Avatar
 
Join Date: May 2003
Location: 127.0.0.1
Posts: 1,197
Send a message via MSN to Tuork
Default Re: Timetable in MySQL

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!
__________________
My avatar: "An obscure and non sensical pattern made to get people to post what they think it is" - Vamp

Dimitri:
Dell XPS M1530
Core2Duo T5550 1.86Ghz | 3GB DDR2 | 8600M GT 256MB | 250GB Hdd | BenQ FP202W 20" LCD | MX1000 mouse | Inspire T5400 5.1
Tuork is offline   Reply With Quote
Old 07-15-09, 12:35 AM   #4
ViN86
 
Join Date: Mar 2004
Posts: 15,486
Default Re: Timetable in MySQL

Quote:
Originally Posted by Tuork View Post
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.
ViN86 is offline   Reply With Quote
Old 07-15-09, 02:00 AM   #5
Tuork
Official pain in the ass
 
Tuork's Avatar
 
Join Date: May 2003
Location: 127.0.0.1
Posts: 1,197
Send a message via MSN to Tuork
Default Re: Timetable in MySQL

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?
__________________
My avatar: "An obscure and non sensical pattern made to get people to post what they think it is" - Vamp

Dimitri:
Dell XPS M1530
Core2Duo T5550 1.86Ghz | 3GB DDR2 | 8600M GT 256MB | 250GB Hdd | BenQ FP202W 20" LCD | MX1000 mouse | Inspire T5400 5.1
Tuork is offline   Reply With Quote
Old 07-15-09, 09:17 AM   #6
ViN86
 
Join Date: Mar 2004
Posts: 15,486
Default Re: Timetable in MySQL

Quote:
Originally Posted by Tuork View Post
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.
ViN86 is offline   Reply With Quote
Old 07-15-09, 04:32 PM   #7
Tuork
Official pain in the ass
 
Tuork's Avatar
 
Join Date: May 2003
Location: 127.0.0.1
Posts: 1,197
Send a message via MSN to Tuork
Default Re: Timetable in MySQL

Quote:
Originally Posted by ViN86 View Post
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!!!!!

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

Quote:
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.
__________________
My avatar: "An obscure and non sensical pattern made to get people to post what they think it is" - Vamp

Dimitri:
Dell XPS M1530
Core2Duo T5550 1.86Ghz | 3GB DDR2 | 8600M GT 256MB | 250GB Hdd | BenQ FP202W 20" LCD | MX1000 mouse | Inspire T5400 5.1
Tuork is offline   Reply With Quote
Old 07-16-09, 09:20 AM   #8
ViN86
 
Join Date: Mar 2004
Posts: 15,486
Default Re: Timetable in MySQL

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.)
ViN86 is offline   Reply With Quote

Old 07-16-09, 12:41 PM   #9
Tuork
Official pain in the ass
 
Tuork's Avatar
 
Join Date: May 2003
Location: 127.0.0.1
Posts: 1,197
Send a message via MSN to Tuork
Default Re: Timetable in MySQL

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.
__________________
My avatar: "An obscure and non sensical pattern made to get people to post what they think it is" - Vamp

Dimitri:
Dell XPS M1530
Core2Duo T5550 1.86Ghz | 3GB DDR2 | 8600M GT 256MB | 250GB Hdd | BenQ FP202W 20" LCD | MX1000 mouse | Inspire T5400 5.1
Tuork is offline   Reply With Quote
Old 07-16-09, 12:48 PM   #10
ViN86
 
Join Date: Mar 2004
Posts: 15,486
Default Re: Timetable in MySQL

Quote:
Originally Posted by Tuork View Post
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.
ViN86 is offline   Reply With Quote
Old 07-16-09, 06:20 PM   #11
Tuork
Official pain in the ass
 
Tuork's Avatar
 
Join Date: May 2003
Location: 127.0.0.1
Posts: 1,197
Send a message via MSN to Tuork
Default Re: Timetable in MySQL

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.
__________________
My avatar: "An obscure and non sensical pattern made to get people to post what they think it is" - Vamp

Dimitri:
Dell XPS M1530
Core2Duo T5550 1.86Ghz | 3GB DDR2 | 8600M GT 256MB | 250GB Hdd | BenQ FP202W 20" LCD | MX1000 mouse | Inspire T5400 5.1
Tuork is offline   Reply With Quote
Old 08-03-09, 03:27 PM   #12
ViN86
 
Join Date: Mar 2004
Posts: 15,486
Default Re: Timetable in MySQL

So how's the project coming?
ViN86 is offline   Reply With Quote
Reply


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -5. The time now is 02:39 PM.


Powered by vBulletin® Version 3.7.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 1998 - 2014, nV News.