如何对事件持续时间进行建模？

Question

环境: mysql

我应该如何为下列场景建模事件持续时间.

今天是星期一(一周开始)

1. 一天的活动安排在今天上午10点到下午5点之间。
2. 每天从上午10点到11点开放，从今天到1周。(包括weekends)
3. Every日活动，由今日上午十时至上午十一时至两星期开放.)(不包括周末)

Answer 1

我会选择一种简单的方式：

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;

编辑

根据最近的评论，一种也将支持反复发生的事件和更细粒度的时间间隔选择的解决方案是：

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;

此表保存所有事件信息(即名称、.)。

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;

这些是每个事件的对应时间间隔。对于反复发生的事件，我们将isRecurrent字段设置为True。

对于此数据集

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);

这个(几乎是人为的)查询

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;

列出所有事件：

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