title: Howto design a public transport timetable database?
body: <p>Are there any examples of database design for public transport time-tables ?</p>
  
  <p>Or any OpenSource timetable-engine solution?</p>
  
  <p>Or if not, how to do it oneselfs?</p>
  
  <p>How do I best design the database so that a search can be reasonable fast ?</p>
  
  <p>I'd do it like this:</p>
  
  <p>For testing, I'd take an X-shaped bus network, with 5 stops (edges and vertex)..</p>
  
  <p>Table 1:
  A list of Villages/Locations/Stops and their geographical coordinates, plus UID.</p>
  
  <p>Table 2: A list of Location names, including translations, short-signs, with table 1 as foreign key</p>
  
  <p>Table 3: A list of transport vehicles, such as Bus, Train, Airplane, Ship and their translations.</p>
  
  <p>Table 4: A list of countries</p>
  
  <p>Table 5: A list of inclusive fromdates and todates, for public holidays, and holiday name including translation, with country as foreign key</p>
  
  <p>Table 6: A list of from-to dates for n different seasons including translation, e.g. winter, summer, with country as foreign key</p>
  
  <p>And now comes the tricky part (Timetable, routes, stops):</p>
  
  <p>Table 7:
  Line_UID, vehicle_uid, line_nr, line_name, line_start_location_uid, line_stop_location_uid</p>
  
  <p>Table 8:
  hop_uid, line_uid, stop_uid, stop_sequence_nr, time_uid</p>
  
  <p>Table 9:
  time_nuid, hop_uid, time</p>
  
  <p>I think especially the association between stop and stop times is difficult.
  Also I'm still thinking about how best to associate different seasons to line/routes.
  Does this seem reasonable ?
  Or do you see a problem ?</p>
  
  <p><strong>Edit:</strong>
  No, doesn't work, I need to take into account arrival and departure times.</p>