A Database Model for a Hotel Reservation Booking App and Channel Manager

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.

The Idea

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
• Reservations
• Guests

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 city_name–postal_code–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.
• email – An email address for that company.
• city_id – References the city where 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:

• The first_name and last_name of that user.
• The user’s email address.
• A UNIQUE user_name.
• A password hash value.
• is_active – A flag denoting if that user account is active.
• ts_created and ts_updated – Timestamps when this account was created and the last time it was updated.
• company_id – References the company this 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 plan_name.
• All additional details related to that plan, in textual format.
• rooms_min and rooms_max – Defines the range of rooms that can be managed using this plan. The rooms_max attribute 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 status_name and 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:

• company_id – The company this invoice was issued to.
• invoice_amount – The total amount for that invoice.
• invoice_period – The time period this invoice was issued for.
• ts_issued, ts_paid, and 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 category_name values.

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 hotel this room belongs to. Together with room_name, it forms the alternate key of this table.
• room_type_id – A reference to the room_type dictionary 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

The 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:

• guest_id – The guest that made this reservation.
• start_date and end_date – The date interval for this reservation. Any room that’s part of the reservation will be occupied during this period.
• ts_created and 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 channel_used table.

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 (message_received). The ts attribute denotes the actual moment when this record was generated.

Guests

The last subject area in our model is Guests. It contains only two tables.

In the guest table, we’ll store a list of all the guests referenced in reservations and invoices. For each guest, we’ll store a first_name and last_name, contact information like email, phone, and address, and any additional details.

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:

• guest_id and reservation_id – References the guest to whom we’ve issued this invoice and the reservation related to it.
• ts_issued, ts_paid, and 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.