0

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?

Cocoa Dev
  • 9,361
  • 31
  • 109
  • 177
  • 6
    No pun intended? – Widor Apr 05 '12 at 14:50
  • 1
    I did one in reponse to a [question about bus schedules](http://stackoverflow.com/a/8247982/812837) a while back, which should be adaptable. Although I think you may want to update your table names - you're not tracking trains, you're tracking routes – Clockwork-Muse Apr 05 '12 at 15:51
  • Hello X-Zero, your post was very detailed and complicated. I am relatively new at SQL and your post had me so confused. – Cocoa Dev Apr 05 '12 at 16:20

1 Answers1

0

You might want to have a separate table of routes, and another that links trains to routes. You could have partial routes and link one train to multiple partial routes.

scorpdaddy
  • 303
  • 3
  • 14