Mysql – Detecting resource scheduling conflicts with MySQL

MySQL

How to query the 3 tables below to detect resource scheduling conflicts? Conflicts occur when two events with overlapping date/time ranges require common participants.

CREATE TABLE `events` (
  id int(11) AUTO_INCREMENT,
  `date` date,               # date on which the event occurs
  start_minute smallint(6),  # minute of day on which event starts
  end_minute smallint(6),    # minute of day on which event ends
);

CREATE TABLE participants (
  id int(11) AUTO_INCREMENT,
  `name` varchar(255),
);

CREATE TABLE event_participations (
  event int(11),             # refers to events.id
  participant int(11),       # refers to participants.id
  PRIMARY KEY (event,participant)
);

I think the following query would detect overlapping events:

SELECT *
FROM events e CROSS JOIN events e2
WHERE e.date=e2.date AND e.start_minute<e2.end_minute AND e2.start_minute<e.end_minute

However, overlaps do not imply conflicts as the two events may involve mutually exclusive sets of participants. How to query for event-pairs that require the same participants at the same time?

Best Answer

Either like this:

SELECT DISTINCT 
'this events and participations overlap',
e.*
FROM events e 
JOIN event_participations ep ON e.id = ep.event
WHERE EXISTS (SELECT 1 
              FROM events e2
              JOIN event_participations ep2 ON e2.id = ep2.event 
              WHERE e.date = e2.date 
                AND e2.start_minute < e.end_minute 
                AND e2.end_minute > e.start_minute
                AND ep2.participant = ep.participant
             )

or like this:

SELECT DISTINCT 
'this events and participations overlap',
e.*
FROM events e 
JOIN event_participations ep ON e.id = ep.event
JOIN events e2 ON  e.date = e2.date 
               AND e2.start_minute < e.end_minute 
               AND e2.end_minute > e.start_minute
JOIN event_participations ep2 ON e2.id = ep2.event 
WHERE ep2.participant = ep.participant

The nice thing about EXISTS is, that it stops to search as soon as it hits. The JOIN approach builds the whole data set.

Related Question