CODEWITHSUNDEEP
algorithmdatabase-design
user1321553
user1321553

Reputation:

Database design for bus reservation

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.

Upvotes: 12

Views: 18712

Answers (2)

user1360951
user1360951

Reputation:

From the perspective of bus company:

Usually one route is considered as series of sections, like A to B, B to C, C to D, etc. The fill is calculated on each of those sections separately. So if the bus leaves from A full, and people leave at C, then user can buy ticket at C.

We calculate it this way, that each route has ID, and each section belongs to this route ID. Then if user buys ticket for more than one section, then each section is marked. Then for the next passenger system checks if all sections along the way are available.

Upvotes: 10

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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

enter image description here

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

Upvotes: 15

Related Questions