views:

89

answers:

2

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.

+5  A: 

Google has created a format for this data:

http://code.google.com/transit/spec/transit_feed_specification.html

thomasfedb
+1 for a nice reference.
David Thomas
+1, Very, very nice reference.
Quandary
A: 

Here's one place to look for a public model of public transport.

Data Base Answers transport model.

Walter Mitty