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 results

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 content, and keep the same structure if it fits your project.

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

Edit this hotel schema template