MySQL schema for recurring events - Query by specific date

Question

I've just asked this exact same question on stackoverflow so apologies for cross posting but I only just noticed dba so thought I'd ask across here as well.

I'm just about to start putting together a schema for a system that will have recurring events and keep going through different options. None of which seem ideal.

The requirements are that I need to store the events and the pattern by which they occur I also need to be able to query this data with a specific date to determine if the date/time specified is covered by any of these events.

An example that demonstrates the functionality I am after works along the lines of a train time lookup system.

I'd like to be able to insert the time at which a train departs for a specific journey. Say 10am every day Monday - friday.

Then I would like to be able to query this data set to see if any trains are leaving at 10am (with 15 minutes leeway either side so 9:45 - 10:15) on Wednesday 30th April 2014.

I keep going backwards and forwards on this without making any real progress so any ideas would be much appreciated.

Edit :

My initial thoughts on the table are as so:

The issue with this is that it is very static in it only allows for 24 hour granularity for the frequency of the trips. Ideally a system that allowed me to enter a start date and altering periods between trips would be the optimal solution. So for example, once a month, once a fortnight etc.

Answer 1

Let's say you made the table like this

CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    station_arr_id INT NOT NULL,
    station_dep_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY station_arr_index (station_arr_id,arr_dt),
    KEY station_dep_index (station_dep_id,dep_dt)
);

Next, you insert into the table the event

• Arriving From Station 21 on April 30, 2014
• Arrived at Station 29 at 10:00 AM on April 30, 2014
• Departing From Station 29 1 minute later

Here is that INSERT query

INSERT INTO TrainEvents (station_arr_id,arr_dt,station_dep_id,dep_dt)
VALUES (21,'2014-04-30 10:00:00',29,'2014-04-30 10:01:00');

OK, now let's see how you can query the following departure

• All Departures 10:00 AM on April 30, 2014 from Station 29
• Check Departures 15 minutes before and after

Here is that SELECT query

SET @MinuteWindow = 15;
SET @DepartureDT = '2014-04-30 10:00:00';
SET @DepartureDTMinBef = @DepartureDT - INTERVAL @MinuteWindow MINUTE;
SET @DepartureDTMinAft = @DepartureDT + INTERVAL @MinuteWindow MINUTE;
SELECT * FROM TrainEvents
WHERE station_dep_id = 29
AND dep_dt >= @DepartureDTMinBef;
AND dep_dt <= @DepartureDTMinAft;

EPILOGUE

Given your initial idea, you could change the station_arr_id and station_dep_id into a single route_id and store the routes in a route table

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY arr_index (route_id,arr_dt),
    KEY dep_index (route_id,dep_dt)
);

You could also make the event record arrivals and departures separately.

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    event_dt DATETIME NOT NULL,
    event_type TINYINT NOT NULL # 1 for arrival, 2 for departure

    ...
    PRIMARY KEY (id),
    KEY event_index1 (route_id,event_dt,event_type),
    KEY event_index2 (route_id,event_type,event_dt),
);

I am just giving ideas. I'll leave it to you to implement. Perhaps, to give you some UI ideas, see the site I use to commute : http://as0.mta.info/mnr/schedules/sched_form.cfm . The result of the route you pick in that site shows route departures within a 5-hour window (2.5 hours before and 2.5 hours after).

Give it a Try !!!

Answer 2

What you seek is a table like the one below,

from which you could query:

SELECT *
FROM departures
WHERE route_id = 'SOME_ROUTE' -- This is the code to a route, encompassing from/where
  AND departure_date     =  @some_day
  AND departure_time_min <= @target_time
  AND departure_time_max >= @target_time

I present a solution where such table is precomputed from other simpler, more manageable tables.

On route_id

I understood that route_id id a FK to a row defining the route as a unique departure to destination. The way back shall be defined by another row in that "route" table.

Simple daily departure schedules

Daily departures - Scheduling data

These might be scheduled in a table such:

