Yuki Tanaka runs the front office of a 30-room hotel with a board that says which rooms are occupied and which are vacant, and about once a week somebody gives away a room that is not free. The board is the bug. This post designs the hotel's database from a conversation with her, eight tables and one view, built and loaded on SQL Server and PostgreSQL, with the one rule that matters most in a hotel enforced by the database itself: two reservations for the same room can never overlap. The refusals, the five daily questions and the board's wrong answers are all quoted from the runs.
- Download the script for your engine: p07-sqlserver.sql or p07-postgresql.sql
- Run it:
sqlcmd -S "(localdb)\MSSQLLocalDB" -I -f 65001 -i p07-sqlserver.sql, orpsql -d postgres -c "CREATE DATABASE sp_p07"thenpsql -d sp_p07 -e -f p07-postgresql.sql - Find the overlap rule in section 2: an
EXCLUDEconstraint on PostgreSQL, a trigger on SQL Server - Read section 3b in your output: the overlapping booking refused, the back-to-back one accepted, the extension refused
- Check 4.4: Liam O'Brien's bill ends with a balance due of 8370.00 on both engines
- Check 5.3: the typed board disagrees with the reservations on three rooms
- Paste section 2 into the ER diagram tool and p07-sheet.csv into the normalization checker
The conversation
Yuki: We keep a board that says which rooms are occupied and which are vacant. The receptionist types the status in when a guest arrives or leaves. When the desk is busy, it waits. Today the board said 102 was occupied, but Fatima Al-Sayed had left at 10:40.
Designer: So the board repeats something the stays already know, late. Tell me about the rooms.
Yuki: Every room has a number and a floor. There are three kinds: single, double and suite. A single sleeps one, a double two, a suite up to four.
Designer: Does the price belong to the room or to the kind of room?
Yuki: To the kind. All doubles cost the same per night. But the price changes with the season: a double is 3800 a night until the end of October and 4800 from the first of November.
Designer: If a guest stays across that change, what does the bill say?
Yuki: Each night is charged at the rate of that night. Liam O'Brien came on 30 October and leaves on 2 November: two nights at 3800 and one at 4800.
Designer: Then a stay is a list of nights, and each night finds its own rate. How does a booking reach you?
Yuki: A guest phones or writes and asks for a room from one date to another. We note the name, phone, email and address, the dates and how many adults. One booking is one room. Some guests come back; David Chen stayed in October and is booked again for November.
Designer: Then the guest is written down once and every stay points to them. What must never happen between two bookings?
Yuki: Two guests in the same room on the same night. But one guest can leave in the morning and the next arrive that afternoon: Aisha Khan takes 201 on the day Liam leaves it.
Designer: So a booking holds the nights from the check-in date up to the night before the check-out date, and the check-out day is free for the next guest. And on the day itself?
Yuki: At check-in we write down the time the guest actually arrives, and at check-out the time they leave. Kwame Mensah is booked for today and has not arrived yet. Then there is laundry, room service, an airport transfer, an extra bed, each with a usual price. And payments: a deposit when they book or arrive, the rest at check-out. At check-out I want one number, what they still owe.
Designer: "Occupied" is a stay with an arrival time and no departure time yet; "arriving today" is a booking that starts today with no arrival time. Both are read from the reservations. The balance is room nights plus services minus payments, worked out from the rows. Nothing on the board needs typing, so the board can go.
From sentences to tables
| Yuki said | Entity | Columns |
|---|---|---|
| three kinds, each sleeps a number of guests | room_types | name, max_guests |
| the price belongs to the kind and changes with the season | rate_seasons | room_type_id, season_name, start_date, end_date, nightly_rate |
| every room has a number and a floor | rooms | room_no, floor_no, room_type_id |
| name, phone, email, address; some come back | guests | written once; every stay points at the guest |
| a room from one date to another, for so many adults; arrival and departure times | reservations | guest_id, room_id, check_in, check_out, adults, checked_in_at, checked_out_at |
| laundry, room service, each with a usual price, charged to the stay | services, reservation_services | the charge keeps service_date, quantity and the unit_price actually charged |
| a deposit, then the rest | payments | reservation_id, paid_at, amount, method |
| each night at the rate of that night | view reservation_nights | one row per night with its season's rate, counted, not stored |
| occupied, vacant, what they owe | nothing | derived from reservations and payments |
The rule the board could not keep
On PostgreSQL the whole rule is one line inside the table. It needs the btree_gist
extension, which the script creates, and it reads: for the same room, two date ranges must not
overlap.
CREATE TABLE reservations (
reservation_id int GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_reservations PRIMARY KEY,
guest_id int NOT NULL CONSTRAINT fk_reservations_guests REFERENCES guests (guest_id),
room_id int NOT NULL CONSTRAINT fk_reservations_rooms REFERENCES rooms (room_id),
check_in date NOT NULL,
check_out date NOT NULL,
adults smallint NOT NULL CONSTRAINT df_reservations_adults DEFAULT 1,
checked_in_at timestamp(0) NULL,
checked_out_at timestamp(0) NULL,
CONSTRAINT ck_reservations_dates CHECK (check_out > check_in),
CONSTRAINT ck_reservations_adults CHECK (adults >= 1),
CONSTRAINT ex_reservations_no_overlap EXCLUDE USING gist (room_id WITH =, daterange(check_in, check_out) WITH &&)
);daterange(check_in, check_out) with two arguments is half-open: it holds the check-in
night and stops before the check-out day, which is exactly Yuki's "one leaves in the morning, the
next arrives that afternoon". SQL Server has no exclusion constraint, so the same rule is a
trigger that looks for a clashing row after every insert or update and throws it back:
SELECT TOP (1) @msg = CONCAT('Room ', rm.room_no, ' is already reserved from ', r.check_in,
' to ', r.check_out, ' (reservation ', r.reservation_id, ').')
FROM inserted AS i
JOIN reservations AS r
ON r.room_id = i.room_id
AND r.reservation_id <> i.reservation_id
AND r.check_in < i.check_out
AND r.check_out > i.check_in
JOIN rooms AS rm ON rm.room_id = r.room_id
ORDER BY r.check_in;
IF @msg IS NOT NULL
BEGIN
ROLLBACK TRANSACTION;
THROW 50001, @msg, 1;
END;Section 3b tests both. A second booking for room 201 from 1 to 4 November, while Liam O'Brien holds it from 30 October to 2 November, is refused on both engines. PostgreSQL names the constraint and shows both ranges:
ERROR: conflicting key value violates exclusion constraint "ex_reservations_no_overlap"
DETAIL: Key (room_id, daterange(check_in, check_out))=(5, [2026-11-01,2026-11-04)) conflicts with existing key (room_id, daterange(check_in, check_out))=(5, [2026-10-30,2026-11-02)).SQL Server raises error 50001 from the trigger with the message the trigger built:
Room 201 is already reserved from 2026-10-30 to 2026-11-02 (reservation 6).Then Aisha Khan's booking for the same room from 2 November, the day Liam leaves, is accepted on both. And a third case the conversation did not ask for: extending Liam's stay by one night now collides with Aisha's booking and is refused too, which shows the rule covers updates as well as inserts. One small thing to notice in the listing afterwards: both engines skipped id 14, because the refused insert consumed it. Identity values are not a row count.
reservation_id | full_name | check_in | check_out
----------------+--------------+------------+------------
1 | Maria Garcia | 2026-10-03 | 2026-10-06
6 | Liam O'Brien | 2026-10-30 | 2026-11-02
15 | Aisha Khan | 2026-11-02 | 2026-11-05
(3 rows)Each night at its own rate
"Each night is charged at the rate of that night" is the sentence that decides the bill. The script answers it with a view that expands a stay into its nights and joins each night to the season it falls in:
CREATE VIEW reservation_nights AS
SELECT r.reservation_id,
r.room_id,
r.check_in + g.n AS night,
s.season_name,
s.nightly_rate
FROM reservations AS r
JOIN rooms AS rm ON rm.room_id = r.room_id
CROSS JOIN LATERAL generate_series(0, r.check_out - r.check_in - 1) AS g(n)
JOIN rate_seasons AS s
ON s.room_type_id = rm.room_type_id
AND r.check_in + g.n BETWEEN s.start_date AND s.end_date;
SQL Server 2022 has GENERATE_SERIES too, through CROSS APPLY with
DATEADD for the arithmetic, as long as the database runs at compatibility level
160; the script checks that first. The bill and the revenue questions both read this view, so
the rate of a night is decided in one place.
The ER diagram
Five questions the front desk asks
The sample loads 12 of the 30 rooms and fixes "today" at 2 November 2026 so that every answer is
the same on every run. Which rooms are free from the 5th to the 8th: the same overlap test the
constraint uses, turned into a NOT EXISTS.
SELECT rm.room_no, rt.name AS room_type
FROM rooms AS rm
JOIN room_types AS rt ON rt.room_type_id = rm.room_type_id
WHERE NOT EXISTS (
SELECT 1
FROM reservations AS r
WHERE r.room_id = rm.room_id
AND daterange(r.check_in, r.check_out) && daterange(DATE '2026-11-05', DATE '2026-11-08')
)
ORDER BY rm.room_no;
room_no | room_type
---------+-----------
102 | Single
103 | Single
104 | Single
201 | Double
202 | Double
205 | Double
301 | Suite
302 | Suite
(8 rows)Room 201 is in the list although Aisha's stay ends on the 5th, because a check-out day is not a reserved night. SQL Server writes the test as two date comparisons and returns the same eight. Today's arrivals and departures, with the time if it has happened:
movement | room_no | full_name | done_at
-----------+---------+-----------------+---------------------
arrival | 201 | Aisha Khan |
arrival | 203 | Elena Rossi | 2026-11-02 13:10:00
arrival | 301 | Kwame Mensah |
departure | 102 | Fatima Al-Sayed | 2026-11-02 10:40:00
departure | 201 | Liam O'Brien |
(5 rows)Occupancy per night for the week, from the nights view against the room count:
night | rooms_reserved | rooms_total | occupancy_pct
------------+----------------+-------------+---------------
2026-11-02 | 5 | 12 | 41.7
2026-11-03 | 5 | 12 | 41.7
2026-11-04 | 4 | 12 | 33.3
2026-11-05 | 4 | 12 | 33.3
2026-11-06 | 2 | 12 | 16.7
2026-11-07 | 2 | 12 | 16.7
2026-11-08 | 1 | 12 | 8.3
(7 rows)The bill of reservation 6, Liam O'Brien's three nights across the season change, with his services and his deposit. This is the one number Yuki asked for, and it is a query, not a column:
item | amount
-----------------------------------------+----------
Room 201, Regular, 2 night(s) x 3800.00 | 7600.00
Room 201, Peak, 1 night(s) x 4800.00 | 4800.00
Laundry x 4 | 320.00
Room service x 1 | 650.00
Payment, card, 2026-10-30 | -5000.00
Balance due | 8370.00
(6 rows)And room revenue per type for October, counted by the night's date, so a stay that crosses into November contributes only its October nights:
room_type | room_nights | room_revenue
-----------+-------------+--------------
Double | 9 | 34200.00
Suite | 4 | 28000.00
Single | 6 | 15000.00
(3 rows)The board, and why it was wrong three times
Section 5 rebuilds the receptionist's board as a table, rooms_with_status, exactly as
it stood at 14:00 on 2 November, and asks it the walk-in question: which rooms can I give a guest
tonight?
room_no | room_type | status
---------+-----------+--------
101 | Single | vacant
104 | Single | vacant
202 | Double | vacant
203 | Double | vacant
204 | Double | vacant
205 | Double | vacant
301 | Suite | vacant
303 | Suite | vacant
(8 rows)It offers 203, where Elena Rossi checked in at 13:10, and 301, which Kwame Mensah arrives for tonight, and it misses 102, free since 10:40. The same question put to the reservations gives seven rooms, the right seven. And a query that compares the two, row by row, shows every place the board lied:
room_no | typed_status | derived_status
---------+--------------+----------------
102 | occupied | vacant
203 | vacant | occupied
301 | vacant | arriving today
(3 rows)
The board has no dates either, so "free from the 5th to the 8th" cannot even be asked of it. A
status column that a person types is a cache with no refresh; derive it. The receptionist's
spreadsheet of the same board, with a services column holding lists like "Laundry,
Room service", is what the normalization checker sees below.
services and the facts that repeat with the room type; the typed status is a design mistake it cannot see, because nothing in the values gives it away. Real screenshot of the tool on this site.What this design does not do
Cancellations and no-shows are not modelled; Yuki did not raise them, and they would add a status
to the reservation, this time a real one, with the date it changed. Two rules are also left to the
application: two seasons of one room type must not overlap, or a night would be priced twice, and
adults is not checked against the type's max_guests. Neither was tested.
And the SQL Server trigger was not tried with two sessions booking the same room at the same
instant; the PostgreSQL constraint is backed by an index and holds under that too.
The three habits this project teaches: a stay is a range of nights, half-open; the rule that two stays cannot share a room belongs in the database, not on a board; and the price of a night belongs to the night, which a view can work out every time.
Frequently asked
- How do you prevent double booking of a hotel room in the database?
- On PostgreSQL with an exclusion constraint: EXCLUDE USING gist (room_id WITH =, daterange(check_in, check_out) WITH &&) after CREATE EXTENSION btree_gist. The half-open range lets the next guest check in on the check-out day. SQL Server has no exclusion constraint, so an AFTER INSERT, UPDATE trigger looks for a clashing reservation and throws; both refuse the overlap and both accept the back-to-back booking in this post's run.
- What tables does a hotel management system database need?
- Eight here: room_types, rate_seasons (the nightly rate per type per season), rooms, guests, reservations (one room, a date range, arrival and departure times), services, reservation_services (the charges, with date, quantity and the price actually charged) and payments. Occupancy, the bill and the balance are queries; a view that expands a stay into its nights gives each night its own rate.
- Why is a room status column a mistake in a hotel database?
- Because somebody has to type it, and when the desk is busy it waits. In this post's sample the board was wrong for three of twelve rooms at 14:00: a room marked occupied whose guest had left, a room marked vacant whose guest had checked in, and a room with an arrival due that the board could not express. The status derived from reservations was right for all twelve.
Next: Part 8, a railway reservation system: routes as ordered stops, seats that cannot be given twice for the same date, fares, bookings and a waitlist.