Hotel Reservation System
A reservation is an entity, not a many-to-many between guest and room: it has a code of its own, its own dates, and payments that refer to it.
Intermediate
The diagram
Opening an example creates a copy in your own projects. The original example is never modified.
The problem
A small hotel with forty rooms wants to replace the paper diary kept at the reception. The receptionist records every booking with the guest's name and e-mail, the room assigned, the arrival and departure dates, and the code that is quoted on the phone and printed on the confirmation. A guest may book several times over the years, and a room is booked again and again, one stay after another. The hotel does not accept group bookings: one booking is always for one guest and one room, and a family that needs two rooms receives two bookings. Money is recorded separately, because most guests pay a deposit when they book and the balance when they leave; a booking that has been confirmed has therefore received at least one payment, and sometimes two or three. The hotel keeps only bookings that have reached this point: a phone enquiry that never turns into a deposit is not entered in the system at all. Finally, the reception must be able to answer the question "is this room free from the 3rd to the 10th of August?" without reading every booking in the diary.
Requirements
- The system must record guests, each with a unique guest number, a full name and an e-mail address.
- The system must record rooms, each with a unique room number, a room type and a nightly rate.
- The system must record reservations, each with a unique reservation code, an arrival date, a departure date, exactly one guest and exactly one room.
- A guest may have zero, one or many reservations, and the same guest may book the same room again in a different period.
- A room may appear in zero, one or many reservations, one after another, but never in two reservations whose periods overlap.
- The system must record payments, each with a unique payment number, an amount and a payment date, and each payment refers to exactly one reservation.
- Every reservation must have at least one payment and may have several; the payments of a reservation are summed to check that the stay has been paid in full.
- The system must be able to list the reservations of a room in a given period, so that the reception can answer availability questions.
Entities and attributes
| Entity | Attributes | Identifier |
|---|---|---|
| Guest | guest_no, full_name, email | guest_no |
| Room | room_no, room_type, nightly_rate | room_no |
| Reservation | reservation_code, check_in, check_out | reservation_code |
| Payment | payment_no, amount, paid_at | payment_no |
Relations and cardinalities
| Relation | From | To | Cardinality (min, max) | Note |
|---|---|---|---|---|
| Books | Guest | Reservation | (0,N) – (1,1) |
A guest takes part in zero, one or many reservations (0,N); each reservation involves exactly one guest (1,1), so the guest is reachable from the reservation without repeating the name. |
| Reserves | Room | Reservation | (0,N) – (1,1) |
A room appears in zero, one or many reservations over time (0,N); each reservation is for exactly one room (1,1). The tag says nothing about the period: the overlap rule is not expressible here. |
| SettledBy | Reservation | Payment | (1,N) – (1,1) |
A reservation is settled by at least one and possibly several payments (1,N) - deposit, balance, extras - while each payment refers to exactly one reservation (1,1). |
Reading the diagram
- The diagram has four entities. Guest is identified by guest_no, Room by room_no, Reservation by reservation_code and Payment by payment_no. An identifier is the attribute the business itself uses to name an instance: the receptionist says "reservation 4711", not "the third booking of Mr Rossi".
- Reservation is the interesting part of this example. The first instinct is to draw a many-to-many Books between Guest and Room, with arrival and departure written on the diamond: a guest books many rooms, a room is booked by many guests. Those cardinalities are not wrong, but the diagram cannot say what the hotel actually needs. A reservation is a thing the hotel talks about, it has a code of its own, and the code must still exist if the guest moves to a different room or shortens the stay. An association has no identifier: the only way to name an instance of Books would be to name the guest and the room, and both can change.
- For the same reason the pair (room_no, check_in) cannot serve as the identifier. It is unique only under the rule "a room is never reserved twice in overlapping periods", and that rule is not part of the E-R model: the model compares values, not intervals. A designer who built the key on (room_no, check_in) would be trusting a constraint that nothing in the schema enforces, and would discover the problem only when the first double booking arrived. The code avoids the trap: it is generated once, when the booking is taken, and never depends on dates or rooms.
- Books and Reserves are the two one-to-many relationships that replace the many-to-many. Reading the tags: (0,N) on the Guest side of Books means that a guest takes part in zero or more bookings, and (1,1) on the Reservation side means that each reservation involves exactly one guest. Reserves reads the same way for rooms: a room takes part in zero or more reservations over time, and each reservation is for exactly one room. Neither tag forbids two reservations of the same room in the same week; the model has no way to say it.
- SettledBy shows why Payment deserves its own entity. The tag (1,N) on the Reservation side says that a reservation is settled by at least one and possibly many payments - typically a deposit and a balance - and the tag (1,1) on the Payment side says that a payment is never shared between two reservations. If amount and paid_at were attributes of Reservation, the hotel could record only one payment per booking, the deposit would overwrite nothing and the sum would have to be recomputed by hand.
- The minimum 1 on the reservation side is a business rule, not a formality. It says that the database contains no reservation that has received no money: an unpaid enquiry stays outside the system. Lowering that minimum to 0 would change the meaning of the whole example, because Reservation would then also represent quotations and options, and the reception would have to distinguish them by looking at the payments.
- What the diagram cannot say is written in the assumptions and must be enforced elsewhere: a room is never reserved twice in overlapping periods, check_out must be later than check_in, and the payments must eventually cover the price of the stay. None of these is a cardinality; they are constraints on values and on pairs of rows, and they belong to the logical schema (CHECK), to a trigger or to the application. Stating that boundary explicitly is part of the design, not a weakness of it.
From the conceptual model to the logical schema
Guest(guest_no, full_name, email) PRIMARY KEY (guest_no) Room(room_no, room_type, nightly_rate) PRIMARY KEY (room_no) Reservation(reservation_code, check_in, check_out, guest_no, room_no) PRIMARY KEY (reservation_code) FOREIGN KEY (guest_no) REFERENCES Guest(guest_no) FOREIGN KEY (room_no) REFERENCES Room(room_no) CHECK (check_out > check_in) Payment(payment_no, amount, paid_at, reservation_code) PRIMARY KEY (payment_no) FOREIGN KEY (reservation_code) REFERENCES Reservation(reservation_code)