CREATE TABLE departures_daily (
  departure_id INT NOT NULL AUTO_INCREMENT,
  --
  route_id CHAR(36) NOT NULL,
  --
  operation_began DATE NOT NULL,
  operation_ends  DATE NOT NULL DEFAULT '1900/01/01',
  --
  departure_time TIME NOT NULL,
  --
  sun BIT(1) NOT NULL,
  mon BIT(1) NOT NULL, tue BIT(1) NOT NULL, wed BIT(1) NOT NULL,
  thu BIT(1) NOT NULL, fri BIT(1) NOT NULL, sat BIT(1) NOT NULL,
  --
  CONSTRAINT pk_departures_daily PRIMARY KEY(departure_id)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

Where:

• operation_began and operation_ends constraints this information to a specific time-frame
• operation_ends is set as default to Jan, 1st 1900 by design, as shown below
• sun to sat marks operating days

If operation_ends is not defined, it's set to the last day in the year of operation_began:

DELIMITER //
CREATE TRIGGER tr_departures_daily_before_insert BEFORE INSERT ON departures_daily FOR EACH ROW
BEGIN
  IF NEW.operation_ends = '1900/01/01' THEN
    SET NEW.operation_ends = SUBDATE(MAKEDATE(YEAR(NEW.operation_began) + 1, 1), 1);
  END IF;
END//
DELIMITER ;

Daily departures - Queriable data

The goal is to make this settings be reflected to a queriable table defined by:

CREATE TABLE departures (
  route_id CHAR(36) NOT NULL,
  --
  departure_date     DATE NOT NULL,
  departure_time_min TIME NOT NULL,
  departure_time_max TIME NOT NULL,
  --
  departure_source VARCHAR(23) NOT NULL,
  departure_id     INT NOT NULL,
  --
  INDEX ak_departures(
    route_id ASC, departure_date ASC, departure_time_min ASC, departure_time_max ASC
  ),
  --
  INDEX ix_departures_source(
    departure_source ASC, departure_id ASC, departure_date ASC
  )
)  COLLATE='utf8_general_ci' ENGINE=InnoDB;

Where:

• departure_source identifies the table from which the information was retrieved
• departure_id identifies the row at that table
• route_id, departure_date, departure_time_min and departure_time_max provide the query parameters

Daily departures - Transformation code

Given a departure_id from departures_daily, the code below transforms the data and fills in departures.

DELIMITER //
CREATE PROCEDURE sp_departures_daily_process(id INT)
BEGIN
  -- Retrieves the scheduling parameters ---------------------------------------
  -- ---------------------------------------------------------------------------

  SELECT
    route_id,
    --
    sun *  1 + mon *  2 + tue *  4 + wed * 8 +
    thu * 16 + fri * 32 + sat * 64,
    --
    operation_began,
    operation_ends,
    --
    SUBTIME(departure_time, '00:15:00'),
    ADDTIME(departure_time, '00:15:00')
  INTO
    @route_id,
    @dw_hash,
    @operation_began, @operation_ends,
    @departure_time_min, @departure_time_max
  FROM departures_daily
  WHERE departure_id = id;

  -- Defines temporary space for pre-computed data -----------------------------
  -- ---------------------------------------------------------------------------

  DROP TEMPORARY TABLE IF EXISTS _departures;

  CREATE TEMPORARY TABLE _departures(
    departure_date     DATE NOT NULL,
    departure_time_min TIME NOT NULL,
    departure_time_max TIME NOT NULL
  );

  -- Iterates through the time-frame constraint --------------------------------
  -- Inserts into the temporary space the dates that apply ---------------------
  -- ---------------------------------------------------------------------------

  SET @departure_date = @operation_began;

  WHILE @departure_date <= @operation_ends DO
    IF (POWER(2, WEEKDAY(@departure_date) - 1) & @dw_hash <> 0) THEN
      INSERT INTO _departures
      VALUES(@departure_date, @departure_time_min, @departure_time_max);
    END IF;

    SET @departure_date = ADDDATE(@departure_date, INTERVAL 1 DAY);
  END WHILE;

  -- Updates departures table --------------------------------------------------
  -- ---------------------------------------------------------------------------

  -- Inserts new data ------------------  

  INSERT INTO departures(
    route_id,
    departure_date, departure_time_min, departure_time_max,
    departure_source, departure_id
  )
  SELECT
    @route_id,
    departure_date, departure_time_min, departure_time_max,
    'DAILY', id
  FROM _departures
  WHERE departure_date NOT IN(
          SELECT departure_date
          FROM departures
          WHERE departure_source = 'DAILY'
            AND departure_id     = id
        );

  -- Updates changed data --------------

  UPDATE
    departures dep
      INNER JOIN _departures _dep
      ON _dep.departure_date   =  dep.departure_date
  SET dep.departure_time_min = _dep.departure_time_min,
      dep.departure_time_max = _dep.departure_time_max
  WHERE dep.departure_source   =  'DAILY'
    AND dep.departure_id       =  id
    AND dep.departure_time_min <> _dep.departure_time_min;

  -- Deletes obsolete data -------------

  DELETE FROM departures
  WHERE departure_source = 'DAILY'
    AND departure_id     = id
    AND departure_date NOT IN(
          SELECT departure_date
          FROM _departures
        ); 

  -- Clean-up ------------------------------------------------------------------
  -- ---------------------------------------------------------------------------

  DROP TEMPORARY TABLE _departures; 
END//
DELIMITER ;

Daily departures - Triggers

The transformation code, of-course, is called by triggering inserts and updates.

DELIMITER //
CREATE TRIGGER tr_departures_daily_before_insert BEFORE INSERT ON departures_daily FOR EACH ROW
BEGIN
  IF NEW.operation_ends = '1900/01/01' THEN
    SET NEW.operation_ends = SUBDATE(MAKEDATE(YEAR(NEW.operation_began) + 1, 1), 1);
  END IF;
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_departures_daily_after_insert AFTER INSERT ON departures_daily FOR EACH ROW
BEGIN
  CALL sp_departures_daily_process(NEW.departure_id);
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_departures_daily_after_update AFTER UPDATE ON departures_daily FOR EACH ROW
BEGIN
  CALL sp_departures_daily_process(NEW.departure_id);
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_departures_daily_after_delete AFTER DELETE ON departures_daily FOR EACH ROW
BEGIN
  DELETE FROM departures
  WHERE departure_source = 'DAILY'
    AND departure_id     = OLD.departure_id;
END//
DELIMITER ;

Daily departures - Result

Given the insert (sundays, wednesdays and saturdays):

INSERT INTO departures_daily(
  route_id, operation_began, departure_time,
  mon, tue, wed, thu, fri, sat, sun
)
values(
  'GENOVA_ROMA', '2014/01/01', '08:00',
  1, 0, 0, 1, 0, 0, 1
);

One might query:

SELECT *
FROM departures
WHERE route_id       = 'GENOVA_ROMA'
  AND departure_date = '20140419';

Fortnight departures

But, you asked for a broader, extensible, solution, such for fortnight schedules.

Fortnight departures - Scheduling data

The scheduling parameters might be stored in a table such:

CREATE TABLE departures_fortnight (
  departure_id INT NOT NULL AUTO_INCREMENT,
  --
  route_id CHAR(36) NOT NULL,
  --
  operation_began DATE NOT NULL,
  operation_ends  DATE NOT NULL DEFAULT '1900/01/01', 
  --
  departure_time TIME NOT NULL,
  --
  on_first_day  BIT(1) NOT NULL,
  on_last_day   BIT(1) NOT NULL,
  --
  CONSTRAINT pk_departures_daily PRIMARY KEY(departure_id)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

Which differs from departures_daily solely on on_first_day and on_last_day columns.

operation_ends has the same treatment of its counterparts.

DELIMITER //
CREATE TRIGGER tr_departures_fortnight_before_insert BEFORE INSERT ON departures_fortnight FOR EACH ROW
BEGIN
  IF NEW.operation_ends = '1900/01/01' THEN
    SET NEW.operation_ends = SUBDATE(MAKEDATE(YEAR(NEW.operation_began) + 1, 1), 1);
  END IF;
END//
DELIMITER ;

Fortnight departures - Queriable data

Here, the departures table fits as-is, and as planned.

Fortnight departures - Transformation code

The transformation code is a bit more complicated than that for daily schedules.

Maybe due to my poor skills on MySql.

Please, note the change in departures.departure_source.

DELIMITER //
CREATE PROCEDURE sp_departures_fortnight_process(id INT)
BEGIN
  -- Retrieves the scheduling parameters ---------------------------------------
  -- ---------------------------------------------------------------------------

  SELECT
    route_id,
    --
    on_first_day, on_last_day,
    --
    operation_began, operation_ends,
    --
    SUBTIME(departure_time, '00:15:00'), ADDTIME(departure_time, '00:15:00')
  INTO
    @route_id,
    @on_first_day, @on_last_day,
    @operation_began, @operation_ends,
    @departure_time_min, @departure_time_max
  FROM departures_fortnight
  WHERE departure_id = id;

  -- Defines temporary space for pre-computed data -----------------------------
  -- ---------------------------------------------------------------------------

  DROP TEMPORARY TABLE IF EXISTS _departures;

  CREATE TEMPORARY TABLE _departures(
    departure_date     DATE NOT NULL,
    departure_time_min TIME NOT NULL,
    departure_time_max TIME NOT NULL
  );

  -- Iterates through the time-frame constraint --------------------------------
  -- Inserts into the temporary space the dates that apply ---------------------
  -- ---------------------------------------------------------------------------

  IF DAY(@operation_began) < 15 THEN
    SET @departure_date    = STR_TO_DATE(CONCAT(DATE_FORMAT(@operation_began, '%Y%b'), '01'), '%Y%b%d');
    SET @add_to_next_month = 0;
  ELSE
    SET @departure_date    = STR_TO_DATE(CONCAT(DATE_FORMAT(@operation_began, '%Y%b'), '15'), '%Y%b%d');
    SET @add_to_next_month = 1;
  END IF;

  WHILE @departure_date <= @operation_ends DO
    IF @on_first_day = 1 THEN
      INSERT INTO _departures
      VALUES(@departure_date, @departure_time_min, @departure_time_max);
    END IF;

    IF @on_last_day = 1 THEN
      IF @add_to_next_month = 0 THEN
        INSERT INTO _departures
        VALUES(STR_TO_DATE(CONCAT(DATE_FORMAT(@departure_date, '%Y%b'), '14'), '%Y%b%d'), @departure_time_min, @departure_time_max);
      ELSE
        INSERT INTO _departures
        VALUES(LAST_DAY(@departure_date), @departure_time_min, @departure_time_max);
      END IF;
    END IF;

    IF @add_to_next_month = 0 THEN
      SET @departure_date    = STR_TO_DATE(CONCAT(DATE_FORMAT(@departure_date, '%Y%b'), '15'), '%Y%b%d');
      SET @add_to_next_month = 1;
    ELSE
      SET @departure_date    = ADDDATE(LAST_DAY(@departure_date), INTERVAL 1 DAY);
      SET @add_to_next_month = 0;
    END IF;
  END WHILE;

  -- Updates departures table --------------------------------------------------
  -- ---------------------------------------------------------------------------

  INSERT INTO departures(
    route_id,
    departure_date, departure_time_min, departure_time_max,
    departure_source, departure_id
  )
  SELECT
    @route_id,
    departure_date, departure_time_min, departure_time_max,
    'FORTNIGHT', id
  FROM _departures
  WHERE departure_date NOT IN(
          SELECT departure_date
          FROM departures
          WHERE departure_source = 'FORTNIGHT'
            AND departure_id     = id
        );

  UPDATE
    departures dep
      INNER JOIN _departures _dep
      ON _dep.departure_date =  dep.departure_date
  SET dep.departure_time_min = _dep.departure_time_min,
      dep.departure_time_max = _dep.departure_time_max
  WHERE dep.departure_source   =  'FORTNIGHT'
    AND dep.departure_id       =  id
    AND dep.departure_time_min <> _dep.departure_time_min;

  DELETE FROM departures
  WHERE departure_source = 'FORTNIGHT'
    AND departure_id     = id
    AND departure_date NOT IN(
          SELECT departure_date
          FROM _departures
        ); 

  -- Clean-up ------------------------------------------------------------------
  -- ---------------------------------------------------------------------------

  DROP TEMPORARY TABLE _departures; 
END//
DELIMITER ;

Fortnight departures - Triggers

These are pretty much the same.

DELIMITER //
CREATE TRIGGER tr_departures_fortnight_before_insert BEFORE INSERT ON departures_fortnight FOR EACH ROW
BEGIN
  IF NEW.operation_ends = '1900/01/01' THEN
    SET NEW.operation_ends = SUBDATE(MAKEDATE(YEAR(NEW.operation_began) + 1, 1), 1);
  END IF;
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_departures_fortnight_after_insert AFTER INSERT ON departures_fortnight FOR EACH ROW
BEGIN
  CALL sp_departures_fortnight_process(NEW.departure_id);
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_departures_fortnight_after_update AFTER UPDATE ON departures_fortnight FOR EACH ROW
BEGIN
  CALL sp_departures_fortnight_process(NEW.departure_id);
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_departures_fortnight_after_delete AFTER DELETE ON departures_daily FOR EACH ROW
BEGIN
  DELETE FROM departures
  WHERE departure_source = 'FORTNIGHT'
    AND departure_id     = OLD.departure_id;
END//
DELIMITER ;

Conclusions

Is it worth the bounty?

Answer 3

You should store all the events(*) in a dedicated table A with a date column for time of the train departure (to re use your example). This will allow you to deal with exceptions when (to reuse your example) a train departure is cancelled even if it was initially supposed to leave the station this day.

Quering the table will then consist in using 2 additional computed columns for the start date and end date corresponding to the leeway you want to take into account.

These two columns could be directly inserted and stored in the table A or appear in a SQL view based on the table A.

(*) two comments: - as many database schema, you should compute and use a calendar table (where all dates for a range of year are stored) to ease the inserting of records in the table A.

• from a database capacity point of view, storing all the events should not be normally a problem. You will have 365 x 'number of events a day' records.