mysql - SQL 表设计 - 存储多段行程

Question

我想创建一个数据库来存储行程的路段，其中每条路段在另一个表中都有一个 FK，行程标识符将是键/唯一。

例如“‘东海岸公路旅行’:波士顿 -> 纽约市，纽约市 -> 费城，费城 -> 巴尔的摩，巴尔的摩 -> 华盛顿特区，华盛顿特区 -> 罗利”

稍后，我想要运行查询，例如，

"Which trips contain the NYC -> Philly and Philly -> Baltimore legs?"

对于如何有效地存储此类旅行信息，我有点困惑。行程标识符键和将行程行程存储为纯文本列可能不是最有效的解决方案。

如果有任何有关如何处理此问题的提示，我们将不胜感激。

Answer 1

假设您的行程不是“一次性”，而是在预定线路上进行(并且一条线路可以生成多次行程)，那么您将需要这样的东西:

(如果它们是一次性的，想象一下 LINE 就是旅行。)

注意LEG的结构:

• 它的 PK 包含 LEG_NO，但不包含 STOP_ID:LEG_NO 决定了给定行中腿的顺序，并且如果需要(例如，在往返行程中)，还允许多个腿穿过同一个停靠点。
• 此外，腿中只有“开始”(而不是“结束”)停靠点 - 无论“前一个”(由 LEG_NO 定义)腿是什么决定下一个腿的起始停靠点。这样，您就永远不会有断开的腿(即前一腿的终点站与下一腿的起点站不匹配)。

TRIP 的 PK 包含 TRIP_NO 而不是(例如)START_DATE_TIME，以允许多个行程同时在同一条线上开始，如果您需要的话。

您的示例“东海岸公路旅行”线上的第 23 次旅行可以这样表示:

TRIP: LINE_ID  TRIP_NO
      -------  -------
          100       23

LINE: LINE_ID  LINE_NAME
      -------  ---------
          100  'East coast roadtrip'

LEG:  LINE_ID  LEG_NO  STOP_ID
      -------  ------  -------
          100       1       55
          100       2       11
          100       3       66
          100       4       22
          100       5       44
          100       6       33

STOP: STOP_ID  STOP_NAME
      -------  ---------
           22  'Baltimore'
           11  'NYC'
           33  'Raleigh'
           66  'Philly'
           55  'Boston'
           44  'DC'

(注意:我故意使用非序列号来更清楚地充实连接。)

---

使用此数据库结构，您可以轻松获取经过所有给定站点的行程，例如:

SELECT *
FROM TRIP
WHERE
    LINE_ID IN (
        SELECT LINE_ID
        FROM LEG JOIN STOP ON LEG.STOP_ID = STOP.STOP_ID
        WHERE STOP_NAME IN ('NYC', 'Philly', 'Baltimore')
        GROUP BY LINE_ID
        HAVING COUNT(DISTINCT STOP_ID) = 3
    )

(注意:在旧版本的 MySQL 上，由于查询优化器在 IN 方面存在问题，您希望将此查询重写为 JOIN。)

但是，如果您想要按此顺序经过这些站点的行程并且中间没有“间隙”，大腿很快就会变得毛茸茸。最好的办法可能是获取上述子查询的结果并在客户端对其进行分析，而不是尝试建立顺序并检测 SQL 中的差距(这基本上是基于集合的)。