# How to create a train schedule in SQLite3?

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?

-

I think you need a track_segment table to track the segments between stations. The problem with your Station table is that you include distance there, but distance to/from what?

So let's try this:

Train
-----
ID
Train_number
Days_of_operation
other_details

Station
-------
ID
Name
other_data

Track_Segment
-------------
From_Station_ID
To_Station_ID
Length
Line_ID

Line
----
ID
Name
other_data

Train_Run
---------
ID
Train_ID
from_Station_ID
to_station_ID
Depart_datetime
Arrive_datetime
other_details


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 between from_station_id and to_station_id in track_segment. I also included the line 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".

-
Might you also consider adding another parent to Train_Run to account for equipment used? I only think of this to distinguish your Train table as denoting logical routes as opposed to physical trains. +1 for a good design regardless. –  Joel Brown Apr 6 '12 at 2:07
The distance is not necessary. Would that make the problem easier to solve –  Cocoa Dev Apr 10 '12 at 10:49