MySQL Hotel Booking Database: Rooms and Date Ranges

Project goal: a hotel that must never sell the same room for the same night twice. The new idea compared with the clinic project is working with date ranges: a stay from the check-in day up to (not including) the check-out day.

Requirements

Rooms have a number, a type and a price per night. Guests have a name and an email.
A booking reserves one room for a range of nights, from a check-in date to a check-out date. The guest leaves on the check-out day, so that night is not used.
A room cannot have two confirmed bookings that share a night. A guest checking out on the 12th and another checking in on the 12th is fine.
Reports: available rooms for given dates, the cost of a stay, occupancy per night, and revenue by room type.

Tables and relationships

Table	Relationship
`rooms` 1 — N `bookings`	A booking is for one room
`guests` 1 — N `bookings`	A booking is made by one guest

The schema

query.sql

SQL

CREATE TABLE rooms (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    number CHAR(4) NOT NULL,
    type ENUM('single', 'double', 'suite') NOT NULL,
    price_per_night DECIMAL(8,2) NOT NULL,
    UNIQUE KEY uq_rooms_number (number),
    CONSTRAINT chk_rooms_price CHECK (price_per_night > 0)
);

CREATE TABLE guests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_guests_email (email)
);

CREATE TABLE bookings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_id INT UNSIGNED NOT NULL,
    guest_id INT UNSIGNED NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    status ENUM('confirmed', 'cancelled') NOT NULL DEFAULT 'confirmed',
    CONSTRAINT chk_booking_dates CHECK (check_out > check_in),
    CONSTRAINT fk_bookings_room FOREIGN KEY (room_id) REFERENCES rooms (id),
    CONSTRAINT fk_bookings_guest FOREIGN KEY (guest_id) REFERENCES guests (id),
    KEY idx_bookings_room_dates (room_id, check_in, check_out),
    KEY idx_bookings_guest (guest_id)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

The no-double-booking rule

A stay uses the nights from check_in up to but not including check_out: a half-open range [check_in, check_out). Two stays overlap when each starts before the other ends:

query.sql

SQL

DELIMITER //

