数据库设计的巴士预约 [英] Database design for bus reservation

问题描述

我正在开发巴士预约模块，我有麻烦了设计合适的数据库结构吧。

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

让我们以下面的案例：
从A巴士转到D与中途停留在B和C乘客可以保留门票任何路由，即。从A到B，C到D，A至D等。

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.

我想设计一个表结构和路由的方式，这将有助于更容易地搜索免费席位停止。因此，如果有人将保留座位从A到B，然后席位从B到C或D将仍然可用。

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.

所有的想法将AP preciated。

All ideas would be appreciated.

推荐答案

我可能会用蛮力结构类似这样的基本思路走：

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

（有许多应该存在于真实模型多个字段，这是仅包含必要建立表之间的关系的最基本的简化版本。）的

通过TICKET_STOP表票包括停止，例如，如果一票涵盖了3站，然后TICKET_STOP将包含相关的是的3排的票。如果有2个不属于该票等站，那么就没有相关行出现，但并没有什么preventing一个的不同的的覆盖了从这些车站售票。

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.

自由使用或自然键/确定的关系，确保两票不能覆盖相同的座椅/停止组合。看看如何LINE.LINE_ID迁移菱形依赖性的两个边缘一起，只在其底部被合并，在TICKET_STOP表

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
    )

（更换参数preFIX ：什么是适合你的DBMS）的

(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.

您可以轻松地添加： STOP.STOP_NO IN（...）或 SEAT.SEAT_NO IN（...）到，其中子句限制在特定的停止或座位的搜索。

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屋！