Project: a railway booking database (IRCTC-like)

The idea

Indian Railways' booking system, run through IRCTC, is one of the busiest booking platforms in the world. Its real design is not public, and it handles things we will skip: quotas (Tatkal, ladies, senior citizen), seat-level availability per segment, charts, refunds and much more. Our model keeps the core questions a learner can answer with joins and window functions. Station codes are real; train numbers, times, PNRs and fares are made up.

The schema

CNF means a confirmed berth. RAC (Reservation Against Cancellation) means you may travel but share a side berth until one frees up. WL means waiting list: you get a berth only if enough people ahead of you cancel. A real PNR can hold up to six passengers; we keep one passenger per booking so the queries stay simple.

Trains between two stations, in the right direction

The heart of the model is train_stops. A train goes from Surat to Vadodara only if it stops at both and Surat comes first: the stop_order of the "from" stop must be smaller than that of the "to" stop. Join train_stops to itself, once for each station:

SELECT t.train_no, t.name, a.departure, b.arrival
FROM train_stops a
JOIN train_stops b ON b.train_no = a.train_no AND a.stop_order < b.stop_order
JOIN trains t ON t.train_no = a.train_no
WHERE a.station_code = 'ST' AND b.station_code = 'BRC'
ORDER BY a.departure;
-- 20003 | Gujarat Intercity    | 08:45 | 10:20
-- 10001 | Mumbai Delhi Express | 19:10 | 20:50

Bookings by status

SELECT train_no, journey_date, status, COUNT(*) AS bookings
FROM bookings
GROUP BY train_no, journey_date, status
ORDER BY train_no, journey_date, status;
-- 10001 | 2025-11-14 | CNF | 4
-- 10001 | 2025-11-14 | RAC | 1
-- 10001 | 2025-11-14 | WL  | 4
-- ...

Waiting-list position

Waiting lists are kept per train, date and class, in the order people booked. ROW_NUMBER with PARTITION BY gives each WL booking its place in its own queue:

SELECT b.pnr, p.name, b.class_code,
       ROW_NUMBER() OVER (PARTITION BY b.train_no, b.journey_date, b.class_code
                          ORDER BY b.booked_at) AS wl_position
FROM bookings b JOIN passengers p ON p.id = b.passenger_id
WHERE b.status = 'WL' AND b.train_no = '10001' AND b.journey_date = '2025-11-14';
-- 4501234566 | Arjun Reddy | 3A | 1   (booked 1 Nov, 07:10)
-- 4501234564 | Imran Khan  | 3A | 2
-- 4501234565 | Sneha Patil | 3A | 3
-- 4501234568 | Karan Mehta | SL | 1

Revenue per class

Count only CNF and RAC tickets as revenue: in our model, waiting-list tickets that never confirm are refunded. Join to classes for readable names.

SELECT c.name AS class, COUNT(*) AS bookings, SUM(b.fare) AS revenue
FROM bookings b JOIN classes c ON c.code = b.class_code
WHERE b.status IN ('CNF', 'RAC')
GROUP BY c.name
ORDER BY revenue DESC;
-- AC 3 Tier    | 5 | 12350
-- AC 2 Tier    | 1 | 4000
-- AC Chair Car | 2 | 1550
-- Sleeper      | 1 | 760

Ideas to extend it

• A coaches and berths table, and a seat_allocations table per segment (a berth from MMCT to BRC can be sold again from BRC to NDLS).
• A quota column (GN, TQ for Tatkal, LD, SS) and availability per quota.
• A trigger that moves the first RAC or WL booking up when a CNF booking is cancelled (module 11).
• A booking_passengers table so one PNR holds several passengers.

Common mistakes

• Forgetting the direction check, so a Surat → Vadodara search shows trains going the other way.
• Numbering the waiting list across all classes or dates. PARTITION BY train, date and class.
• Counting WL fares as revenue.
• Joining stops without matching train_no, which pairs stops of different trains.