ChatDiagram
Try for free
data warehouse · hospitality · bookings

Hotel booking snowflake schema

This hotel booking snowflake schema records each stay in FactStay with its nights and room revenue. The fact links to check-in date, guest and room. Guest details connect to country, while room details connect through room type to property. These separate levels make it possible to report revenue by property, room type, guest country or arrival period without repeating descriptive values throughout the fact table. The model is useful for hospitality groups that maintain shared property and room-type hierarchies across locations. It also keeps local room details separate from the cross-property categories used for management reporting. This improves consistency when properties share a central reporting model across a portfolio.

Type Entity-Relationship Diagram (ERD)Standard Chen 1976 / Everest 1976 (crow's foot)Engine schematex-erdUpdated 9/24/2026
Hotel Booking Snowflake Schema
Drawing preview
The request

“A hotel booking snowflake schema: FactStay linked to Date, Guest, and Room; normalize Guest through Country and Room through Room Type to Property.”

Then tryAdd a booking channel dimensionAdd a rate plan below Room Type
What is in this drawing

Read the decisions behind it.

01Stay grain

Store one row per guest stay.

02Guest geography

Keep country outside the guest row.

03Room hierarchy

Separate room, type and property.

Use this for occupancy and revenue reporting across multiple properties.

More like this

Other examples in this family.

Loading drawing…
Healthcare claims snowflake schemadata warehouseOpen →
Loading drawing…
Retail sales snowflake schemadata warehouseOpen →
Loading drawing…
Supply chain snowflake schemadata warehouseOpen →

Browse all erd templates →

FAQ

Common questions

What is the fact table grain?

Each FactStay row represents one guest stay.

Why separate room type from room?

Many rooms can share a type, such as deluxe king.

Can this support property reporting?

Yes. Room Type links to Property for property-level rollups.