I am interested in what a SQLite3 database schema would look like for a train timetable.
Generally this is presented in table format as the final result.
Sample Schedule for M to F
STATION A STATION B STATION C ... STATION K
6:00 6:15 7:30 9:00
6:30 6:45 7:45 9:30
7:00 7:15 ---- 10:00
8:00 8:15 9:30 11:00
Sample Schedule for Weekends
STATION A STATION B STATION C ... STATION K
7:00 7:15 8:30 10:00
8:30 8:45 8:45 10:30
9:00 ---- 9:30 11:00
10:00 10:15 10:30 12:00
The trains don't have any number (like the A-Train, 12 Train, etc). So we can make an internal number. Should the internal number be unique or duplicated for a Monday-Friday to a Saturday and Sunday. Then a train will stop at a number of station, but not all, and sometimes the station needs an arrival (a) and departure (d) time, or one or the other.
Stations can be ordered by their distance.
So far I am thinking I need tables for:
- Stations (ID, name, distance, line)
- Train (ID, train no, Day Operation, details)
- Train run (Train.ID, Station.ID, arr/dep, time, notes)
Is this normalised enough?
Is my Schema correct or so I need to make any changes?
Best Answer
I think you need a
track_segment
table to track the segments between stations. The problem with yourStation
table is that you include distance there, but distance to/from what?So let's try this:
This allows a
Train_run
to reference a specific train, as well as the two endpoints of the journey. Of course, there might be multiple segments of track between these two stations, so the total length of the journey could be had by looking at all the segments betweenfrom_station_id
andto_station_id
intrack_segment
. I also included theline
table, since you had a refernce to it in your tables. I assume that a "Line" is just a descriptive term for a specific train journey that might have multiple stops along the way, like "Orient Express" or "Rocky Mountain Route".