Section 5: The Counter That Cannot Be Wrong - Data Modeling

Executive Summary: This is the pivotal decision of the module. A hotel does not sell rooms; it sells fungible room types, so inventory is a counter per (property, room_type, date) rather than a row per physical room-night. We cost all three candidate models with the ledger's arithmetic, lay out the seven tables, walk Dana's booking decrementing exactly three inventory rows — and not the fourth — and restate the correctness invariant as a check constraint. The section closes on the argument that decides the storage engine: under last-write-wins, two concurrent increments lose one, and a lost update is an oversold room with no error raised anywhere.

The interviewer uncaps a marker. "Show me your tables. Start with inventory."

Everything Section 4's contract promised — the 409 sold_out, the ten-minute hold, the idempotency key — is a promise something underneath has to keep. Section 7's atomic decrement, Section 8's availability bitmap and Section 9's overbooking allowance are all downstream of what you write in the next ninety seconds.

Three Models, Costed Before Chosen

Do not answer with a schema. Answer with three schemas and their arithmetic, which is what turns a preference into a decision (D1):

The first is the Seat-Map Reflex, seductive because it is how the physical world looks: 220 rooms times 365 dates is 80,300 lockable rows at the Marlin Bay alone, 547M across the chain. It works, and nobody does it — for a reason that has nothing to do with row counts. If Dana's booking owns room 1412 for three nights, the front desk can no longer put a family in adjoining rooms, move her off the corridor being repainted, or upgrade her when a suite frees up. Each of those becomes a search for a reassignment that doesn't break somebody else's three-night chain. You have turned a counter decrement into bin packing, and the people who pay are standing at a desk at 11 p.m.

The third model stores what the business thinks it stores — one interval per booking, [check_in, check_out) — and derives availability by counting overlaps. Only ~24,000 rows a year for one hotel, and perfectly correct. It is also unaffordable in the only direction that matters: Section 3 measured a city search at 6,300 inventory cells against 4,100 peak search QPS, and under this model each of those 25.8 million cell reads per second is an interval scan rather than a primary-key lookup.

Key Insight: Inventory models the sellable product, not the physical world. Four room types times 365 dates is 1,460 rows — 292 KB, so a hotel's entire availability picture lives in a Postgres buffer pool. The model is cheap because it matches what is for sale. Flip it only when the unit stops being interchangeable: one unit per listing (Section 11), or a boutique property that sells the named corner suite as the product.

That second clause is D2: room assignment is deferred to check-in day. Dana buys "an Ocean-View Suite for three nights"; the front desk picks 1412 at 3 p.m. with the whole day's picture in front of them. "But she asked for a high floor away from the elevator." Those are preferences on the booking, honoured at assignment time — requests, not inventory. The flip trigger: a property where the specific unit is the product, whose room types then degenerate to physical_count = 1, which is already Section 11's model.

Quick check before moving on: