How should I go about modeling event duration

Question

Env: mysql

How should I go about modeling the event duration for the following scenarios...

Today is Monday (start of the week)

One day event scheduled between 10 AM - 5 PM today.
Every day event open from 10 AM - 11 AM from today till 1 week. (inclusive of weekends)
Every day event open from 10 AM - 11 AM from today till 2 weeks. (exclusive of weekends)

possible duplicate of [What is the best way to optimize schema for capturing attendance data](http://stackoverflow.com/questions/3193227/what-is-the-best-way-to-optimize-schema-for-capturing-attendance-data) — APC, Jul 26 '10 at 13:56
I don't think this is a duplicate, this is more specific to modeling the duration (which can be quite simple or as complex as [3]) — Sam, Jul 26 '10 at 16:34

the_void · Accepted Answer · 2010-07-26T18:19:17.383

I would choose the easy way:

CREATE TABLE `scheduler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `startDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `endDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `weekendIncluded` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

EDIT:

In the light of recent comments, a solution that would also support recurrent events and a more fine-grained time interval selection would be:

CREATE TABLE `events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

This table holds all the event information (i.e. name, ...).

CREATE TABLE `scheduler` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(11) unsigned DEFAULT NULL,
  `isRecurrent` tinyint(4) DEFAULT '0',
  `startDate` date DEFAULT NULL,
  `endDate` date DEFAULT NULL,
  `startTime` time DEFAULT NULL,
  `endTime` time DEFAULT NULL,
  `onMonday` tinyint(4) NOT NULL DEFAULT '0',
  `onTuesday` tinyint(4) NOT NULL DEFAULT '0',
  `onThursday` tinyint(4) NOT NULL DEFAULT '0',
  `onWednesday` tinyint(4) NOT NULL DEFAULT '0',
  `onFriday` tinyint(4) NOT NULL DEFAULT '0',
  `onSaturday` tinyint(4) NOT NULL DEFAULT '0',
  `onSunday` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK_scheduler` (`event_id`),
  CONSTRAINT `events_scheduler` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

These are the corresponding time intervals for each of the events. For recurrent events we will set the isRecurrent field to True.

For this data set

insert into `events` (`id`,`name`) values (1,'@work');
insert into `events` (`id`,`name`) values (2,'This Friday\'s Movie');
insert into `events` (`id`,`name`) values (3,'Fishing on Sunday');
insert into `events` (`id`,`name`) values (4,'Get a Haircut');
insert into `scheduler` (`id`,`event_id`,`isRecurrent`,`startDate`,`endDate`,`startTime`,`endTime`,`onMonday`,`onTuesday`,`onThursday`,`onWednesday`,`onFriday`,`onSaturday`,`onSunday`) values (1,1,1,NULL,NULL,'09:00:00','18:00:00',1,1,1,1,1,0,0);
insert into `scheduler` (`id`,`event_id`,`isRecurrent`,`startDate`,`endDate`,`startTime`,`endTime`,`onMonday`,`onTuesday`,`onThursday`,`onWednesday`,`onFriday`,`onSaturday`,`onSunday`) values (2,2,0,'2010-07-29','2010-07-29','20:00:00','23:00:00',0,0,0,0,0,0,0);
insert into `scheduler` (`id`,`event_id`,`isRecurrent`,`startDate`,`endDate`,`startTime`,`endTime`,`onMonday`,`onTuesday`,`onThursday`,`onWednesday`,`onFriday`,`onSaturday`,`onSunday`) values (3,3,0,'2010-08-01','2010-08-01','04:00:00','14:00:00',0,0,0,0,0,0,0);

this (pretty much contrived) query

SELECT  MAKE_SET(SCHED.onMonday | SCHED.onTuesday * 2 | SCHED.onThursday * 4 | SCHED.onWednesday * 8 | SCHED.onFriday * 16 | SCHED.onSaturday * 32 | SCHED.onSunday * 64,"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun") AS days,
        EVT.name,
        CONCAT(IFNULL(SCHED.startDate,""), " ", IFNULL(SCHED.startTime,"")) AS startTime, CONCAT(IFNULL(SCHED.endDate,""), " ", IFNULL(SCHED.endTime,"")) AS endTime FROM events EVT
INNER JOIN scheduler SCHED ON SCHED.event_id = EVT.id
WHERE SCHED.isRecurrent

UNION ALL

SELECT  "ONE-TIME" AS days, EVT.name, CONCAT(SCHED.startDate, " ", SCHED.startTime) AS startTime, CONCAT(SCHED.endDate, " ", SCHED.endTime) AS endTime FROM events EVT
INNER JOIN scheduler SCHED ON SCHED.event_id = EVT.id
WHERE NOT SCHED.isRecurrent;

lists all the events:

Mon,Tue,Wed,Thu,Fri   @work                  09:00:00             18:00:00
ONE-TIME              This Friday's Movie    2010-07-29 20:00:00  2010-07-29 23:00:00
ONE-TIME              Fishing on Sunday      2010-08-01 04:00:00  2010-08-01 14:00:00

You could declare a FK `event_id` to an `events` table and associate the event with multiple intervals. — the_void, Jul 26 '10 at 13:56
[3] was just to demonstrate that the event is not there on weekends. There is also a possibility that they work on Saturdays and not on Sundays or they work 1/2 day on Saturdays only. @the_void can you provide some examples of multiple intervals along with the events table. — Sam, Jul 26 '10 at 16:45
I've updated my answer with a solution that covers more use-cases. However, note that there are still corner cases and you should fit this to your particular needs. — the_void, Jul 26 '10 at 18:18

How should I go about modeling event duration

1 Answers1