Detecting resource scheduling conflicts with MySQL

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

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
  `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
  participants int(11),      # refers to
  PRIMARY KEY (reservation,resource)

I think the following query would detect overlapping events:

FROM events e CROSS JOIN events e2
WHERE 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