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 !!!
Best Answer
IMHO there is no a perfect solution for such scenario but you can utilize
IGNOREclause.For example if you were to have an
auto_incrementid column in your table and you already have a row in it with a known id then your query to import only rows for february might look like thisWhat it does it reads values of sldate field in @slfield variable then in
SETclause it checks whether it's february and if it is then assigns NULL toidcolumn and if not then assigned predefined existing value1violating aUNIQUEconstraint andIGNOREclause does exactly that it skips a row in such a case.That being said I'd recommend another approach: