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
time_back indicates that there is no backward route that passes by station_id=20).
My questions are:
- 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?
- 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
timestable 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.