CREATE TRIGGER trg_bookings_no_overlap_insert BEFORE INSERT ON bookings FOR EACH ROW
BEGIN
    IF NEW.status = 'confirmed' AND EXISTS (
        SELECT 1 FROM bookings b
        WHERE b.room_id = NEW.room_id AND b.status = 'confirmed'
          AND b.check_in < NEW.check_out AND NEW.check_in < b.check_out
    ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Room is already booked for some of these nights';
    END IF;
END//

DELIMITER ;

Output

OUTPUT

Query OK, 0 rows affected

(An UPDATE trigger with the same check, as in the clinic project, is needed when bookings can be moved. As there, a busy system also locks the room row in the same transaction before inserting, so that two requests for the same room are handled one after the other.)

Sample data

query.sql

SQL

INSERT INTO rooms (number, type, price_per_night) VALUES ('101', 'single', 60.00), ('102', 'double', 90.00), ('201', 'double', 95.00), ('301', 'suite', 200.00);
INSERT INTO guests (name, email) VALUES ('Maya', 'maya@example.com'), ('Rohan', 'rohan@example.com'), ('Isha', 'isha@example.com'), ('Kabir', 'kabir@example.com');

INSERT INTO bookings (room_id, guest_id, check_in, check_out) VALUES
(1, 1, '2026-03-09', '2026-03-12'),
(1, 2, '2026-03-12', '2026-03-14'),
(2, 3, '2026-03-10', '2026-03-13'),
(3, 4, '2026-03-11', '2026-03-12'),
(4, 1, '2026-03-13', '2026-03-16');
INSERT INTO bookings (room_id, guest_id, check_in, check_out, status) VALUES (2, 4, '2026-03-12', '2026-03-15', 'cancelled');

Output

OUTPUT

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 1 row affected

Room 101 has two stays that touch on 12 March (one checks out and the next checks in). The trigger accepted that. The cancelled booking in room 102 does not block anything. Now a real conflict:

query.sql

SQL

INSERT INTO bookings (room_id, guest_id, check_in, check_out) VALUES (1, 3, '2026-03-11', '2026-03-13');

Output

OUTPUT

ERROR 1644 (45000): Room is already booked for some of these nights

Room 101 is taken on the nights of the 11th and the 12th. The database refused.

Query 1: available rooms for a stay

A room is available when no confirmed booking overlaps the wanted dates:

query.sql

SQL

SELECT r.number, r.type, r.price_per_night
FROM rooms r
WHERE NOT EXISTS (
    SELECT 1 FROM bookings b
    WHERE b.room_id = r.id AND b.status = 'confirmed'
      AND b.check_in < '2026-03-13' AND '2026-03-10' < b.check_out
)
ORDER BY r.price_per_night;

Output

OUTPUT

+--------+-------+-----------------+
| number | type  | price_per_night |
+--------+-------+-----------------+
| 301    | suite |          200.00 |
+--------+-------+-----------------+
1 row in set

This looks for stays from 10 to 13 March (nights of the 10th, 11th and 12th). Room 101 is used on the 10th and 11th, 102 on all three, and 201 on the 11th. Only the suite is free. The anti-join NOT EXISTS uses the index idx_bookings_room_dates (Subqueries).

Query 2: the price of a stay

query.sql

SQL

SELECT g.name, r.number, b.check_in, b.check_out, DATEDIFF(b.check_out, b.check_in) AS nights, DATEDIFF(b.check_out, b.check_in) * r.price_per_night AS total
FROM bookings b
JOIN rooms r ON r.id = b.room_id
JOIN guests g ON g.id = b.guest_id
WHERE b.status = 'confirmed'
ORDER BY b.check_in, r.number;

Output

OUTPUT

+-------+--------+------------+------------+--------+--------+
| name  | number | check_in   | check_out  | nights | total  |
+-------+--------+------------+------------+--------+--------+
| Maya  | 101    | 2026-03-09 | 2026-03-12 |      3 | 180.00 |
| Isha  | 102    | 2026-03-10 | 2026-03-13 |      3 | 270.00 |
| Kabir | 201    | 2026-03-11 | 2026-03-12 |      1 |  95.00 |
| Rohan | 101    | 2026-03-12 | 2026-03-14 |      2 | 120.00 |
| Maya  | 301    | 2026-03-13 | 2026-03-16 |      3 | 600.00 |
+-------+--------+------------+------------+--------+--------+
5 rows in set

DATEDIFF(check_out, check_in) is the number of nights. The price is computed at query time here. In a real system, also store the price on the booking when it is made, because room prices change (Denormalization).

Query 3: occupancy per night

A report by night needs one row per night, including nights with no booking. Generate the calendar with a recursive CTE, and join the bookings:

query.sql

SQL

WITH RECURSIVE nights AS (
    SELECT DATE '2026-03-09' AS night
    UNION ALL
    SELECT night + INTERVAL 1 DAY FROM nights WHERE night < '2026-03-16'
)
SELECT n.night,
       COUNT(b.id) AS rooms_occupied,
       ROUND(COUNT(b.id) / (SELECT COUNT(*) FROM rooms) * 100) AS occupancy_pct
FROM nights n
LEFT JOIN bookings b ON b.status = 'confirmed' AND n.night >= b.check_in AND n.night < b.check_out
GROUP BY n.night
ORDER BY n.night;

Output

OUTPUT

+------------+----------------+---------------+
| night      | rooms_occupied | occupancy_pct |
+------------+----------------+---------------+
| 2026-03-09 |              1 |            25 |
| 2026-03-10 |              2 |            50 |
| 2026-03-11 |              3 |            75 |
| 2026-03-12 |              2 |            50 |
| 2026-03-13 |              2 |            50 |
| 2026-03-14 |              1 |            25 |
| 2026-03-15 |              1 |            25 |
| 2026-03-16 |              0 |             0 |
+------------+----------------+---------------+
8 rows in set

The condition n.night >= b.check_in AND n.night < b.check_out says “this night belongs to the stay”. The LEFT JOIN keeps nights with nobody in the hotel (the 16th, when the last guest has left, has 0 occupied rooms) (Recursive CTEs).

Query 4: revenue by room type

query.sql

SQL

SELECT r.type, COUNT(*) AS bookings, SUM(DATEDIFF(b.check_out, b.check_in)) AS nights, SUM(DATEDIFF(b.check_out, b.check_in) * r.price_per_night) AS revenue
FROM bookings b JOIN rooms r ON r.id = b.room_id
WHERE b.status = 'confirmed'
GROUP BY r.type
ORDER BY revenue DESC;

Output

OUTPUT

+--------+----------+--------+---------+
| type   | bookings | nights | revenue |
+--------+----------+--------+---------+
| suite  |        1 |      3 |  600.00 |
| double |        2 |      4 |  365.00 |
| single |        2 |      5 |  300.00 |
+--------+----------+--------+---------+
3 rows in set

Common mistakes

Treating the check-out day as an occupied night. Then back-to-back stays conflict, and revenue is overcounted by one night per stay. Use a half-open range and < for the end.
Using BETWEEN for stays: it includes both ends.
Overlap logic with the wrong comparison. The test is existing.start < new.end AND new.start < existing.end. Memorise it.
Checking availability and booking in two separate steps without a lock. Two guests can both see the room as free.
No index on (room_id, check_in, check_out). The availability query scans the bookings table.
Deleting cancelled bookings. Reports on cancellations, and refunds, need them.
Hotels in different time zones with DATETIME check-ins. For whole nights, a DATE is correct and simpler.

Final result

query.sql

SQL

SELECT status, COUNT(*) AS bookings, SUM(DATEDIFF(check_out, check_in)) AS nights FROM bookings GROUP BY status ORDER BY status;

Output

OUTPUT

+-----------+----------+--------+
| status    | bookings | nights |
+-----------+----------+--------+
| confirmed |        5 |     12 |
| cancelled |        1 |      3 |
+-----------+----------+--------+
2 rows in set

Next steps: seasonal prices (a price table with valid_from and valid_to, as in Price History), a payments table, room features, and a minimum-stay rule in the trigger.