Liam O'Brien supervises bookings for a small regional railway, six stations and four trains, and keeps every booking as one row in a spreadsheet with the passengers in one cell and their seats in another, "S1-1, S1-2, S1-3". Twice last month the railway sold the same seat to two people. This post designs the reservation database from a conversation with him: eleven tables on SQL Server and PostgreSQL, a route that is an ordered list of stops with times and distances, fares copied onto the ticket so that next year's prices do not rewrite this year's, a seat that cannot be sold twice for the same date, a waiting list that moves up when someone cancels, and the spreadsheet shown accepting the double sale without a word.

Steps in this part
  1. Download the script for your engine: p08-sqlserver.sql or p08-postgresql.sql
  2. Run it: sqlcmd -S "(localdb)\MSSQLLocalDB" -I -f 65001 -i p08-sqlserver.sql, or psql -d postgres -c "CREATE DATABASE sp_p08" then psql -d sp_p08 -e -f p08-postgresql.sql
  3. Find the WHERE status = 'confirmed' on the unique index in section 2
  4. Read section 3: the double assignment refused, the cancellation that frees two seats, and the waiting list moving up
  5. Read 3e: a plain unique constraint on the same columns fails, and the two engines name different duplicates
  6. Read section 5: the spreadsheet's LIKE '%S1-1%' finds seat S1-11 too, and sells F1-1 twice in silence
  7. Paste sections 1 and 2 into the ER diagram tool and p08-sheet.csv into the normalization checker

The conversation

Liam: Each train has a number and a name and runs a fixed route. The 101, the Bayview Express, leaves Northgate at 06:00, stops at Riverside Junction, Millbrook and Eastport, and reaches Bayview at 08:15. The 102 does the same trip back in the evening. The 301 runs the same stations at other times, and the 201 turns off at Riverside Junction to Hillcrest.

Designer: So the times belong to the train, not to the line. Each stop is one row: train, position in the order, station, arrival, departure. Does every train run every day?

Liam: No. The 101 and 102 daily, the 201 Monday to Saturday, the 301 only Monday, Wednesday and Friday. Passengers sit in coaches: F1 is first class, S1 and S2 second. A first-class coach has four seats, a second-class twelve, numbered from 1. The ticket names coach and seat, like S1-11.

Designer: A train has a set of running days, a coach belongs to one train and has one class, a seat belongs to one coach. What is on a booking?

Liam: One train, one date, where they get on, where they get off, the class, and the people travelling. Maria Garcia booked herself and David Chen, Northgate to Bayview, first class, on Wednesday 4 November. When someone phones, they give us the six-letter booking code. And people come back: Maria and David go home on the 102 the same evening.

Designer: Then a passenger is written down once, and a separate row links passenger, booking and seat. How do you work out the price?

Liam: By class and distance. Second class is 20 up to 25 km, 35 up to 50, 55 up to 100; first class 45, 75 and 120. And if the fares change next year, old tickets must still show what was paid.

Designer: So every stop also carries its distance from the first station, the fares are a list of bands, and the fare is copied onto the passenger's row at booking time. Now the rule that was broken twice: when is a seat taken?

Liam: Strictly, only between where a passenger boards and where they leave; someone else could use the seat for the rest of the run. But for a railway our size, one seat, one confirmed passenger per train per date is fine. We lose a few short trips, but we never sell a seat twice. When seats run out, passengers go on a waiting list in the order the bookings came in, WL 1, WL 2.

Designer: Then the database can refuse a seat and a date appearing twice among the confirmed passengers, and each passenger row has a status and, while waiting, a position. What happens when someone cancels?

Liam: Aisha Khan cancelled the booking for herself and Tomas Silva. We refunded 180 of the 240 and kept 60. Their two seats went to the first two on the list, and the others moved up two places.

Designer: The cancellation is a row of its own with its time and refund; the passengers' status becomes cancelled, which frees the seats; the first two waiting passengers get them. All in one transaction, so the railway is never half-way through it.

From sentences to tables

