Hotel Management Database Design Examples

These hotel schema examples show how the same room-booking-payment core scales from one property to a chain, integrates with booking channels, and handles resort-style add-on services.

Edit this hotel schema template Back to template

Hotel Management Database Design Examples

Real examples

Single property (the baseline)

Who uses it: Developer building a first hotel reservation system

RoomType (id, name, base_rate, capacity)

Room (id, room_type_id, number, floor, status)

Booking (id, guest_id, room_id, check_in, check_out, status)

Guest (id, name, email, phone)

Availability query: rooms with no booking overlapping the date range

Why this works: The single-property schema's key decision is separating RoomType from Room — rates and capacity live on the type, while individual rooms carry their own number and status, which keeps pricing and inventory cleanly apart.

Multi-property chain

Who uses it: Team running several hotels on one platform

Property (id, name, address) added at the top

RoomType and Room both reference a property

Bookings scoped to a property for reporting

Rates can vary per property even for the same room-type name

Central guest profile shared across properties

Why this works: A chain adds a Property entity that rooms and room types belong to — the design choice is whether guest profiles are shared centrally or per-property, with shared profiles enabling cross-property loyalty and history.

Channel-managed (OTA integration)

Who uses it: Team syncing with Booking.com, Expedia, etc.

Channel (id, name) and a RatePlan per channel

Booking gains a channel_id and an external_ref

Inventory allotment per channel to avoid overbooking

Rate parity tracked across channels

Sync log table for reconciling external bookings

Why this works: Channel management adds the source of each booking and per-channel rate plans — the diagram's new job is preventing overbooking when multiple OTAs sell the same physical rooms, which the allotment relationship models.

Resort with services

Who uses it: Team where add-on revenue matters as much as room nights

Service (id, name, price) catalog of add-ons

BookingService junction links bookings to services with quantity

Folio (running bill) aggregates room + services per booking

Payment can be partial against the folio total

Service usage logged with timestamps for billing

Why this works: Resorts make services first-class revenue — the diagram adds a folio that sums room charges and service charges, so a single booking accumulates a running bill rather than just a fixed room rate.

Tips for better study mind maps

Separate RoomType from Room — rates and capacity belong on the type, while status and number belong on the individual room.
Model bookings with explicit check_in and check_out dates so an availability query can detect overlaps.
Store the rate on the Booking at booking time; base rates change, but a confirmed booking must keep its agreed price.
Attach payments and services to the Booking, not the Guest, so a booking's full cost is reconstructable.

Start editing online

Go back to the template, swap in your own topics, and keep the same structure if it fits your class or project.

Use this template: /editor/new?template=hotel-management-database-design

Edit this hotel schema template