Transport Booking System like MakeMyTrip

What We Are Building

We build a multi-modal transport booking system: flights, trains, and buses in one schema. We cover the reference data (cities, operators, routes), the sellable inventory (trips, fare classes, seats), and the booking flow (bookings, passengers, payments, coupons, cancellations). We finish with UML use-case and ER diagrams.

The Booking Journey

Every booking follows the same five steps. Our tables are shaped around this flow.

One Schema for Flights, Trains, and Buses

The first big decision: do we build separate tables for each mode, or one shared set of tables with a mode column? Here is the trade-off.

Reference Tables — Cities, Operators, Routes

These tables rarely change. A city is a place you travel to or from. An operator is an airline, railway, or bus company. A route connects two cities for one operator.

-- ============ CITIES / STATIONS ============
CREATE TABLE cities (
    id       INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    name     VARCHAR(120) NOT NULL,      -- "New Delhi"
    code     VARCHAR(10)  NOT NULL,      -- DEL, BOM (airport/station code)
    state    VARCHAR(120) NULL,
    country  VARCHAR(80)  NOT NULL DEFAULT 'India',
    PRIMARY KEY (id),
    UNIQUE KEY uq_city_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ OPERATORS (airline / railway / bus company) ============
CREATE TABLE operators (
    id      INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    name    VARCHAR(150) NOT NULL,
    mode    ENUM('flight','train','bus') NOT NULL,
    code    VARCHAR(10)  NOT NULL,      -- 6E, AI, VRL
    rating  DECIMAL(2,1) NULL,          -- 4.2
    PRIMARY KEY (id),
    UNIQUE KEY uq_op_code (mode, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ ROUTES (source -> destination for an operator) ============
CREATE TABLE routes (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    operator_id     INT UNSIGNED    NOT NULL,   -- FK -> operators
    source_city_id  INT UNSIGNED    NOT NULL,   -- FK -> cities
    dest_city_id    INT UNSIGNED    NOT NULL,   -- FK -> cities
    distance_km     INT UNSIGNED    NULL,
    PRIMARY KEY (id),
    KEY ix_route_search (source_city_id, dest_city_id),
    CONSTRAINT fk_route_op  FOREIGN KEY (operator_id)    REFERENCES operators(id) ON DELETE CASCADE,
    CONSTRAINT fk_route_src FOREIGN KEY (source_city_id) REFERENCES cities(id),
    CONSTRAINT fk_route_dst FOREIGN KEY (dest_city_id)   REFERENCES cities(id),
    CONSTRAINT chk_route_diff CHECK (source_city_id <> dest_city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

UML Use-Case Diagram — Who Does What

Three actors use the system. The dashed box is the platform boundary.

Inventory — Trips, Fare Classes, Seats

A trip is one dated service on a route (flight 6E-233 on 25 Sep). Each trip sells one or more fare classes (Economy, Sleeper). Each class holds a set of seats. This is what a traveller actually buys.

-- ============ TRIPS (a dated service on a route) ============
CREATE TABLE trips (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    route_id      BIGINT UNSIGNED NOT NULL,     -- FK -> routes
    service_no    VARCHAR(20)     NOT NULL,     -- 6E-233, 12951
    departure_at  DATETIME        NOT NULL,
    arrival_at    DATETIME        NOT NULL,
    status        ENUM('scheduled','departed','arrived','cancelled') NOT NULL DEFAULT 'scheduled',
    PRIMARY KEY (id),
    KEY ix_trip_depart (route_id, departure_at),
    CONSTRAINT fk_trip_route FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ FARE CLASSES (price + seat counts per trip) ============
CREATE TABLE fare_classes (
    id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_id          BIGINT UNSIGNED NOT NULL,   -- FK -> trips
    class_name       VARCHAR(40)     NOT NULL,   -- Economy, Sleeper, AC 3-Tier
    price            DECIMAL(10,2)  NOT NULL,
    seats_total      INT UNSIGNED    NOT NULL,
    seats_available  INT UNSIGNED    NOT NULL,   -- fast counter for search results
    PRIMARY KEY (id),
    UNIQUE KEY uq_fare (trip_id, class_name),
    CONSTRAINT fk_fare_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE CASCADE,
    CONSTRAINT chk_seats CHECK (seats_available <= seats_total)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ SEATS (the seat map of a trip) ============
CREATE TABLE seats (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_id        BIGINT UNSIGNED NOT NULL,    -- FK -> trips
    fare_class_id  BIGINT UNSIGNED NOT NULL,    -- FK -> fare_classes
    seat_no        VARCHAR(8)      NOT NULL,    -- 12A, S4-33, U7
    status         ENUM('available','held','booked') NOT NULL DEFAULT 'available',
    PRIMARY KEY (id),
    UNIQUE KEY uq_seat (trip_id, seat_no),
    KEY ix_seat_pick (fare_class_id, status),
    CONSTRAINT fk_seat_trip FOREIGN KEY (trip_id)       REFERENCES trips(id)        ON DELETE CASCADE,
    CONSTRAINT fk_seat_fare FOREIGN KEY (fare_class_id) REFERENCES fare_classes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The Core Problem — Never Sell a Seat Twice

Booking Tables — Users, Bookings, Booking Items

A booking is one purchase by one user. It has a booking reference (the PNR). Each booking_item is one seat for one passenger on one trip. Multi-passenger and multi-leg trips are just many items under one booking.

-- ============ USERS ============
CREATE TABLE users (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name           VARCHAR(120)    NOT NULL,
    email          VARCHAR(190)    NOT NULL,
    phone          VARCHAR(20)     NULL,
    password_hash  VARCHAR(255)    NOT NULL,
    created_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_user_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ BOOKINGS (one purchase = one PNR) ============
CREATE TABLE bookings (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id       BIGINT UNSIGNED NOT NULL,     -- FK -> users
    booking_ref   VARCHAR(12)     NOT NULL,     -- PNR, e.g. MMT7X9K2
    status        ENUM('pending','confirmed','cancelled','refunded') NOT NULL DEFAULT 'pending',
    total_amount  DECIMAL(10,2)  NOT NULL DEFAULT 0,
    coupon_id     BIGINT UNSIGNED NULL,         -- FK -> coupons (added in next section)
    booked_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_booking_ref (booking_ref),
    KEY ix_booking_user (user_id, booked_at),
    CONSTRAINT fk_booking_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ BOOKING_ITEMS (one seat + one passenger + one trip) ============
CREATE TABLE booking_items (
    id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    booking_id        BIGINT UNSIGNED NOT NULL,   -- FK -> bookings
    trip_id           BIGINT UNSIGNED NOT NULL,   -- FK -> trips
    fare_class_id     BIGINT UNSIGNED NOT NULL,   -- FK -> fare_classes
    seat_id           BIGINT UNSIGNED NULL,       -- FK -> seats
    passenger_name    VARCHAR(120)    NOT NULL,
    passenger_age     TINYINT UNSIGNED NULL,
    passenger_gender  ENUM('M','F','O') NULL,
    price             DECIMAL(10,2)  NOT NULL,   -- price snapshot at booking time
    PRIMARY KEY (id),
    -- the double-booking safety net: one seat can be sold once per trip
    UNIQUE KEY uq_seat_once (trip_id, seat_id),
    KEY ix_item_booking (booking_id),
    CONSTRAINT fk_item_booking FOREIGN KEY (booking_id)    REFERENCES bookings(id)     ON DELETE CASCADE,
    CONSTRAINT fk_item_trip    FOREIGN KEY (trip_id)       REFERENCES trips(id),
    CONSTRAINT fk_item_fare    FOREIGN KEY (fare_class_id) REFERENCES fare_classes(id),
    CONSTRAINT fk_item_seat    FOREIGN KEY (seat_id)       REFERENCES seats(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Payments, Coupons, and Cancellations

A booking is paid by one or more payments. A coupon can lower the total. A cancellation records a refund request against a booking or a single item.

-- ============ COUPONS (create before the bookings FK below) ============
CREATE TABLE coupons (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    code           VARCHAR(30)     NOT NULL,   -- FLY500
    discount_type  ENUM('flat','percent') NOT NULL,
    value          DECIMAL(10,2)  NOT NULL,   -- 500 or 10 (%)
    max_discount   DECIMAL(10,2)  NULL,       -- cap for percent coupons
    valid_from     DATE            NULL,
    valid_till     DATE            NULL,
    usage_limit    INT UNSIGNED    NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_coupon_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- now wire the coupon link on bookings
ALTER TABLE bookings
    ADD CONSTRAINT fk_booking_coupon
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE SET NULL;

-- ============ PAYMENTS ============
CREATE TABLE payments (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    booking_id  BIGINT UNSIGNED NOT NULL,     -- FK -> bookings
    amount      DECIMAL(10,2)  NOT NULL,
    method      ENUM('card','upi','netbanking','wallet') NOT NULL,
    status      ENUM('initiated','success','failed','refunded') NOT NULL DEFAULT 'initiated',
    txn_ref     VARCHAR(64)     NULL,          -- gateway reference
    paid_at     TIMESTAMP       NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_txn (txn_ref),
    KEY ix_pay_booking (booking_id),
    CONSTRAINT fk_pay_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ CANCELLATIONS / REFUNDS ============
CREATE TABLE cancellations (
    id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    booking_id       BIGINT UNSIGNED NOT NULL,   -- FK -> bookings
    booking_item_id  BIGINT UNSIGNED NULL,       -- FK -> booking_items (NULL = whole booking)
    reason           VARCHAR(255)    NULL,
    refund_amount    DECIMAL(10,2)  NOT NULL DEFAULT 0,
    status           ENUM('requested','approved','refunded','rejected') NOT NULL DEFAULT 'requested',
    requested_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY ix_cancel_booking (booking_id),
    CONSTRAINT fk_cancel_booking FOREIGN KEY (booking_id)      REFERENCES bookings(id)      ON DELETE CASCADE,
    CONSTRAINT fk_cancel_item    FOREIGN KEY (booking_item_id) REFERENCES booking_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ER Diagrams — The Full Schema

Part 1: reference and inventory. Each arrow reads one-to-many (1 → N) from parent to child. routes links to cities twice (source and destination).

Part 2: the booking and payment side. A booking gathers items, payments, and any cancellation.

The Booking Lifecycle and Example Queries

Search: trips with seats left (Delhi → Mumbai, 25 Sep)

SELECT o.name AS operator, t.service_no, t.departure_at,
       fc.class_name, fc.price, fc.seats_available
FROM   trips t
JOIN   routes r        ON r.id = t.route_id
JOIN   operators o     ON o.id = r.operator_id
JOIN   fare_classes fc ON fc.trip_id = t.id
JOIN   cities src      ON src.id = r.source_city_id
JOIN   cities dst      ON dst.id = r.dest_city_id
WHERE  src.code = 'DEL'
  AND  dst.code = 'BOM'
  AND  t.departure_at >= '2026-09-25 00:00:00'
  AND  t.departure_at <  '2026-09-26 00:00:00'
  AND  fc.seats_available > 0
  AND  t.status = 'scheduled'
ORDER BY fc.price ASC;

Book a seat safely (one transaction)

START TRANSACTION;

-- 1) lock the seat so no one else can take it
SELECT id, status FROM seats
WHERE  trip_id = 4100 AND seat_no = '12A'
FOR UPDATE;
-- (app checks status = 'available'; if not, ROLLBACK and tell the user)

-- 2) create the booking
INSERT INTO bookings (user_id, booking_ref, status, total_amount)
VALUES (57, 'MMT7X9K2', 'pending', 4899.00);
SET @booking_id = LAST_INSERT_ID();

-- 3) add the seat as a booking item (UNIQUE(trip_id, seat_id) is the safety net)
INSERT INTO booking_items (booking_id, trip_id, fare_class_id, seat_id,
                            passenger_name, passenger_age, passenger_gender, price)
VALUES (@booking_id, 4100, 9001, 55012, 'Asha Rao', 29, 'F', 4899.00);

-- 4) mark the seat booked and lower the counter
UPDATE seats SET status = 'booked' WHERE id = 55012;
UPDATE fare_classes SET seats_available = seats_available - 1
WHERE id = 9001 AND seats_available > 0;

COMMIT;

Golden Rules