Liam said became the entity and the tableThe 101 leaves Northgate at 06:00,stops at Riverside Junction, Millbrookand Eastport Route stop route_stops train_id, stop_seq, station_idarrival_time, departure_time, kmF1 is first class, S1 and S2 aresecond; four seats, or twelve,numbered from 1 Seat seats coach_id, seat_noSecond class is 20 up to 25 km, 35 upto 50 km, 55 up to 100 km Fare band fares travel_class, min_km, max_km, amountEach person on the booking gets a seatof their own; old tickets show whatwas paid Passenger row booking_passengers booking_id, passenger_id, seat_idstatus, fareWe refunded 180 of the 240 and kept 60 Cancellation cancellations booking_id, cancelled_atrefund_amount
Five sentences and what each became. The passenger row is the junction between a booking and a passenger, and it carries the seat, the status and the fare as it was charged.
Liam saidEntityColumns
six stations, four trains with a number and a namestations, trainsstation_code, station_name; train_no, train_name
the list in order, with arrival and departure at each stop; distanceroute_stopstrain_id, stop_seq, station_id, arrival_time, departure_time, km_from_origin
daily, Monday to Saturday, Monday Wednesday Fridaytrain_daysone row per train per weekday
F1 first class, S1 and S2 second; four or twelve seats numbered from 1coaches, seatscoach_code, travel_class; seat_no unique inside its coach
20 up to 25 km, 35 up to 50, 55 up to 100farestravel_class, min_km, max_km, amount
one train, one date, on, off, class, the people travelling, a six-letter codebookings, passengersbooking_code, train_id, journey_date, from_station_id, to_station_id, travel_class
each person gets a seat; old tickets must still show what was paid; WL 1, WL 2booking_passengersseat_id, status, waitlist_no, fare, and a copy of journey_date
we refunded 180 of the 240cancellationsbooking_id, cancelled_at, refund_amount

One seat, one confirmed passenger, one date

The passenger row is where the design earns its keep. It copies the journey date from the booking, so that the seat rule can be a rule on one table, and a two-column foreign key keeps the copy honest: a passenger row whose date differs from its booking's date is refused. 

CREATE TABLE booking_passengers (
    booking_id    integer       NOT NULL,
    passenger_id  integer       NOT NULL CONSTRAINT fk_booking_passengers_passengers REFERENCES passengers (passenger_id),
    journey_date  date          NOT NULL,
    seat_id       integer       NULL CONSTRAINT fk_booking_passengers_seats REFERENCES seats (seat_id),
    status        varchar(10)   NOT NULL CONSTRAINT ck_booking_passengers_status CHECK (status IN ('confirmed', 'waitlisted', 'cancelled')),
    waitlist_no   smallint      NULL CONSTRAINT ck_booking_passengers_waitlist_no CHECK (waitlist_no >= 1),
    fare          numeric(8,2)  NOT NULL,
    CONSTRAINT pk_booking_passengers PRIMARY KEY (booking_id, passenger_id),
    CONSTRAINT fk_booking_passengers_bookings FOREIGN KEY (booking_id, journey_date)
        REFERENCES bookings (booking_id, journey_date) ON UPDATE CASCADE,
    CONSTRAINT ck_booking_passengers_seat CHECK (
           (status = 'confirmed'  AND seat_id IS NOT NULL AND waitlist_no IS NULL)
        OR (status = 'waitlisted' AND seat_id IS NULL     AND waitlist_no IS NOT NULL)
        OR (status = 'cancelled'  AND waitlist_no IS NULL))
);

And the rule itself is a unique index on the seat and the date that only looks at confirmed rows, the same statement on both engines apart from the dbo. prefix: 

CREATE UNIQUE INDEX ux_booking_passengers_seat_date
    ON booking_passengers (seat_id, journey_date)
    WHERE status = 'confirmed';

Section 3a has the clerk give waitlisted Yuki Tanaka seat F1-3 while Aisha Khan holds it for the same day. PostgreSQL: 

ERROR:  duplicate key value violates unique constraint "ux_booking_passengers_seat_date"
DETAIL:  Key (seat_id, journey_date)=(3, 2026-11-04) already exists.

And SQL Server, with error 2601:

Cannot insert duplicate key row in object 'dbo.booking_passengers' with unique index 'ux_booking_passengers_seat_date'. The duplicate key value is (3, 2026-11-04).

Then Aisha cancels, in one transaction: the cancellation row with the 180 refund, her two rows become cancelled, and the first two waiting passengers take F1-3 and F1-4 while the rest move up two places. The same statement that was refused a moment ago now succeeds, and this is the state afterwards: 

 booking_code |    full_name    |   status   | seat | waitlist_no
