Everybody books into a hotel at some point. In this article, we’ll look at a data model that could power a hotel reservations system and channel manager.
Faster, cheaper transportation options allow us to travel across the world in a matter of hours. And people have more disposable income than ever before. Is it any surprise that tourism is growing rapidly?
In addition to traditional hotel booking channels, we also have newer options – like Airbnb and Booking.com – for online reservations. Although these sites expand a hotel’s reach, they also create some potential problems. Using more than one such online marketplace could lead to overbooking. To avoid that, it’s best to have all hotel reservation information in a single location.
That’s what we’ll be doing in today’s article – discussing a data model that could be used as the backbone of a hotel reservations system. Of course, this model could be used for similar businesses as well.
Picture an application that would allow hoteliers to track reservations and synchronize bookings from many different services (channels). We’ll be building a data model for such an app. We want to have one central place that will store everything, “listen” to other systems, and make changes accordingly. Our theoretical application will be used by many companies, with each of them having many users participating in their operations.
The Data Model
The hotel reservation and channel manager data model consists of five subject areas:
Countries & cities
Companies & plans
Hotels & rooms
We’ll describe each of these subject areas in the order listed.
Countries and Cities
This subject area is not specific to this model, but we’ll describe it first because these tables are referenced later.
The two dictionaries in this section, each containing lists of the countries and cities referenced in the full address of the company or hotel. Each
country is UNIQUELY defined by its
country_name, while each
city is UNIQUELY defined by the
country_id combination. Since cities often have multiple postal codes, we’ll use the “main” postal code for that city in the
postal_code value. This could be changed if we create an additional table with a list of all postal codes belonging to each city.
Companies and Plans
Companies are our app’s customers. In order to use our application, they’ll subscribe to a certain plan. The plan itself will be defined by the number of rooms managed in the app.
The central table in this subject area is the
company table. It contains a list of all the companies (i.e. hotels) we work with. For each company, we’ll store a:
company_name– The company’s name.
VAT_ID– A UNIQUE tax ID number.
city_id– References the
citywhere the company is located.
company_address– The full address of the company, including the street name, number, and postal code.
- All additional
details, in textual format
is_active– A flag denoting if that company is active in our system or not.
Companies can create multiple
user_accounts. Each account can log into the application and perform their desired operations (please note that roles are not implemented in this model). For each account, we’ll need to store:
last_nameof that user.
- The user’s email address.
- A UNIQUE
- A password hash value.
is_active– A flag denoting if that user account is active.
ts_updated– Timestamps when this account was created and the last time it was updated.
company_id– References the
companythis user account belongs to.
We offer different plans to our customers. A list of all our plans is stored in the
plan table. For each plan, we’ll have:
- A UNIQUE
- All additional
detailsrelated to that plan, in textual format.
rooms_max– Defines the range of rooms that can be managed using this plan. The
rooms_maxattribute can be NULL because we can expect that our largest plan will not cap the number of rooms, e.g. “100 rooms and up”.
monthly_price– A price we will charge to the customer for each month they use this plan.
The relationship between companies and plans is stored in the
company_plan table. Each company can change plans according to their current needs. For each record in this table, we’ll store company and plan IDs as well as the timestamps when that plan was created, activated, and deactivated. These three timestamps will be assigned automatically, as the plan status changes.
While we want to know if a certain plan is active or not, we also need to understand each company’s activity with a given plan. To do that, we’ll need a list of all possible statuses for any plan assigned to a company. we’ve put this list in a dictionary called
company_plan_status_catalog. It contains only two attributes: the UNIQUE
plan_is_active, which denotes if the record assigned to this status is active or not. Expected values in this table include “subscribed for the first time”, “plan upgraded”, “plan downgraded”, “unsubscribed”, “resubscribed”.
Next, we’ll use the
company_plan_status_events to assign statuses to companies’ subscribed plans (current and past plans). These statuses will closely describe what led to the plan changes stored in the
company_plan table. Besides the foreign keys, we’ll store only the timestamp (
ts) when this status was assigned.
The last table in this subject area is the
invoice_company table. We’ll use its simplified structure to store all the invoices created in our system. These invoices will be issued to companies for using our application. For each invoice, we’ll need to store the following details:
companythis invoice was issued to.
invoice_amount– The total amount for that invoice.
invoice_period– The time period this invoice was issued for.
ts_canceled– When this invoice was issued, paid, and/or canceled.
Please note that we’re assuming all users have equal rights and permissions in the application, so there are no admin, standard user, read-only user, (etc.) roles. If we want to implement that, we’ll need to add a dictionary to list all roles, another dictionary to list permissions, and an additional table to assign these roles to users.
Hotels and Rooms
In this subject area, we’ll store information about the physical properties related to our business: hotels and their rooms.
The central table here is the
hotel table. I’ve used the term “hotel” to describe the collection of rooms. Still, this could be a single apartment, motel, hostel, etc. For each hotel, we’ll use a UNIQUE
hotel_name, an additional textual description, its’
category_id, and references to the
company_id which runs that hotel and the
city_id where the hotel is located. The
is_active attribute denotes if that hotel is still active or not.
I’ve already mentioned the
category. This is a simple dictionary used to closely describe the category of that hotel. It contains a list of UNIQUE
Each hotel will have one or more rooms. Information about them is stored in the
room table. For each room, we’ll need a:
room_name– A name used to define that room in the hotel. This could be a descriptive name or just a number.
description– An additional textual description of the room.
hotel_id– A reference to the
hotelthis room belongs to. Together with
room_name, it forms the alternate key of this table.
room_type_id– A reference to the
room_typedictionary that denotes the type/category of that room.
current_price– The current price charged for this room. Pricing is simplified and our model doesn’t allow users to set future prices (though that could be very useful for planning).
The last table in this subject area is the
room_type dictionary. This is where we’ll list all possible types we could assign to any room. Expected values include “single”, “double”, “suite”, “queen”, “king”, etc.
Reservations subject area is the central part of this data model as well as its “brain”. Everything we’ve done so far has led up to this.
We’ll start with the
reservation table. Guests will create reservations for a defined time period. They can book one or more rooms in the same reservation. For each reservation, we’ll store these details:
guestthat made this reservation.
end_date– The date interval for this reservation. Any room that’s part of the reservation will be occupied during this period.
ts_updated– Timestamps when this record was created and most recently updated.
discount_percent– The percentage of price discount, if any.
total_price– The total price for that reservation.
A list of all reserved rooms is stored in the
room_reserved table. For each record, we’ll store references to the reservation, room, and the current price of that room.
We also need to track reservation statuses; we’ll use two tables for that. The
reservation_status_catalog is a simple dictionary containing all possible UNIQUE
status_names we could assign to any reservation. The actual statuses are assigned to reservations using the
reservation_status_events table. It contains references to the status catalog and the
reservation table. It also stores additional textual
details and the timestamp when the status was assigned.
The last things we need to solve in this subject area are channels and synchronization. We can expect that hotels will offer their rooms on many different channels. When a room is reserved on any channel, we should reserve that room on other channels as well. This will require several steps.
First, we’ll need to store a list of all the channels our application supports synchronization with. That list is stored in the
channel table. Some expected channels are “Airbnb”, “Booking”, “FlipKey”, “HomeAway”, “Expedia”, etc.
Next, we’ll store info about which channel was used to book which room. This info shall be entered by our customers: they can select the channels used for each room. That UNIQUE pair is stored in the
The last thing we need to store is the
synchronization. I’ll simplify it here and store only when the event happened. For each reservation and channel, we’ll store a textual representation of the synchronization message (
message_sent) and the response (
ts attribute denotes the actual moment when this record was generated.
The last subject area in our model is
Guests. It contains only two tables.
guest table, we’ll store a list of all the guests referenced in reservations and invoices. For each guest, we’ll store a
last_name, contact information like
address, and any additional
The last table in our model stores invoices issued to guests. As with company invoices, this table is a simplification. For each
invoice_guest, we’ll store:
reservation_id– References the
guestto whom we’ve issued this invoice and the
reservationrelated to it.
ts_canceled– Are timestamps when the invoice was issued, paid, and/or canceled.
Share Your Thoughts on our Hotel Reservations Data Model
We’ve discussed the backbone of a hotel reservation and channel manager data model. Besides the standard options, we also have the ability to synchronize reservations across many channels. Have you tried this type of application? If so, do you think our model lacks anything? What would you add or remove? Please tell us in the comments below.