Bus time schedule database design

database-design

Hi I'm trying to figure out how to design a database for bus time schedule.

There are several lines, each line have two routes (forward and back), each route have several stations (stops) and each station may belong to several routes.

The main goal of the database is to easily select the next arrival time for a specific bus station with a specific line.

I googled and came up with next database design:

lines (id, name, …)

routes (id, name, line_id, …)

stations (id, location)

route_station (id, route_id, station_id)

times (id, station_id, line_id, time_forward, time_back)

So, for instance

times Table
id  station_id   line_id   time_forward  time_back    
1       10          100         9:15         9:18
2       10          100         9:30         9:33
3       10          100         9:45         9:48
4       10          100         10:00        10:03
5       10          100         10:15        10:18
6       10          100         10:30        10:33
7       10          100         10:45        10:48

8       20          100         9:20         null
9       20          100         9:35         null
10      20          100         9:50         null
11      20          100         10:05        null
...
45      40          200         9:55         null
46      40          200         9:09         null
...

In the first 7 rows, the line with line_id=100 stops at different times in the station with station_id=10 (The null in time_back indicates that there is no backward route that passes by station_id=20).

My questions are:

  1. I'm not sure if this design is right at all. I'm no looking for performance. I just want to know if I will have problems with this design in future time (For example: is easy to insert a new line station? Is easy to select the next arrival time for a bus station with specific line station?
  2. How I deal with different time frequencies? Because is different for weekdays and weekends. If my design is ok, then the example above will work fine with arrival times in weekdays but no for weekends. I need a second table or add a column in times table indicating the type of the day?

Thanks.

P.S. I've taken a look at this design, but I think is too much for my system.

P.S.2 I've read about using NoSQL databases may fits better for this kind of systems; but my knowledge is limited to relational databases.

Best Answer

Since you really only have to keep track of times between stations on each route, you only need to keep the start time of each route, the rest can be calculated easily by storing the time delta value for each line stop (the time between the current station and the last station ), instead of keeping time data for each route_stop. You also need to maintain the order of the stops on the line, and if it's a circular route you simply put the stops twice into the chain with different ordering numbers ( so each stop on a circular route is inserted twice into the route_station relation table with different order number ).

You can of course keep the time for each stop if you want, but that seems redundant and makes it harder adding stops to a route, since you'd then have to recalculate all bus stop times, instead of simply adding the new stop and updating the delta time of the next stop.

I'd probably start with a data model like this ( but of course this needs to be expanded if you want to add information about the buses and drivers etc ) :

lines (id, name, ...)

routes (id, name, line_id, ...)

stops (id, location)

line_stops (id, line_id, stop_id, order, time_delta)

route_start_times (id, route_id, start_time)
Related Question