How does a hotel reservation system prevent double booking?

In this article

1. Before you start
2. Why interviewers ask this
3. Hotel reservation system from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

This page is a worked case from the machine coding rounds guide, where a booking system is a common practical problem.

Why interviewers ask this

A booking problem looks like a form with a few fields, yet it hides the hardest ideas in coding rounds: time intervals, overlap, and two users acting at the same moment. Interviewers use it to see whether a candidate finds the core correctness problem before writing classes. It is reported by candidates in low-level design rounds; see the low-level design interview questions for the neighbouring practical tasks.

A weak answer lists classes such as Hotel, Room and Guest and checks availability with a loop. A strong answer states that a booking is an interval, defines how two intervals overlap, and explains what happens when two requests arrive together. A Staff-level answer moves the final protection into the database, because application code that checks first and inserts later can be beaten by a concurrent request. The sections below build exactly that.

Hotel reservation system from first principles

Hotel reservation system

A hotel reservation system is a service that records which guest holds which room for which dates, answers whether a room or room type is free for a requested stay, and rejects any booking that would put two guests in one room on the same night.

Half-open interval

A half-open interval is a range that includes its lower bound and excludes its upper bound, written [start, end); for a hotel stay the check-in day is inside the range and the check-out day is not.

Exclusion constraint

An exclusion constraint is a database rule that compares any two rows with chosen operators and rejects a row when every comparison is true; with the overlap operator it makes overlapping ranges impossible to store.

In plain words

Think of a paper wall chart in a small guesthouse. Each room is a row, each night is a column, and a booking is a bar drawn across consecutive columns. Two bars in one row must never share a column. The analogy breaks in one place. On paper one clerk draws every bar. A real system takes requests from many people and channels together, so the chart must defend itself.

In one precise sentence: a booking is a room and a half-open interval of dates. The system keeps all intervals for one room disjoint.

Step 1: A stay is a half-open interval of nights

A stay is stored as a room, a check-in date and a check-out date, and the stay owns the nights from check-in up to but not including check-out. The PostgreSQL 18 range types documentation says the two-argument constructor builds a range in standard form, with an inclusive lower bound and an exclusive upper bound, and that [) is the default when the third argument is omitted. Discrete range types such as daterange are likewise kept in the canonical [) form.

This convention removes the same-day turnover problem. One guest leaves on the day another arrives, and the two stays share a date but no night. With inclusive end dates the system would call that a conflict. The number of nights is simply check-out minus check-in.¹ The same documentation uses room reservations as its own example of why ranges exist. You now know what to store: one row per stay, with a date range and no end-date special cases.

Step 2: Two stays overlap when each starts before the other ends

Two half-open stays overlap when the first starts before the second ends and the second starts before the first ends. As a test on dates, a.start < b.end and b.start < a.end. The test is checked in the data script against the sets of nights of every pair of short stays, and both agree.¹ PostgreSQL names the same idea with the && operator, which is true when two ranges share any point.

Input: two intervals. Output: true or false. Edge touching, such as one stay ending the day another starts, returns false because the upper bound is exclusive. Containment, partial overlap from either side and identical stays all return true. A frequent bug is writing <=, which turns every touching pair into a conflict. Now you can decide whether two bookings clash; next you must make that decision binding for every request.

Step 3: The database refuses overlapping stays

An exclusion constraint makes the overlap rule a property of the table instead of a habit of the code. The PostgreSQL range types page shows a reservation table whose constraint prevents any overlapping values, and a room version that needs the btree_gist extension so that room equality combines with range overlap:

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

Read the last line as: reject a row when another row has the same room and an overlapping range. The constraints documentation adds that the constraint creates its index automatically, and advises unique, exclusion or foreign-key constraints for rules across rows. For date-only stays, daterange is the matching type. Check-then-insert code would race here; the constraint cannot.

Step 4: Concurrent requests either confirm or must retry

