0

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?

0

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.

  • Thank you. I'm just concerned about efficiency as event_participations would be by far the largest table, and this solution (in my novice understanding) would in principle query the entire largest table for each entry in the entire largest table. – Museful Apr 16 '18 at 14:29
  • It's fully covered by your primary key. I wouldn't worry until I tried it out and performance really is an issue. Until then don't worry. And you want to have the information, don't you? So bite the apple. – tombom Apr 17 '18 at 5:53
  • It just feels like a "brute force" way to structure the query, although I don't have a better idea. With the DB just starting out (200 participants, 200 events, 2000 event_participations) the query already takes a couple of seconds. I imagine that (because of the query structure) the running time would increase quadratically with size in event_participations. – Museful Apr 17 '18 at 8:23
  • With that few data your server is seriously wrong configured or your server is a potato, when this query takes several seconds. Did you change the configuration of your server? If not, the first step would be for example to raise the innodb_buffer_pool to 80% of your RAM (assuming you use InnoDB). And are those really auto_increment columns? Because then they must also be primary key, but I don't see that info in your create table statements. I really wouldn't expect bad performance on a reasonably configured server with halfway decent hardware. – tombom Apr 17 '18 at 8:30
  • Perhaps, if you fear for performance, you could ignore events from the past? – Gerard H. Pille Apr 17 '18 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.