--------------+-----------------+------------+------+-------------
 K4TR7Q       | Maria Garcia    | confirmed  | F1-1 |
 K4TR7Q       | David Chen      | confirmed  | F1-2 |
 M8WX2D       | Aisha Khan      | cancelled  | F1-3 |
 M8WX2D       | Tomas Silva     | cancelled  | F1-4 |
 P2LN6V       | Yuki Tanaka     | confirmed  | F1-3 |
 R7HC3J       | Fatima Al-Sayed | confirmed  | F1-4 |
 R7HC3J       | Priya Nair      | waitlisted |      |           1
 R7HC3J       | Wei Zhang       | waitlisted |      |           2
(8 rows)

Aisha's cancelled row keeps F1-3 as history and Yuki holds F1-3 as confirmed, which is exactly what the filter allows. Section 3e shows why the filter is not optional: a plain unique constraint on the same two columns fails on the loaded data, and the two engines complain about different rows. PostgreSQL names the cancelled-plus-confirmed pair on seat 3; SQL Server, which treats NULLs as equal in a unique index, names the waitlisted rows with no seat at all: 

ERROR:  could not create unique index "uq_booking_passengers_seat_date"
DETAIL:  Key (seat_id, journey_date)=(3, 2026-11-04) is duplicated.
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.booking_passengers' and the index name 'uq_booking_passengers_seat_date'. The duplicate key value is (<NULL>, 2026-11-04).

One SQL Server trap on the way: a filtered index needs QUOTED_IDENTIFIER on. The script sets it itself, and a copy without that line, run without sqlcmd -I, failed to create the index with error 1934 and then accepted the double sale.

The ER diagram

ER diagram of the railway database: stations, trains, route_stops, train_days, coaches, seats, fares, passengers, bookings, booking_passengers and cancellations with their foreign keys
Eleven tables drawn by the ER diagram tool from section 2 of the PostgreSQL script. Route stops join trains to stations with the times as payload; bookings reach stations twice, once for boarding and once for alighting.

Five questions the booking office asks

The journey date in every query is Wednesday 4 November 2026. Which trains run from Northgate to Riverside Junction that day, found by two stops of the same train in the right order and a row in the running days: 

 train_no |     train_name      | departs  | arrives
----------+---------------------+----------+----------
      101 | Bayview Express     | 06:00:00 | 06:25:00
      201 | Hillcrest Passenger | 08:30:00 | 08:55:00
      301 | Market Day Fast     | 11:00:00 | 11:22:00
(3 rows)

The same pair on the Sunday returns the 101 alone. Seats free on the 101 that day, per coach, with the free seat numbers listed:

 coach_code | travel_class | seats | taken | free |              free_seats
------------+--------------+-------+-------+------+---------------------------------------
 F1         | first        |     4 |     4 |    0 |
 S1         | second       |    12 |     4 |    8 | 4, 5, 6, 7, 8, 9, 10, 12
 S2         | second       |    12 |     0 |   12 | 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
(3 rows)

The passenger chart for the guard, and the waiting list under it:

 seat  |    full_name    | board | alight | booking_code
-------+-----------------+-------+--------+--------------
 F1-1  | Maria Garcia    | NGT   | BYV    | K4TR7Q
 F1-2  | David Chen      | NGT   | BYV    | K4TR7Q
 F1-3  | Yuki Tanaka     | RVJ   | EPT    | P2LN6V
 F1-4  | Fatima Al-Sayed | NGT   | MLB    | R7HC3J
 S1-1  | Elena Rossi     | NGT   | RVJ    | T5BM9K
 S1-2  | Kwame Mensah    | NGT   | RVJ    | T5BM9K
 S1-3  | Hannah Weber    | NGT   | RVJ    | T5BM9K
 S1-11 | Omar Haddad     | MLB   | BYV    | V3QF8N
(8 rows)

 travel_class | waitlist_no | full_name  | board | alight | booking_code
--------------+-------------+------------+-------+--------+--------------
 first        |           1 | Priya Nair | NGT   | MLB    | R7HC3J
 first        |           2 | Wei Zhang  | NGT   | MLB    | R7HC3J
(2 rows)

A booking looked up by the code a caller reads out, every passenger with their status and the fare charged:

 booking_code | train_no | journey_date | board | alight | travel_class |    full_name    |   status   | seat | fare
