MySQL schema for recurring events – Query by specific date

MySQLschema

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:

table image

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.

Best Answer

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 !!!

Related Question