Bus Timetable database design

Question

hi,

I'm trying to design a db to store the timetable for 300 different bus routes, Each route has a different number of stops and different times for Monday-Friday, Saturday and Sunday. I've represented the bus departure times for each route as follows, I'm not sure if i should have null values in the table, does this look ok?

route,Num,Day,    t1,   t2,   t3,    t4    t5      t6      t7      t8     t9    t10 

117, 1, Monday,   9:00, 9:30, 10:50, 12:00, 14:00  18:00   19:00   null   null   null
117, 2, Monday,   9:03, 9:33, 10:53, 12:03, 14:03  18:03   19:03   null   null   null
117, 3, Monday,   9:06, 9:36, 10:56, 12:06, 14:06  18:06   19:06   null   null   null
117, 4, Monday,   9:09, 9:39, 10:59, 12:09, 14:09  18:09   19:09   null   null   null
.
.
.
117, 20, Monday,   9:39, 10.09, 11:39, 12:39, 14:39  18:39   19:39   null   null   null
119, 1, Monday,   9:00, 9:30, 10:50, 12:00, 14:00  18:00   19:00   20:00  21:00  22:00
119, 2, Monday,   9:03, 9:33, 10:53, 12:03, 14:03  18:03   19:03   20:03  21:03  22:03
119, 3, Monday,   9:06, 9:36, 10:56, 12:06, 14:06  18:06   19:06   20:06  21:06  22:06
119, 4, Monday,   9:09, 9:39, 10:59, 12:09, 14:09  18:09   19:09   20:09  21:09  22:09
.
.
.
119, 37, Monday,   9:49, 9:59, 11:59, 12:59, 14:59  18:59   19:59   20:59  21:59  22:59
139, 1, Sunday,   9:00, 9:30,  20:00  21:00  22:00  null   null     null    null   null  
139, 2, Sunday,   9:03, 9:33,  20:03  21:03  22:03  null   null     null    null   null  
139, 3, Sunday,   9:06, 9:36,  20:06  21:06  22:06  null   null     null    null   null  
139, 4, Sunday,   9:09, 9:39,  20:09  21:09  22:09  null   null     null    null   null  
.
.
.
139, 20, Sunday,   9:49, 9:59, 20:59  21:59  22:59  null   null     null    null   null  

Answer 1

No, it does not look okay. Each Route/Day/Stop time value should be in a separate row:

 Route, Day, Run #, Stop #, Time

 117, Monday, 1, 1, 9:00
 117, Monday, 1, 2, 9:30
 117, Monday, 1, 3, 10:50
 117, Monday, 1, 4, 12:00
 . . . Finish Run #1 . . .
 117, Monday, 2, 1, 9:03
 117, Monday, 2, 2, 9:33
 117, Monday, 2, 3, 10:53
 117, Monday, 2, 4, 12:03

etc.

Ideally, you'll have a separate table with Route, Day, Run # in it and assign them to a unique RunID integer value, then use that value in place of the Route, Day, and Run # columns in the StopTimes table.

In general, when you have columns with index numbers at the end (Time1, Time2 or Phone1, Phone2) it's a hint your database is not designed according to the principles of normalization.

Of course, you'll convert the data into the format you suggested for publication.

Answer 2

The basics of your system are the bus (or vehicle), the stops (scheduled), and the route similar to the following:

Bus

bus_id
vehicle_type?
handicap_accessible?
etc...

Stop

stop_id
location
other_stop_attributes?

Route

route_id
stop_id
stop_order

I would think you would list all the stops in the route table in the order you want them to occur. having this seprated allows you to do other route optimization in the future if you want or need to.

You may desire to have a large matrix of Stop to stop distances and times which would give you flexibility in designing new routes. And lastly, you will want a schedule to tell which bus will be on which route on which day. all similar to the following examples:

Stop_Stop

stop_1_id
stop_2_id
distance
time

Schedule

day_of_week
bus_id
route_id
start_time

Answer 3

I had to solve this problem and I used this :

Line - number - name

Station - name - latitude - longitude - is_terminal

Holiday - date - description

Route - line_id - from_terminal : station_id - to_terminal : station_id

Route schedule - route_id - is_holiday_schedule - starting_at

Route stop - route_id - station_id - enlapsed_time_from_start : in minutes

Does it looks good for you ?