--------------+----------+--------------+-------+--------+--------------+-----------------+------------+------+-------
 R7HC3J       |      101 | 2026-11-04   | NGT   | MLB    | first        | Fatima Al-Sayed | confirmed  | F1-4 | 75.00
 R7HC3J       |      101 | 2026-11-04   | NGT   | MLB    | first        | Priya Nair      | waitlisted | WL 1 | 75.00
 R7HC3J       |      101 | 2026-11-04   | NGT   | MLB    | first        | Wei Zhang       | waitlisted | WL 2 | 75.00
(3 rows)

And revenue per train for November, confirmed fares plus what the railway kept from cancellations, waitlisted fares excluded:

 train_no |     train_name      | ticket_fares | cancellation_charges | revenue
----------+---------------------+--------------+----------------------+---------
      101 | Bayview Express     |       485.00 |                60.00 |  545.00
      102 | Northgate Express   |       185.00 |                 0.00 |  185.00
      201 | Hillcrest Passenger |        70.00 |                 0.00 |   70.00
      301 | Market Day Fast     |       190.00 |                 0.00 |  190.00
(4 rows)

The spreadsheet, and the seat it sold twice

Section 5 builds bookings_flat, one row per booking with the passengers in one cell and the seats in another, loaded with the same twelve bookings. Who sits in S1-1 of the 101 on the 4th, the obvious way: 

 booking_code |               passengers                |      seats
--------------+-----------------------------------------+------------------
 T5BM9K       | Elena Rossi, Kwame Mensah, Hannah Weber | S1-1, S1-2, S1-3
 V3QF8N       | Omar Haddad                             | S1-11
(2 rows)

LIKE '%S1-1%' matches S1-11 as well, and the row it should have found names three people with no way to tell which of them has which seat. Worse, a new flat booking for Ngozi Okafor in F1-1 on the same train and date is accepted without a word, and finding the double sale afterwards means cutting every seat list apart with STRING_SPLIT or string_to_table: 

 seat | times_sold
------+------------
 F1-1 |          2
(1 row)

The proper tables answer the first question with one row and refuse the second insert, as section 3a showed:

 seat |  full_name  | booking_code
------+-------------+--------------
 S1-1 | Elena Rossi | T5BM9K
(1 row)
The normalization checker's verdict on the railway's booking spreadsheet
The booking sheet in the normalization checker: it finds the two parallel lists, passengers and seats, and proposes one row per passenger. Real screenshot of the tool on this site.

What this design does not do

Liam accepted the simpler seat rule, and the schema enforces only that: one confirmed passenger per seat per date, not per segment, so a seat free from Eastport onwards stays unsold. Several other rules are left to the booking procedure and stated here so the post does not claim them: that the seat belongs to the booking's train and class, that the boarding stop comes before the alighting stop, that the train runs on the journey date, that waitlist positions have no gaps, and that fare bands do not overlap. The cancellation and the promotions happen in one transaction in the script; nothing stops a clerk doing them in two.

The three habits this project teaches: a route is rows of stops, not a string of names; a price that may change tomorrow is copied onto the ticket today; and a rule that applies only to some rows is a filtered unique index, which both engines spell the same way.

Frequently asked

What tables does a railway reservation system database need?
Eleven in this design: stations, trains, route_stops (train, order, station, arrival, departure, distance), train_days, coaches, seats, fares (class and distance band), passengers, bookings (train, date, boarding and alighting station, class, a code), booking_passengers (the seat assignment with status, waitlist position and the fare charged) and cancellations. Seat availability, the chart and revenue are queries.
How do you prevent the same seat being booked twice in SQL?
With a filtered unique index on the seat assignment table: CREATE UNIQUE INDEX ux_booking_passengers_seat_date ON booking_passengers (seat_id, journey_date) WHERE status = 'confirmed'. Waitlisted rows have no seat and cancelled rows keep theirs as history, and neither is in the index. A plain unique constraint on the same columns failed on this data on both engines, for different reasons: PostgreSQL on the cancelled-plus-confirmed pair, SQL Server on the NULL seats of waitlisted rows.
Why is storing seats as a comma-separated list a mistake?
Because LIKE '%S1-1%' also matches S1-11, the row that matches names three people with no seat each, and a second sale of F1-1 for the same train and date is accepted without any error; finding it afterwards needs STRING_SPLIT or string_to_table over every row. One row per passenger with a seat_id, protected by a unique index, answers the question with one row and refuses the double sale.

Next: Part 9, an online shop: variants, carts that are not orders, orders that keep their prices, shipments as a history of events, and one review per customer per product.