Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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".

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.