Hotel Booking Database Schema database schema

What this schema models

A hotel reservation and property management database schema that gets the two decisions right which make or break every booking system. 16 tables, 13 enums and 23 relationships covering properties, room types, amenities, physical rooms, rate plans, cancellation policies, a per-night rate and availability calendar, guests, bookings, payments, housekeeping and verified reviews. DECISION 1 — AVAILABILITY IS PER ROOM TYPE PER NIGHT Guests book a room TYPE, not a specific door. A specific room is assigned at check-in via booking_rooms.assigned_room_id, which stays null until then. Pinning a guest to room 402 six months in advance is exactly what causes the endless reshuffles that hotel systems are notorious for. rate_calendar holds one row per room type per rate plan per night, carrying both rooms_available and rate_cents, so a single indexed table answers both "what is free" and "what does it cost" — which is why availability search stays fast. DECISION 2 — A STAY IS PRICED AS THE SUM OF ITS NIGHTS booking_room_nights stores one row per night with the rate frozen at booking time. Rates move daily; a confirmed booking must not. Totals on bookings are snapshots rolled up from those rows, so any later change to rate_calendar can never rewrite what a guest was quoted. OTHER DETAILS WORTH STEALING - Dates are LOCAL calendar dates, not UTC instants, which is why properties carries a timezone alongside check_in_time and check_out_time. - Non-refundable at a lower price is a rate plan, not a discount — and it carries its own cancellation policy with an hours threshold and a penalty type. - One booking, many rooms: a family taking two rooms is one reservation with two booking_rooms rows. - booking_guests records who actually occupies each room, which many jurisdictions legally require a hotel to capture, separately from whoever paid. - Deposits, balances, incidentals and refunds are all separate rows in payments distinguished by type. Never net them into one figure. - Housekeeping closes the loop: a room is not sellable again until its departure clean is completed. - reviews is unique on booking_id, so only a real completed stay can leave one. That single constraint is the entire anti-fake-review mechanism. - Money in minor units as integers. Never floats. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded by zone — property and rooms, rates and availability, guests, bookings, money, operations and trust — with sticky notes explaining each decision, and every table carrying its own Note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building a hotel PMS, booking engine or channel manager, students needing a realistic hotel reservation ER diagram, and anyone modelling date-range inventory for the first time.

Tables (16)

Relationships (23)

• booking_guests.booking_room_id references booking_rooms.id many to one
• booking_guests.guest_id references guests.id many to one
• booking_room_nights.booking_room_id references booking_rooms.id many to one
• booking_rooms.assigned_room_id references rooms.id many to one
• booking_rooms.booking_id references bookings.id many to one
• booking_rooms.rate_plan_id references rate_plans.id many to one
• booking_rooms.room_type_id references room_types.id many to one
• bookings.primary_guest_id references guests.id many to one
• bookings.property_id references properties.id many to one
• cancellation_policies.property_id references properties.id many to one
• housekeeping_tasks.room_id references rooms.id many to one
• payments.booking_id references bookings.id many to one
• rate_calendar.rate_plan_id references rate_plans.id many to one
• rate_calendar.room_type_id references room_types.id many to one
• rate_plans.cancellation_policy_id references cancellation_policies.id many to one
• rate_plans.property_id references properties.id many to one
• reviews.booking_id references bookings.id many to one
• reviews.guest_id references guests.id many to one
• reviews.property_id references properties.id many to one
• room_type_amenities.amenity_id references amenities.id many to one
• room_type_amenities.room_type_id references room_types.id many to one
• room_types.property_id references properties.id many to one
• rooms.room_type_id references room_types.id many to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the Hotel Booking Database Schema schema?

16 tables and 23 relationships, across 134 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.