A race condition is a bug where the result depends on the order in which two simultaneous requests interleave. Under the default Read Committed isolation level (an isolation level is the setting that decides what one transaction may see of another's changes), the transaction isolation documentation says a query sees only data committed before it began. Two requests can therefore both read a room as free, then both insert. In a simulated interleaving in the data script (pure Python, not a database run), the check-then-insert order stores two rows for one room and one stay; with the constraint, only one is stored.¹

With the constraint the loser gets an error, and the service turns it into a clear "no longer available" response. Serializable isolation also protects against this, but only if every competing transaction runs at Serializable, and the same page says applications must be prepared to retry after serialization failures. Either way, failure is an expected result, not a crash.

1 · Interval2 · Overlap test3 · Database guard4 · Confirm or retryReplay

Step-by-step description

1. A stay is a half-open interval of nights.
2. Each stay starts before the other ends.
3. The constraint refuses overlaps.
4. A conflict becomes a clear answer.

• A stay is stored as a half-open interval of nights.
• The database constraint refuses overlapping stays for one room.

A minimal schema and three endpoints

A minimal hotel reservation design uses a handful of tables and three endpoints. The tables:

• hotel: one row per property.
• room_type: belongs to a hotel, with a name and a capacity.
• room: one physical room, belonging to a room type.
• reservation: the guest, either a room or a room_type, the stay as a daterange called during, a status, and a unique request_id. The exclusion constraint covers rows that hold a specific room and an active status.
• inventory: one row per room type and night, with total and reserved columns (see room types below).

The application programming interface (API) needs three endpoints:

• GET /availability?type=<type>&from=<date>&to=<date> returns the free count per night.
• POST /reservations with a request_id creates a hold or a reservation.
• DELETE /reservations/<id> cancels it and frees its nights.

Worked example

Take three stays for one room, with day 1 standing for 1 November 2026 (illustrative dates). Guest A holds [1, 3), nights 1 and 2. Guest B asks for [3, 5). Guest C asks for [2, 4).¹ Each stay is two nights long. A and B do not overlap, because day 3 is the upper bound of A and the lower bound of B, so B is accepted. C overlaps A on night 2, so C is refused for that room. If the end dates were inclusive, A and B would wrongly conflict.¹

Now a room type. A hotel has three identical rooms (illustrative) and four stays: [1, 3), [2, 4), [3, 5) and [2, 6). Counting stays per night gives the free rooms per night.¹ This count is the availability read that a search page shows, not the guard: the guard that stops two requests taking the last room is described under room types below.

A new request fits only if every night it covers has a free room. The request [2, 4) needs nights 2 and 3, both at zero free, so it fails. The request [5, 7) needs nights 5 and 6, with two and three free, so it fits.¹ Ten night rows would store these four stays in a night-by-night design, against four interval rows.¹

Text description of the diagram

• Rooms booked per night are 1, 3, 3, 2, 1 and 0 for nights 1 to 6.
• Nights 2 and 3 have no free room in the pool of three.

Trade-offs and edge cases

The first choice is how to store a stay. One row per night works with a plain unique rule on room and night, the scalar case the PostgreSQL range types page calls natural for a unique constraint, but it multiplies rows and edits. One interval row per stay with an exclusion constraint handles any length.

Half-open intervals give same-day turnover with one row per stay, which inclusive end dates and night rows each give up on one side.

Text description of the diagram

• Inclusive end dates report same-day turnover as a conflict.
• Half-open intervals use one row per stay and an exclusion constraint.
• Night rows use a plain unique rule but need one row per night.

Other cases to name aloud:

• Room types. Guests usually book a type, not a number. The exclusion constraint protects only a specific room, and the per-night count above is only the availability read: counting and then inserting is the same check-then-insert race as Step 4, so two requests can both see one room free on the same night. Guard the pool with one inventory row per room type and night, holding total and reserved columns and CHECK (reserved <= total), and reserve with one statement, update inventory set reserved = reserved + 1 where type = :type and night >= :from and night < :to. The constraints documentation describes CHECK constraints, which reject a row that breaks the condition, so an oversell fails and the whole statement is rolled back. Under Read Committed, the transaction isolation page (section 13.2.1) says an update that meets a row being updated by a concurrent transaction waits for it, then re-evaluates its where clause on the updated row, so concurrent reservations queue up instead of both succeeding. Assigning a physical room later needs its own rule, because moving a guest between rooms must also respect the constraint. This is a design choice, not a standard.
• Duplicate submissions. A double-click or a client retry after a timeout sends the same booking twice, and the exclusion constraint does not catch a retry that asks for a different room or type. The client sends a unique request_id with each attempt, stored under a unique constraint on the reservation and written in the same transaction as the inventory update, so a repeat is rejected by the database and the service returns the first result.
• Cancellations. Delete the row or mark it cancelled and exclude cancelled rows from the constraint, so the nights become free again.
• Holds. A short hold while a guest pays is a booking with an expiry; the same idea appears in caches with expiry and time-based key-value stores. An expired hold still blocks the exclusion constraint until a job deletes or cancels it, because now() cannot appear in a constraint predicate, so run that cleanup on a schedule.
• Locks. A lock on the room row queues competing requests, but the constraint still protects a code path that forgets the lock.
• Time zones. Store dates in the hotel's local calendar; mixing zones moves check-out to another day.

For in-memory versions of this kind of problem, the CodeSignal in-memory database assessment covers a related idea, time-based expiry of records.

How to answer it in an interview

The spoken structure: name the interval, state the overlap test, explain why check-then-insert fails, put the rule in the database, then mention retries and extensions.

Common mistakes

• "I check availability, then insert the booking in a transaction." (wrong) Under the default Read Committed level, another transaction can commit between your check and your insert. (right) "The database constraint enforces it, and I handle the conflict error."
• "End dates are inclusive." (wrong) A guest leaving and a guest arriving the same day then clash. (right) "Stays are half-open, [check-in, check-out)."
• "Serializable means nothing can fail." (wrong) The PostgreSQL documentation says applications must be prepared to retry. (right) "Serializable can abort a transaction, so I retry it."
• "The overlap test is a.start <= b.end." (wrong) That reports touching stays as overlaps. (right) "I use strict less-than on both sides."

Follow-up questions

How do you prevent double booking in a hotel reservation system?

Make the database enforce it with an exclusion constraint on room and date range, so an overlapping insert fails whatever the application does. Then treat that failure as a normal answer to the guest.

Why not check availability in code and then insert the booking?

Two requests can run the check before either inserts, and both see the room free. That is a race condition, and in the data script's simulated interleaving it stores two rows for one room.¹

Does Serializable isolation remove the need for a constraint?

It can prevent the anomaly, but the documentation says applications must retry on serialization failures. A constraint states the rule directly, works at the default level, and costs no retry logic for correctness.

How do you model a room type with several identical rooms?

Count overlapping stays per night against the number of rooms of that type, as in the worked example, but only as the availability read. Enforce the limit with one inventory row per type and night, check (reserved <= total) and a single update that adds one to reserved, so concurrent requests queue on the row lock. Assign a physical room when the stay is confirmed or at check-in, depending on the hotel's policy.

What if the number of rooms changes or a guest cancels?

Keep room count as data, not a constant, and recompute free rooms from the bookings. A cancellation removes the stay from the active set, which frees its nights. Similar derived-state problems appear in spreadsheet and dependency engines and in event and log processing problems.

What if the guest clicks Book twice?

Send a unique request_id with every booking attempt and store it under a unique constraint on the reservation. The second submission hits that constraint, and the service returns the first result instead of creating a second stay, even when a retry names a different room.

Sources

1. PostgreSQL 18 Documentation, 8.17 Range Types — PostgreSQL Global Development Group, 13 Aug 2026
2. PostgreSQL 18 Documentation, 5.5 Constraints (Exclusion Constraints) — PostgreSQL Global Development Group, 13 Aug 2026
3. PostgreSQL 18 Documentation, 13.2 Transaction Isolation — PostgreSQL Global Development Group, 13 Aug 2026