首先， _ 永远不要 让外部实体指定非唯一的连接列。它们可能（具有授权/认证）可以指示 _唯一
的（例如确定性GUID值）。否则，他们将在某个地方指定一个 自然 键，并且您的数据库会自动分配要加入的行ID。另外，除非您要处理未索引行上的 大量
联接（数十个），否则性能将远不及在其他地方处理它时的麻烦。

因此，从外观上看，您正在存储多家公司的公交车时刻表（类似于google，这是获取公共交通路线的必经之路）。
这是我要如何处理：

• 您将需要一个日历文件。这对于所有业务场景都是有用的，但是在这里将非常有用（请注意：不要在其中添加任何与路由相关的信息）。

• 修改agency表以控制联接键。代理商 无法 指定其ID，只能指定其名称（或一些类似的标识符）。如下所示就足够了：

  agency
  

  =============
  id - identity, incrementing
  name - Externally specified name, unique

• 修改route表以控制联接键。代理只能指定其（可能是非唯一的） 自然 键，因此我们需要用于联接的 代理 键：

  route
  

  ==============
  id - identity, incrementing
  agency_id - fk reference to agency.id
  route_identifier - natural key specified by agency, potentially non-unique.
  - required unique per agency_id, however (or include variation for unique)
  route_variation - some agencies use the same routes for both directions, but they’re still different.
  route_status_id - fk reference to route_status.id (potential attribute, debatable)

请注意，路线表实际上不应列出路线上的 停靠站 -唯一的目的是控制哪个代理商拥有哪个路线。

• 创建一个location或address表。这将使您受益匪浅，因为大多数运输公司倾向于在同一地点放置多条路线：

  location
  

  =============
  id - identity, incrementing
  address - there are multiple ways to represent addresses in a database.
  - if nothing else, seperating the fields should suffice
  lat/long - please store these properly, not as a single column.
  - two floats/doubles will suffice, although there are some dedicated solutions.

• 此时，您有两个选择来处理路线上的停靠点：

  1. 定义一个stop表，并列出所有停靠点。像这样的东西：

         stop
     

     ================
     id - identity, incrementing
     route_id - fk reference to route.id
     location_id - fk reference to location.id
     departure - Timestamp (date and time) when the route leaves the stop.

这当然会很快变得很大，但是使处理假期计划变得容易。

2. 定义一个`schedule`表集和一个`schedule_override`表集：

            schedule
    ===================
    id           - identity, incrementing
    route_id     - fk reference to route.id
    start_date   - date schedule goes into effect.

    schedule_stop
    ===================
    schedule_id  - fk reference to schedule.id
    location_id  - fk reference to location.id
    departure    - Time (time only) when the route leaves the stop 
    dayOfWeek    - equivalent to whatever is in calendar.nameOfDay
                 - This does not have to be an id, so long as they match

    schedule_override
    ===================
    id             - identity, incrementing
    route_id       - fk reference to route.id
    effective_date  - date override is in effect.  Should be listed in the calendar file.
    reason_id      - why there's an override in effect.

    schedule_override_stop
    ===========================
    schedule_override_id  - fk reference to schedule_override.id
    location_id           - fk reference to location.id
    departure             - time (time only) when the route leaves the stop

有了这些信息，我现在可以获得所需的信息：

SELECT
FROM agency as a
JOIN route as b
ON b.agency_id = a.id
AND b.route_identifier = :(whatever 109 equates to)
AND b.route_variation = :(whatever 0 equates to)
JOIN (SELECT COALESCE(d.route_id, j.route_id) as route_id, 
             COALESCE(e.location_id, j.location_id) as location_id,
             COALESCE(TIMESTAMP(c.date, e.departure), 
                      TIMESTAMP(c.date, j.departure)) as departure_timestamp
      FROM calendar as c
      LEFT JOIN (schedule_override as d
                 JOIN schedule_override_stop as e
                 ON e.schedule_override_id = d.id)
      ON d.effective_date = c.date
      LEFT JOIN (SELECT f.route_id, f.start_date
                        g.dayOfWeek, g.departure, g.location_id,
                        (SELECT MIN(h.start_date)
                         FROM schedule as h
                         WHERE h.route_id = f.route_id
                         AND h.start_date > f.start_date) as end_date
                 FROM schedule as f
                 JOIN schedule_stop as g
                 ON g.schedule_id = f.id) as j
      ON j.start_date <= c.date
      AND j.end_date > c.date
      AND j.dayOfWeek = c.dayOfWeek
      WHERE c.date >= :startDate
      AND c.date < :endDate) as k
ON k.route_id = b.id
AND k.departure_timestamp >= :leaveAfter
JOIN location as m
ON m.id = k.location_id
AND m.(location inforation) = :(input location information)
ORDER BY k.departure_timestamp ASC
LIMIT 5

这将列出从指定位置出发的所有出发点的清单，对于给定的路线，在startDate和之间endDate（不包括），在leaveAfter时间戳之后。语句（等效）在DB2上运行。它获取时间表的更改，节假日的替代等。