seat reservation system database design

Question

I am designing a database for seat reservation for buses. My problem is how can I design a database which maps the seats of the buses?

For example, from route SRC to DEST there are 10 buses and every bus has 50 seats. Every bus has unique bus number. How can I design database that can map 50 seats to every bus and also show that the seat are already reserved or not? Passengers can select any bus and any seat number according to their comfortable.

One way is to create table with columns bus_id (int), seat_id (int), status (bool). But with this approach, every bus_id will have rows equal to number of total seats availabe in the bus. Please suggest me on this problem.

Answer 1

It helps to express the business domain in semi-formal language.

I think you're saying something like:

The system has many locations.

The system has many routes.

A route combines 2..n destinations.

A route has 1..n schedules.

A schedule has 1..n departures.

A departure has 1 bus.

A bus has 50 seats.

The system has 0..n passengers.

A passenger has 0..n reservations.

A reservation has 1 departure and 1 seat.

This would give you a schema along the lines of:

Destination
---------
Destination_id

Route
-------
Route_id

Route_destination
-------------
Route_destination_id
Route_id
from_destination
to_destination
sequence

Departure
--------
Departure_id
Route_destination_id
Departure_time
Arrival_time

Bus
---
Bus_id
(seats if this could vary per bus)

Customer
------
Customer_id

Reservation
---------
Reservation_id
Departure_id
Customer_id
Date
Seat_no

If you need to store additional data about the seats (i.e. they aren't just a sequence from 1 to 50, but you need to store location, or whether they're accessible for disabled people, or whether it's a window or isle seat) you need to introduce an additional "bus_seat" table similar to @sqlzim's suggestion.

Answer 2

This is just an example, and its simplicity tries to match the simplicity presented in the question. I am sure there would be complications depending on actual implementation details.

If there are often only a few reservations, you could choose to only have rows when there is a reservation like so:

create table bus_seats (
    bus_id int
  , seat_id int
  /*
  , additional_columns
  , handicap reservation only
  , which row
  , which side
  , is a window seat
  , seat reclines
  , extra_wide
  , distance from the restroom for calculating diffusion of odors over time
  , etc
  */
  );
create table bus_seat_reservations (
    reservation_id int
  , bus_id int
  , seat_id int
  , route_id int
  , passenger_id int
  );

See all bus seats, and which are reserved by route:

select 
    bs.bus_id
  , bs.seat_id
  , r.route_id
  , bsr.passenger_id as reserved_by
from bus_seats bs
  inner join routes r
    on bs.bus_id = r.bus_id
  left join bus_seat_reservations bsr
    on bsr.bus_id   = bs.bus_id
   and bsr.seat_id  = bs.seat_id
   and bsr.route_id =  r.route_id

See reserved seats:

select 
    bsr.bus_id
  , bsr.seat_id
  , bsr.route_id
  , passenger_id
from bus_seat_reservations bsr

See available seats using left join:

select bs.bus_id
  , bs.seat_id
  , r.route_id
  , bsr.passenger_id as reserved_by
from bus_seats bs
  inner join routes r
    on bs.bus_id = r.bus_id
  left join bus_seat_reservations bsr
    on bsr.bus_id   = bs.bus_id
   and bsr.seat_id  = bs.seat_id
   and bsr.route_id =  r.route_id
where bsr.reservation_id is null

See available seats using not exists():

select bs.bus_id
  , bs.seat_id
  , r.route_id
from bus_seats bs
  inner join routes r
    on bs.bus_id = r.bus_id
where not exists (
  select 1
  from bus_seat_reservations bsr
  where bsr.bus_id   = bs.bus_id
    and bsr.seat_id  = bs.seat_id
    and bsr.route_id =  r.route_id
    )