Exercise 2: Range Types, Exclusion Constraints, and the Hotel Double-Booking Problem — Possible Solution
====================================================================

HOW A RANGE TYPE REPRESENTS THE BOOKING
------------------------------
Per this chapter, "a hotel booking system can store a reservation's
stay as a single daterange," using the example:

  CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT,
    stay DATERANGE
  );

Rather than two separate columns (check_in_date, check_out_date) that
have to be compared manually against every other booking's own two
columns, the entire stay is one value — a single DATERANGE — that can
be compared to another range directly.

HOW THE OVERLAP OPERATOR CHECKS FOR CONFLICTS
------------------------------
Per this chapter's own example query, checking for a conflicting
booking is:

  SELECT * FROM bookings
  WHERE room_id = 12 AND stay && DATERANGE('2026-08-01', '2026-08-05');

The && operator directly asks "does this range overlap any existing
range," in one operation, for the exact room being checked.

WHY HAND-WRITTEN BOUNDARY LOGIC IS WEAKER
------------------------------
Per this chapter, "that single overlap operator (&&) replaces
hand-written boundary logic like start1 <= end2 AND start2 <= end1."
Writing that boundary comparison by hand is easy to get subtly wrong
(off-by-one errors on inclusive vs. exclusive boundaries are a classic
source of real bugs in date-range logic), and it has to be re-derived
correctly every single place an overlap check is needed in the
application. The && operator is a single, well-tested, built-in
mechanism that eliminates that whole class of hand-written bugs.

HOW THE EXCLUSION CONSTRAINT MAKES THIS A DATABASE-LEVEL GUARANTEE
------------------------------
Per this chapter, Postgres "can go further and enforce this as a
genuine database-level integrity guarantee via an exclusion
constraint — EXCLUDE USING gist (room_id WITH =, stay WITH &&) —
making it structurally impossible to insert an overlapping booking
for the same room at all." This is the crucial difference from simply
running a SELECT check before an INSERT in application code: an
application-level check can still race (two bookings could both pass
the check simultaneously before either commits), while an exclusion
constraint is enforced by the database itself at the moment of
insertion, closing that race condition entirely — something no simple
MySQL equivalent provides, per this chapter's own closing statement.

WHY THIS WORKS AS AN ANSWER
------------------------------
It walks through the schema, the query, and the constraint using the
chapter's own exact examples, and specifically explains why the
constraint (not just the query) is what actually solves the
double-booking problem reliably, including the race-condition point
the chapter implies but a plain SELECT-before-INSERT check wouldn't
address.