我正在设计一个数据库来存储300条不同公交线路的时间表,
每条路线的停靠次数不同,周一,周六和周日的时间也不同.
我代表每条路线的巴士出发时间如下,
我不确定表中是否应该有空值,这看起来不错吗?
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
解决方法:
不,它看起来不行.每个Route / Day / Stop时间值应该在一个单独的行中:
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
等等
理想情况下,您将拥有一个包含Route,Day,Run#的单独表,并将它们分配给唯一的RunID整数值,然后使用该值代替StopTimes表中的Route,Day和Run#列.
通常,如果末尾有索引号的列(Time1,Time2或Phone1,Phone2),则表明您的数据库不是根据规范化原则设计的.
当然,您将数据转换为您建议发布的格式.