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?


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.

1 Answer 1


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)
  • Thanks. I must keep track of all times because the delta value for each line stop is innacurate and the goal of my system is to predict the next arrival time. So the idea is that the users can introduce the time they take the bus with specifc station and line ... then the system can calculate a median (or something like that) to predict the arrival time.
    – user6814
    Jul 23, 2013 at 7:36
  • But if you have a start time and a delta for each stop, you have the same data, you don't need to store the stop time for each route's stop. If you add each delta to the start time, you will get the bus stop time for each stop.
    – druzin
    Jul 24, 2013 at 1:56
  • 1
    Yes that would work if the frequency was the same (9:15, 9:30, 9:45, 10:00, ...), but the fact is that is not. This would be the a real schedule of the example above: 9:15, 9:31, 9:44, 10:02, ... so I need to store this times.
    – user6814
    Jul 24, 2013 at 15:29
  • That's true, if you want to do measurements for analytical purposes, you need to register the times in a separate table. You could achieve this by adding this table : route_actual_times (route_start_time_id, line_stop_id, actual_time, ... )
    – druzin
    Jul 24, 2013 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.