根据当日和明天（公共汽车旅行）进行多种分类

根据当日和明天（公共汽车旅行）进行多种分类

首先， _ 永远不要 让外部实体指定非唯一的连接列。它们可能（具有授权/认证）可以指示 _唯一
的（例如确定性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

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

SELECtFROM agency as aJOIN route as bON b.agency_id = a.idAND 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 kON k.route_id = b.idAND k.departure_timestamp >= :leaveAfterJOIN location as mON m.id = k.location_idAND m.(location inforation) = :(input location information)ORDER BY k.departure_timestamp ASCLIMIT 5

这将列出从指定位置出发的所有出发点的清单，对于给定的路线，在

startDate

和之间

endDate

（不包括），在

leaveAfter

时间戳之后。语句（等效）在DB2上运行。它获取时间表的更改，节假日的替代等。