ER diagram for hotel management system
This ER diagram models a hotel management system with five tables: **hotels**, **rooms**, **guests**, **bookings** and **payments**. It shows how a hotel tracks which guest booked which room for which dates, and how each booking is paid. It suits students on DBMS projects and developers starting a booking app.
Example: ER diagram for hotel management system: click to open it in the editor and change it.
Entities and key attributes
| Entity | Primary key | Key attributes |
|---|---|---|
| hotels | id | name, city, rating |
| rooms | id | hotel_id (FK), number, type, rate |
| guests | id | name, email, phone |
| bookings | id | room_id (FK), guest_id (FK), check_in_date, check_out_date, status |
| payments | id | booking_id (FK), amount, paid_at |
Relationships and cardinality
- rooms → hotels (N:1): a hotel has many rooms, linked by rooms.hotel_id.
- bookings → rooms (N:1): a room is booked many times over time, linked by bookings.room_id.
- bookings → guests (N:1): a guest can make many bookings, linked by bookings.guest_id.
- payments → bookings (N:1): a booking can have one or more payments, for example a deposit and the balance.
bookings is the central table: it connects guests to rooms and holds the dates and status. Checking for overlapping check_in_date and check_out_date values on the same room tells you whether it is free.
How to adapt it for your project
- Add a room_types table if many rooms share the same type and rate.
- Add staff and services (room service, laundry) tables with a booking_services link table.
- Add reviews linked to guests and hotels.
Tip: Store the rate on the booking as well, so later price changes don't alter old bookings.
Frequently asked questions
What are the entities in a hotel management system ER diagram?
Here: hotels, rooms, guests, bookings and payments. Larger designs add room types, staff, services and reviews.
What is the relationship between guests and rooms?
Many-to-many, resolved through the bookings table: a guest books many rooms over time, and a room is booked by many guests.
How do I check room availability?
Look for bookings of that room whose check-in and check-out dates overlap the requested dates. If there are none, the room is free.
Make your own ER diagram with AI →