公交车预订数据库设计 [英] Database design for bus reservation

问题描述

我正在为公共汽车开发一个预留模块，我无法为其设计正确的数据库结构。我们来看看下面的案例：_
巴士从A到D，在B和C有中途停留。乘客可以为任何路线预订机票，即从A到B，C到D，A到D等。

所以每个路由都可以有很多子路由，而更大的路由可以包含较小的路由。

我想以一种有助于轻松搜索免费座位的方式设计路线和停靠点的表格结构。所以如果有人保留从A到B的座位，那么从B到C或D的座位仍然可以使用。

所有的想法都将不胜感激。

解决方案

我可能会用一个类似于这个基本思想的暴力结构：

（真实模型中应该存在更多的领域，这只是一个简化版本，其中包含了建立表之间关系所必需的基本要素。）

票通过TICKET_STOP表覆盖停止，例如，如果票券包含3站，则TICKET_STOP将包含与票相关的3行。如果该票没有其他两个站点，那么那里就没有相关的行，但是没有任何东西可以防止不同的机票覆盖这些站点。

自由使用或自然键/识别关系确保两张票不能覆盖相同的座椅/停止组合。看看LINE.LINE_ID如何移动在菱形依赖关系的两边，只能在底部合并在TICKET_STOP表中。

本身不会保护您免受异常，例如单次跳过一些停机 - 您必须通过应用程序逻辑来执行一些规则。但是，应该允许一个相当简单和快速的确定哪些座位是免费的，哪些部分是这样的：

  SELECT * 
 FROM 
停止交叉加入座位
 WHERE 
 STOP.LINE_ID =：line_id 
和SEAT.BUS_NO =：bus_no 
 AND NOT EXIST （
 SELECT * 
 FROM TICKET_STOP 
 WHERE 
 TICKET_STOP.LINE_ID =：line_id 
 AND TICKET_STOP.BUS_ID =：bus_no 
 AND TICKET_STOP.TRIP_NO =：trip_no 
 AND TICKET_STOP.SEAT_NO = SEAT.SEAT_NO 
 AND TICKET_STOP.STOP_NO = STOP.STOP_NO 
）
  

（将参数前缀：替换为适合您的DBMS。）

此查询基本上生成给定线路和总线的所有停靠点和座位组合，然后丢弃那些在给定行程上的某些机票已被覆盖的线路和总线。

您可以轻松添加： STOP.STOP_NO IN（...）或 SEAT.SEAT_NO IN（...）到 WHERE 子句限制搜索具体停靠站或座位。

I'm developing a reservation module for buses and I have trouble designing the right database structure for it.

Let's take following case:
Buses go from A to D with stopovers at B and C. A Passenger can reserve ticket for any route, ie. from A to B, C to D, A to D, etc.

So each route can have many "subroutes", and bigger contain smaller ones.

I want to design a table structure for routes and stops in a way that would help easily search for free seats. So if someone reserves seat from A to B, then seats from B to C or D would be still be available.

All ideas would be appreciated.

解决方案

I'd probably go with a "brute force" structure similar to this basic idea:

(There are many more fields that should exist in the real model. This is only a simplified version containing the bare essentials necessary to establish relationships between tables.)

The ticket "covers" stops through TICKET_STOP table, For example, if a ticket covers 3 stops, then TICKET_STOP will contain 3 rows related to that ticket. If there are 2 other stops not covered by that ticket, then there will be no related rows there, but there is nothing preventing a different ticket from covering these stops.

Liberal usage or natural keys / identifying relationships ensures two tickets cannot cover the same seat/stop combination. Look at how LINE.LINE_ID "migrates" alongside both edges of the diamond-shaped dependency, only to be merged at its bottom, in the TICKET_STOP table.

This model, by itself, won't protect you from anomalies such as a single ticket "skipping" some stops - you'll have to enforce some rules through the application logic. But, it should allow for a fairly simple and fast determination of which seats are free for which parts of the trip, something like this:

SELECT *
FROM
    STOP CROSS JOIN SEAT
WHERE
    STOP.LINE_ID = :line_id
    AND SEAT.BUS_NO = :bus_no
    AND NOT EXIST (
        SELECT *
        FROM TICKET_STOP
        WHERE
            TICKET_STOP.LINE_ID = :line_id
            AND TICKET_STOP.BUS_ID = :bus_no
            AND TICKET_STOP.TRIP_NO = :trip_no
            AND TICKET_STOP.SEAT_NO = SEAT.SEAT_NO
            AND TICKET_STOP.STOP_NO = STOP.STOP_NO
    )

(Replace the parameter prefix : with what is appropriate for your DBMS.)

This query essentially generates all combinations of stops and seats for given line and bus, then discards those that are already "covered" by some ticket on the given trip. Those combinations that remain "uncovered" are free for that trip.

You can easily add: STOP.STOP_NO IN ( ... ) or SEAT.SEAT_NO IN ( ... ) to the WHERE clause to restrict the search on specific stops or seats.

这篇关于公交车预订数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！