Train Search and Seat Availability DB Design (IRCTC)

0Problem RestatementProblem

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

1Tables

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)
);

2The 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).

3Deep Dive — A seat that is free for part of the journeyDeep dive

A train runs Delhi → Agra → Bhopal → Nagpur. A seat booked Delhi → Agra is free from Agra onward. Availability is not a property of the seat, it is a property of a seat over a range of stops.

%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
  BK["Seat 12B booked Delhi to Agra"] --> FLAG["available = false"]
  FLAG --> Q["Search Bhopal to Nagpur"]
  Q --> HIDE["Seat 12B hidden - it is actually free for that stretch"]
  FLAG --> LOSS["A long-distance train sells a fraction of its real capacity"]

%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
  Q["Search A to B, date"] --> RM["Route match - trains stopping at A then later B"]
  RM --> SEGS["Segments spanned: seq_A to seq_B"]
  SEGS --> AV[("availability(run, class, segment) - maintained counts")]
  AV --> MIN["Free seats = min count across the spanned segments"]
  MIN --> RES["Results per class, per quota"]
  BOOK["Booking"] --> DEC["Decrement each spanned segment"]
  CANC["Cancellation"] --> INC["Increment each spanned segment"]

4Booking 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.

5Wrap-UpWrap-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.