Hotel PMS data model
Normalized (3NF) hotel PMS model. Property to room-type to room inventory and rate plans feed reservations, which materialize as room-assigned stays; each stay carries a folio with itemized charges and payments, while housekeeping tasks track room servicing. Guest and card data are classified and masked.
Hotel property-management operations: guest profiles, the property/room-type/room inventory hierarchy, rate plans, reservations and realized stays, the folio billing chain (charges and payments), and housekeeping tasks — modeled as a normalized OLTP front-desk schema with guest PII and payment card data classified and masked. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 12
- tables
- 127
- columns
- 14
- relationships
- 9
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Room-type vs. physical-room separation
Guests book a sellable category (room type) at reservation time but are assigned a physical room at check-in; splitting them keeps availability, pricing, and assignment correctly normalized.
Reservation header vs. stay vs. folio
A reservation is the booking intent, a stay is the realized occupancy of an assigned room, and a folio is its billing ledger; keeping them distinct supports no-shows, early departures, and split/master folios.
Charge-code lookup
Folio charges reference a static charge-code table so tax rates and revenue-center categories are maintained once and not repeated on every posting line.
Normalized (3NF) rather than dimensional
Front-desk and night-audit processing is transactional with frequent updates to folios and room status, where 3NF integrity beats analytical star schemas.
Tables in the Hotel PMS model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (11)
guest
Grain: one row per guest profile
Guest party record.
| Column | Type | Keys |
|---|---|---|
| guest_key | bigint | PK |
| guest_number | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| address | varchar | PII |
| country | varchar | |
| date_of_birth | date | PII |
| passport_number | varchar | PII |
| loyalty_tier | varchar | |
| loyalty_points | integer | |
| vip_status | boolean | |
| created_at | timestamp |
property
Grain: one row per hotel property
Hotel/property.
| Column | Type | Keys |
|---|---|---|
| property_key | bigint | PK |
| property_code | varchar | BK |
| property_name | varchar | |
| brand | varchar | |
| address | varchar | |
| city | varchar | |
| country | varchar | |
| star_rating | smallint | |
| total_rooms | integer | |
| check_in_time | varchar | |
| check_out_time | varchar | |
| currency | varchar |
room_type
Grain: one row per room category at a property
Sellable room category.
| Column | Type | Keys |
|---|---|---|
| room_type_key | bigint | PK |
| property_key | bigint | |
| room_type_code | varchar | BK |
| room_type_name | varchar | |
| description | varchar | |
| max_occupancy | smallint | |
| bed_configuration | varchar | |
| base_rate | decimal | |
| square_meters | decimal |
room
Grain: one row per physical room
Physical sellable unit.
| Column | Type | Keys |
|---|---|---|
| room_key | bigint | PK |
| room_type_key | bigint | |
| room_number | varchar | BK |
| floor | smallint | |
| status | varchar | |
| occupancy_status | varchar | |
| connecting_room | varchar | |
| is_accessible | boolean | |
| is_smoking | boolean |
rate_plan
Grain: one row per rate plan
Pricing/policy plan.
| Column | Type | Keys |
|---|---|---|
| rate_plan_key | bigint | PK |
| property_key | bigint | |
| rate_plan_code | varchar | BK |
| rate_plan_name | varchar | |
| description | varchar | |
| meal_plan | varchar | |
| is_refundable | boolean | |
| cancellation_policy | varchar | |
| min_nights | smallint | |
| is_active | boolean |
reservation
Grain: one row per reservation
Booking header.
| Column | Type | Keys |
|---|---|---|
| reservation_key | bigint | PK |
| guest_key | bigint | |
| property_key | bigint | |
| room_type_key | bigint | |
| rate_plan_key | bigint | |
| confirmation_number | varchar | BK |
| booking_channel | varchar | |
| arrival_date | date | |
| departure_date | date | |
| num_adults | smallint | |
| num_children | smallint | |
| num_rooms | smallint | |
| total_amount | decimal | |
| deposit_amount | decimal | |
| status | varchar | |
| special_requests | varchar | |
| booked_at | timestamp |
stay
Grain: one row per realized room-night occupancy
Assigned-room occupancy.
| Column | Type | Keys |
|---|---|---|
| stay_key | bigint | PK |
| reservation_key | bigint | |
| room_key | bigint | |
| check_in_at | timestamp | |
| check_out_at | timestamp | |
| actual_arrival_date | date | |
| actual_departure_date | date | |
| nights | smallint | |
| status | varchar |
folio
Grain: one row per guest folio (billing account)
In-stay billing ledger.
| Column | Type | Keys |
|---|---|---|
| folio_key | bigint | PK |
| stay_key | bigint | |
| folio_number | varchar | BK |
| folio_type | varchar | |
| total_charges | decimal | |
| total_payments | decimal | |
| balance | decimal | |
| currency | varchar | |
| status | varchar | |
| opened_at | timestamp | |
| closed_at | timestamp |
folio_charge
Grain: one row per posted charge
Charge posting line.
| Column | Type | Keys |
|---|---|---|
| folio_charge_key | bigint | PK |
| folio_key | bigint | |
| charge_code_key | bigint | |
| posting_date | date | |
| description | varchar | |
| quantity | decimal | |
| unit_amount | decimal | |
| amount | decimal | |
| tax_amount | decimal | |
| revenue_center | varchar | |
| posted_at | timestamp |
payment
Grain: one row per payment/settlement
Settlement record.
| Column | Type | Keys |
|---|---|---|
| payment_key | bigint | PK |
| folio_key | bigint | |
| payment_number | varchar | BK |
| payment_method | varchar | |
| card_last_four | varchar | PII |
| amount | decimal | |
| currency | varchar | |
| payment_date | date | |
| status | varchar | |
| transaction_reference | varchar |
housekeeping_task
Grain: one row per housekeeping task
Room servicing task.
| Column | Type | Keys |
|---|---|---|
| housekeeping_task_key | bigint | PK |
| room_key | bigint | |
| task_type | varchar | |
| assigned_to | varchar | PII |
| priority | varchar | |
| scheduled_date | date | |
| started_at | timestamp | |
| completed_at | timestamp | |
| status | varchar | |
| notes | varchar |
Lookup tables (1)
charge_code
Grain: static reference of charge codes
Posting code reference.
| Column | Type | Keys |
|---|---|---|
| charge_code_key | bigint | PK |
| code | varchar | BK |
| description | varchar | |
| category | varchar | |
| default_tax_rate | decimal |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- room_type.property_key → property (many-to-one)
- room.room_type_key → room_type (many-to-one)
- rate_plan.property_key → property (many-to-one)
- reservation.guest_key → guest (many-to-one)
- reservation.property_key → property (many-to-one)
- reservation.room_type_key → room_type (many-to-one)
- reservation.rate_plan_key → rate_plan (many-to-one)
- stay.reservation_key → reservation (many-to-one)
- stay.room_key → room (many-to-one)
- folio.stay_key → stay (many-to-one)
- folio_charge.folio_key → folio (many-to-one)
- folio_charge.charge_code_key → charge_code (many-to-one)
- payment.folio_key → folio (many-to-one)
- housekeeping_task.room_key → room (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.