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