Aviation

The first thing someone new to airline reservation systems draws is almost always the same:

reservations
  id, passenger_name, flight_no, seat, status, created_at

This table works for a month. In the second month it collapses. The reason is not performance; it is misunderstanding not what a PNR is, but what it represents.

What a PNR is not

A PNR — Passenger Name Record — is not a record of a passenger. It is a record of a journey. It can contain several passengers, several flights and several services, and each of those parts can be changed at different times by different parties.

Concretely, a single PNR can carry all of these at once:

• Two adults and an infant (the infant has no seat of its own but has its own ticket)
• Istanbul to London outbound, London to Edinburgh to Istanbul inbound (two legs back)
• One leg operated by another airline under a codeshare
• A separately purchased baggage allowance and a seat selection
• A note added by the agency that the airline cannot see

The data model equivalent of that structure is not a row, it is a tree. And the branches of the tree live independently: cancelling one leg does not cancel the other, and removing one passenger does not delete the PNR.

The real point: a PNR is a contract

The modelling mistake comes from one assumption: “a reservation is a record of a state in my system.”

It is not. A PNR is the current state of a contract between parties. Those parties are the passenger, the agency that sold the ticket, the carrying airline, and sometimes a partner airline. Each has different authority over the record and none of them owns it alone.

That has three concrete consequences.

First: a change is not an overwrite. When a passenger name is corrected, the old name does not disappear. Who changed the contract and when has to stay provable, because that is where you look when there is a dispute. So the natural storage form for a PNR is not a row being overwritten but an append-only event log. If you do not build it that way from the start, six months in you cannot answer “when did this name change.”

Second: splitting is a core operation. If one person out of a party of four changes flights, that person is removed from the PNR and moved into a new PNR, with a link left between the two. The industry calls this a split, and it is not an edge case, it is a daily operation. A single-table model has no equivalent for it.

Third: ownership is transferable. Control of a PNR booked through an agency can move to the airline. The record’s “owner” field changes over the record’s lifetime.

Why it is still six characters

The code used to find a PNR, something like A3F9KL — the record locator — is six alphanumeric characters. That limit comes from 1960s terminal screens and it is still there, because every system in the industry expects it.

The practical consequence is that this code is not unique. The same locator can refer to different reservations at different airlines, and after enough time it can be reused even within the same airline. Systems that make the locator a primary key are why strange collisions turn up years later.

The right reading is that a locator is a lookup key, not an identity. Identity should be your own durable identifier; the locator is the language you use to talk to the outside world.

What to do instead

Model the reservation as an event log, with today’s state as a view derived from that log. “Current state” can be its own table, but it must not be the source of truth.

Build the tree as an actual tree: passengers, legs and services as separate entities, with the PNR as the container holding them together. Even if you do not support splitting in the first version, make sure the model allows it — adding it later is close to a rewrite.

Never make the record locator a primary key.

None of this is specific to aviation; the consequences just show up earlier there. We make the same mistake modelling a shopping cart.

A 180-seat aircraft sells 186 tickets. Engineers hearing this for the first time go looking for the bug. There is no bug — it is deliberate.

Why the number is higher

The share of passengers who do not show up for a flight — the industry calls them no-shows — varies by route but is not a small number. Business travellers on flexible tickets change plans at the last minute, connecting passengers miss the first leg, and some people simply never arrive.

A seat is a perishable product: the moment the door closes its value drops to zero and it can never be sold again. Hotel rooms, concert tickets and ad impressions are in the same category.

So the airline sells roughly as many extra tickets as it expects no-shows. When the estimate is wrong there are not enough seats at the gate and a passenger is denied boarding, meaning they are kept off the flight either voluntarily or involuntarily.

What makes the decision tractable

The interesting part is the compensation. What is owed to a passenger who is kept off a flight is defined in advance and regulated: regulation 261/2004 in Europe, DOT rules in the United States, other regimes elsewhere. The amount varies with distance and delay, but it is computable.

That single property is what makes the whole model possible. Because the cost of the risk is known, the decision stops being a gamble and becomes an optimisation problem:

expected gain = P(no-show) × extra ticket revenue
expected cost = P(too many passengers) × compensation + reputational cost

The airline sells enough extra tickets to keep the second smaller than the first. No more than that.

What changes on the software side

The architectural conclusions here are useful even if you never touch aviation.

An inventory counter cannot be a single number. The number of sellable seats is not the number of physical seats; it is a derived value computed from class, fare and forecast. If your system has one field called available_seats, the model is already wrong.

Overselling is not an exception, it is a normal path. The boarding process has to support volunteer solicitation, upgrades, rebooking and compensation routinely. If you code those as an error path, you end up with an error path that runs thousands of times a year — and error paths are always the least tested ones.

The decision point has to be late. Who gets kept off is determined at the gate, not at the point of sale. The system has to be able to stay undecided until the last moment; a design that binds early does not work here.

Why e-commerce does not do the same thing

In e-commerce, selling more than you hold — overselling — counts as a bug, and rightly so. The difference is not in the model but in whether the compensation can be priced.

At an airline, what the passenger is owed is set by law. In e-commerce, when an order is cancelled the customer’s loss is not regulated; the cost lands entirely on reputation and cannot be measured. You cannot optimise a risk you cannot measure.

That is why e-commerce systems protect stock with a reservation: instead of decrementing a counter, they make a promise with an expiry. It is the solution to the same problem in the case where compensation cannot be computed.

Both domains ask the same question: how do you sell a perishable resource in a world where demand is uncertain. Their answers differ, because in one the cost of being wrong is known and in the other it is not.