Bus Timetable database design
- by paddydub
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