我正在使用SQL设计公交车时刻表。每条巴士路线都有多个站点，每条路线是否需要不同的表格？ [英] I am designing a bus timetable using SQL. Each bus route has multiple stops, do I need a different table for each route?
问题描述
我正在尝试提出最高效的数据库。我的巴士路线全都有大约10个站。公交车从第一路开始直到到达第十站，然后再次返回。这个周期每天发生3次。
I am trying to come up with the most efficient database as possible. My bus routes all have about 10 stops. The bus starts at number one until it reaches the 10th stop, then it comes back again. This cycle happens 3 times a day.
我对于如何有效地生成公交车时间以及在哪里存储停靠站感到非常困惑。如果将所有停靠点放在一个字段中，而将时间停放在另一个字段中，则数据库将不会非常动态。
I am really stuck as to how I can efficiently generate the times for the buses and where I should store the stops. If I put all the stops in one field and the times in another, the database won't be very dynamic.
如果我将所有停靠点一个接一个地存储在一个字段中列，然后是另一列中的时间，由于一站有多次，因此将有很多重复发生。
If I store all the stops one by one in a column and then the times in another column, there will be a lot of repeating happening further down as one stop has multiple times.
也许我错过了一些东西，我已经才刚刚开始学习SQL，这是我们已经设置的任务。
Maybe I am missing something, I've only just started learning SQL and this is a task we have been set.
预先感谢。
推荐答案
您将需要一个包含您的时间表的表：
You will need one table that contains your Timetable:
- 路线ID
- 停车ID
- 时间
- 可能还需要其他字段（方向，序列号，块号等）
- Route ID
- Stop ID
- Time
- Possibly other fields as needed (direction, sequence #'s, Block #, etc)
我建议创建单独的表公交车站（用于存储站点名称，纬度/经度等）和路线（用于存储路径名称，第一站，最后一站，路线等）。
I would recommend creating separate tables Bus Stop (to store stop names, lat/longs, etc) and Route (to store route name, first stop, last stop, direction, etc).
您可能已经意识到这一点，但是公交车的安排很快就会变得很复杂。例如：
You are probably aware of this already, but bus scheduling can get complicated very quickly. For example:
-
您可能需要将某些停靠点指定为时间点，这些时间点会显示在打印的时间表中
You may need to designate certain stops as "Time Points" which show up in the printed schedules
每个路线可能会有多种变化。例如，某些版本可能在不同的巴士站开始或结束
Each route may have multiple variations. For example, some versions may start or end at a different bus stop
时间表可能在周六和周日有所不同，大多数代理商每季度都会更改时间表
The schedule will probably be different on Saturday and Sunday, and most agencies change their schedules quarterly
您可能需要考虑其中一些情况，并将其构建到您的模式中。
You may need to consider some of these cases, and build them into your schema.
有帮助吗？
这篇关于我正在使用SQL设计公交车时刻表。每条巴士路线都有多个站点，每条路线是否需要不同的表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
