Please help me with creating my SQLite database. I need to create a sample train schedule but not sure if im on the right track

Go To StackoverFlow.com

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?

2012-04-05 14:48
by Cocoa Dev
No pun intended - Widor 2012-04-05 14:50
I did one in reponse to a question about bus schedules 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 route - Clockwork-Muse 2012-04-05 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 2012-04-05 16:20


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.

2012-04-07 23:42
by scorpdaddy
Ads