ansaurus

Question

How should I go about modeling event duration

Answer 1

+1 A:

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

the_void 2010-07-26 12:59:55

How would this handle the different time windows?

APC 2010-07-26 13:52:29

You could declare a FK `event_id` to an `events` table and associate the event with multiple intervals.

the_void 2010-07-26 13:56:51

[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.

Samuel 2010-07-26 16:45:35

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 2010-07-26 18:18:28

ansaurus

tags:

views:

answers:

How should I go about modeling event duration

related questions