MySQL course scheduler

Question

So I have the following table containing the courses available for me to take:

"CREATE TABLE courses 
(
subject varchar(15), 
course int,
day varchar(15),
timein double,
timeout double,
courseID int
)";

For example: subject= ECON, course= 101, day= M, timein= 9.0, timeout= 10.3, courseID= 11. I need to find a MySQL script that will give me all possible combinations of the courses without having a time conflict. Thanks

When you say "timeout=10.3" are you thinking that would be 10:30? Would it not perhaps - assuming everyone has the same schedule more or less - be more sensible to have a set of standard timeslots in a seperate table so that slot 1 is 9-10, slot 2 is 10-11 and so on? That would simplify your searches a whole lot. — glenatron, Dec 07 '10 at 14:04
Just to clarify, given these three course times: 0900-1000, 1000-1200, 1130-1230. Do you want to return (0900-1000, 1000-1200) and (0900-1000, 1130-1230)? That is, for every course, find all the other courses which do not overlap. If so, I think you'll need to look at [De Morgan's](http://en.wikipedia.org/wiki/De_Morgan%27s_laws) law, an example of which can be found [here](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap). — Mike, Dec 07 '10 at 14:06

score 0 · Answer 1 · answered Dec 07 '10 at 14:06

0

This query searches all the courses who start in the given courses expect the courses it self.

SELECT *
FROM `courses`
WHERE `day` LIKE 'M'
AND `timein` >= 9.0 AND `timein` < 10.3
AND `courseID` !=11

answered Dec 07 '10 at 14:06

ynh

2,919
1
19
18

score 0 · Answer 2 · answered Dec 07 '10 at 14:53

So to extend my suggestion in the comments, I would perhaps normalise your table thus:

CREATE TABLE course
{
   ID int
   subject varchar(100)
}

CREATE TABLE lesson  
 ( 
    timeslotID int
    courseID int 
 ); 

CREATE TABLE timeslots
(
   ID int,
   day varchar(10),
   timein double,
   timeout double
)

You could also leave the day in the lesson table and have fewer timeslots, that makes most difference to how you query the data. Either way, your clash query would be a simple matter of finding all the matching timeslots. It is likely to result in more JOINs in your queries but much less duplication of data.

Obviously the restriction here is that your timeslots have to be discrete so if you had some lessons starting at 10:00 and some at 10:30 you would need to have 30 minute timeslots rather than an hour, for example, but if you're looking at a school or college I think this would probably be fine.

score 0 · Answer 3 · answered Dec 07 '10 at 15:30

All possible combinations is an n-way cross product (join).

SQL can't figure out how many distinct time slots there are AND construct a select statement with that many joins. So you will have to punt and do that yourself.

Hence, for each unique time slot, join on all courses where the course does not overlap.

The resulting set of rows will N x M where N is the number of unique time slots and M is the number of possible combinations.

score 0 · Answer 4 · answered Dec 07 '10 at 15:53

This might be a simplified version of what your are after - it doesn't include days, but that should be fairly simple to add in:

An example table with some data:

CREATE TABLE `courses` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `time_in` time DEFAULT NULL,
  `time_out` time DEFAULT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB;

INSERT INTO courses VALUES
(1, '09:00', '10:00'),
(2, '09:30', '10:30'),
(3, '10:00', '11:00'),
(4, '10:30', '11:30'),
(5, '12:00', '13:00');

For every row (each timeslot), determine if there are any other rows that do not overlap. Note that this allows for end and start times that are the same:

SELECT
  c1.course_id,
  c1.time_in,
  c1.time_out,
  c2.course_id,
  c2.time_in,
  c2.time_out
FROM courses AS c1
JOIN (
  SELECT course_id, time_in, time_out FROM courses
) AS c2
ON (c1.time_out > c2.time_in) XOR (c1.time_in < c2.time_out)
ORDER BY c1.course_id, c1.time_in;

+-----------+----------+----------+-----------+----------+----------+
| course_id | time_in  | time_out | course_id | time_in  | time_out |
+-----------+----------+----------+-----------+----------+----------+
|         1 | 09:00:00 | 10:00:00 |         3 | 10:00:00 | 11:00:00 |
|         1 | 09:00:00 | 10:00:00 |         4 | 10:30:00 | 11:30:00 |
|         1 | 09:00:00 | 10:00:00 |         5 | 12:00:00 | 13:00:00 |
|         2 | 09:30:00 | 10:30:00 |         4 | 10:30:00 | 11:30:00 |
|         2 | 09:30:00 | 10:30:00 |         5 | 12:00:00 | 13:00:00 |
|         3 | 10:00:00 | 11:00:00 |         1 | 09:00:00 | 10:00:00 |
|         3 | 10:00:00 | 11:00:00 |         5 | 12:00:00 | 13:00:00 |
|         4 | 10:30:00 | 11:30:00 |         5 | 12:00:00 | 13:00:00 |
|         4 | 10:30:00 | 11:30:00 |         2 | 09:30:00 | 10:30:00 |
|         4 | 10:30:00 | 11:30:00 |         1 | 09:00:00 | 10:00:00 |
|         5 | 12:00:00 | 13:00:00 |         1 | 09:00:00 | 10:00:00 |
|         5 | 12:00:00 | 13:00:00 |         2 | 09:30:00 | 10:30:00 |
|         5 | 12:00:00 | 13:00:00 |         3 | 10:00:00 | 11:00:00 |
|         5 | 12:00:00 | 13:00:00 |         4 | 10:30:00 | 11:30:00 |
+-----------+----------+----------+-----------+----------+----------+

MySQL course scheduler

4 Answers4