Train Search and Seat Availability DB Design (IRCTC)

0) Problem Restatement

Flipkart asked: design the database for train search like IRCTC (Indian Railways). A user searches trains from station A to station B on a date. We must return trains that stop at A and later at B (in that order), with departure and arrival times, and seat availability per class (Sleeper, 3AC, 2AC) and fare. A train passes through many stations, and a seat can be sold for different parts of the route (e.g., seat 12 from Delhi to Kanpur and again from Kanpur to Patna).

1) Tables

CREATE TABLE stations (station_code TEXT PRIMARY KEY, name TEXT, city TEXT);

CREATE TABLE trains (train_no TEXT PRIMARY KEY, name TEXT, runs_on_days BIT(7));   -- Mon..Sun

CREATE TABLE train_stops (                -- the route, in order
  train_no     TEXT REFERENCES trains,
  stop_seq     INT,                       -- 1, 2, 3, ...
  station_code TEXT REFERENCES stations,
  arrival_time TIME, departure_time TIME,
  day_offset   INT,                       -- 0 = same day as start, 1 = next day...
  distance_km  INT,
  PRIMARY KEY (train_no, stop_seq)
);
CREATE INDEX ON train_stops (station_code, train_no, stop_seq);

CREATE TABLE train_runs (                 -- one row per train per journey date
  run_id BIGINT PRIMARY KEY, train_no TEXT, journey_date DATE, UNIQUE (train_no, journey_date)
);

CREATE TABLE coaches (run_id BIGINT, coach_no TEXT, class TEXT, seat_count INT);

CREATE TABLE seat_bookings (             -- a seat is occupied between two stop numbers
  run_id BIGINT, coach_no TEXT, seat_no INT,
  from_seq INT, to_seq INT,              -- occupied from stop from_seq up to (not including) to_seq
  pnr TEXT
);
CREATE TABLE availability (              -- precomputed counts for fast search
  run_id BIGINT, class TEXT, from_seq INT, to_seq INT, available INT,
  PRIMARY KEY (run_id, class, from_seq, to_seq)
);

2) The Search Query

SELECT a.train_no, a.departure_time, b.arrival_time
FROM train_stops a
JOIN train_stops b ON b.train_no = a.train_no AND b.stop_seq > a.stop_seq   -- B comes after A
JOIN trains t ON t.train_no = a.train_no
WHERE a.station_code = 'NDLS' AND b.station_code = 'PNBE'
  AND get_bit(t.runs_on_days, extract(isodow FROM DATE '2026-10-01' - a.day_offset)::int - 1) = 1;

• The index on (station_code, train_no, stop_seq) finds all trains stopping at A and at B quickly, and the join checks the order.
• runs_on_days + day_offset handles trains that started a day earlier (the date at station A isn't the train's start date).
• This route-matching result changes rarely, so cache it per (A, B, weekday).

3) Seat Availability Per Segment

• A seat is free for journey A→B if no booking of that seat overlaps the stops [seq_A, seq_B). Two bookings don't conflict if one ends at or before the other starts.
• Counting free seats per class for every search this way is expensive, so keep an availability table (or cache) per (run, class, segment) updated on each booking and cancellation, and serve search counts from it.
• Quotas: separate pools (General, Ladies, Tatkal, Senior), with availability per quota.

flowchart LR
    Q["Search A to B, date"] --> RM["Route match - train_stops index, cached"]
    RM --> AV["Availability - cached counts per class"]
    AV --> R["Results: trains, times, seats, fare"]
    BK["Booking"] --> LOCK["Allocate seat - lock run/class, check overlap"]
    LOCK --> SB[("seat_bookings")]
    LOCK -->|"update"| AV

4) Booking and the Tatkal Rush

• Booking allocates a specific seat within a transaction: lock the run and class (or the coach), find a seat with no overlapping booking for [seq_A, seq_B), and insert the booking. If none is free, add to the waitlist (WL) or RAC queue.
• Tatkal at 10:00 AM: a huge spike. Serve search from caches (it can be slightly stale), put booking requests into a queue with fair ordering, and process allocations per train run sequentially (a single writer per run) to avoid lock storms.
• Search shows "available: ~34" as guidance, and the booking step is authoritative.

5) Wrap-Up

Model stations, trains, ordered train_stops (with day offsets) and per-date train runs. Search by joining stops at A and B where B's sequence comes after A's (indexed by station), plus a running-day check. Track seats as bookings over stop ranges so segments can be reused, keep precomputed availability per class and quota for fast search, and allocate seats transactionally (or via a per-run queue at Tatkal time) with waitlists when full.