Detecting resource scheduling conflicts with MySQL

by Museful   Last Updated April 16, 2018 11:06 AM

I would like to query the 4 tables defined below to detect resource scheduling conflicts. Conflicts occur when two events (whose reservations) require common participants also have overlapping date/time ranges.

CREATE TABLE `events` (
  reservation int(11),       # refers to reservations.id
  `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),
  `count` smallint(6),
);

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

CREATE TABLE reservation_participations (
  reservation int(11),       # refers to reservations.id
  participants int(11),      # refers to participants.id
  PRIMARY KEY (reservation,resource)
);

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 require mutually exclusive sets of participants. So how to query event-pairs that overlap in both time and participants? Is it possible to do this in a single, reasonably efficient query?

(Eventually, I would like to also list (for each conflicting event-pair) the participants under contention, perhaps using something like GROUP_CONCAT, but that may be a different question.)

Tags : mysql sql


Related Questions


The equivalent of CTE in MySQL from Microsoft SQL

Updated November 15, 2016 08:02 AM


getting innodb buffer usage

Updated August 24, 2018 15:06 PM


MySQL Upgrade on Windows

Updated June 17, 2015 23:02 PM