Summer Is Here: A Travel Agency Data Model

Most of us love to travel and explore new places, and using travel agencies is an easy way to plan our holidays. With vacation season in mind, we’ll answer the question “What kind of data model would run a travel agency?”

In my personal opinion, the best way to travel would be without any time or budgetary limits. We could leave the beaten track and explore all the most interesting places in as much detail as we want. Unfortunately, most of us don’t have that luxury, so we use what is available: travel agencies. These companies make planning and combining accommodations, transportation, and even activities much easier. In this post, we’ll explore a data model that we could use to run such an agency.

What Should We Know About Travel Agencies?

Before we look at the data model, let’s answer some common questions about the travel business.

1. What do travel agencies do?

   Broadly speaking, the answer is “anything related to travel in any way”. However, two services are the most important and also the first things that come to mind: transportation (how we get to the destination) and accommodation (where we will stay once we get there). These two make up the core business of a travel agency.

   Even so, there are many other services that travel agencies offer. When we reach our destination, we may want to do something that’s unique to that place: visit the Tower Bridge, ride on the London Eye, see an opera at the Sydney Opera House, etc. Travel agencies may buy tickets, rent vehicles, book tours, or do other destination-related things for us. Some people simply ask the agency to organize everything for their trip. Others book a tour package. Universally, customers want to get the best possible service and an experience that meets their expectations at the lowest possible price. Travel software development plays a vital role in enhancing these services by streamlining booking processes, managing itineraries, and providing personalized recommendations based on customer preferences.

2. How does the business model work?

   Travel agencies usually work with partners who provide lodging (hotels, hostels), transportation (airlines, bus lines) and activities. They offer a wide range of services and combine them to present the most attractive offers to potential customers. In addition to their own surcharges and fees, travel agencies may be paid a commission on the partnered services they sell. If they combine many services (i.e. as a package deal), they could also offer a discount to customers.

   Basically, travel partnerships are a win-win situation because partner providers save money they would otherwise need to invest in marketing and travel agencies get needed funds. And there is also a win for the customers, who save time as well as some money.

3. How old is the travel agency concept?

   Before people traveled for pleasure, a voyage usually involved some type of pillaging and burning. ☺ Seriously, though, for much of history only the rich could afford to travel to a distant land. Religious pilgrimages became popular in the Middle Ages; many of these pilgrimages are still very popular.

   From 1660 to the mid-1800s, the Grand Tour became a must for well-educated members of the European upper classes. This was a long trip around Europe that let people experience other cultures, learn new things, and step outside their comfort zone. During the mid-1800s, the rise of railroads and industrialization made travel more accessible to the middle classes.

   The world’s first travel agency, Cox & Co. was established in London by Richard Cox in 1758. Now doing business as Cox & Kings Ltd, it is still active today.

4. What’s the status of today’s travel agencies?

   Technological growth has made many things affordable to middle and lower class customers, including travel. Today you can find multiple travel agencies in nearly every city. Plus, the world is “smaller” than it has been ever before. Travelling from Europe to Cuba or Australia is now a matter of hours rather than weeks or months.

   Also, customers are better informed. When they enter a travel agency, they know about hotel and transport services from all over the globe. They just need to pick and book what they want and can afford.

5. What will we cover in our travel agency data model?

   This will be about basic requirements. We’ll focus on the two most common services: accommodation and transportation. We’ll also have a set of predefined offers, and we’ll track contracts and payments. We won’t go into much detail because the model would become too large.

Now let’s move on to the data model.

The Data Model

The data model consists of four main subject areas:

• Countries & cities
• Hotels & transport companies
• Customers & agents
• Offers & contracts

We’ll describe each subject area in the same order they are listed.

Section 1: Countries & Cities

The Countries & Cities subject area contains only two tables. We’ll use them to denote the cities where hotels are located as well as the starting and ending points of a trip.

The country table contains UNIQUE country_name attributes. Each country will also have a UNIQUE country_code. For the United Kingdom, this code might be “GB”, “GBR” or even “826” (think ISO).

The city table will list all cities where our travel agency transacts some type of business. Each city will have a city_name and the ID of the country it belongs to.

Section 2: Hotels & Transport Companies

This subject area will store records for all services and their providers. I’ve focused only on accommodation and transport here. Three of the seven tables in this section are related to hotel services; the remaining four deal with transportation services. The city table in this section is just a copy used to avoid relations overlapping.

The most important aspect of accommodations is the partners we work with. A list of all such partners – i.e. hotels – is stored in the hotel table. Please note that we could easily use this table to store hostels, motels, or even rental homes or apartments. The attributes in this table are:

• hotel_name – The name of a specific hotel. We can expect that it will be the same as the one that is usually used for that hotel, e.g. “Sheraton Zagreb Hotel”. This name will almost always be unique, but this attribute is not UNIQUE.
• city_id – The city where the hotel is located.
• hotel_address – The address of the hotel. Once again, this will almost always be a unique value, but it is not a UNIQUE attribute.
• details – All the hotel’s details, in text format.
• is_partner – If this is a partner hotel or not.
• active – If the hotel is currently operating.

The room_type dictionary lists all possible types of rooms any hotel could have. Besides the primary key, this table contains only the UNIQUE type_name value. Some expected room types are “single”, “double”, “single with balcony,” etc.

The main service hotels offer is the rental of one or more rooms. We’ll store the prices related to each hotel’s various room types in the hotel_service table. This list will be used later when we make offers to customers. For each room type, we’ll store:

• hotel_id – A reference to the related hotel.
• room_type_id – A reference to the room_type dictionary.
• service_price – The actual price the customer should pay for that room.
• active – If the room type is currently on offer.

Now, let’s move on to transportation. The combination of transportation and accommodation are the backbone of each travel agency’s business. As I’ve already mentioned, we’ll use four tables for this key area.

We’ll consider the two dictionaries first. Both of them consist of only two attributes – id, the primary key of the table, and the type_name attribute, which holds only UNIQUE values. The company_type dictionary will contain values like: “airline company”, “railway company”, “bus company” and “car rental company”. The ticket_type dictionary will contain anything that could be used to describe a ticket: “one-way ticket”, “round-trip ticket”, “first class”, etc.

Just as hotels are our accommodation partners, transportation companies are our transport partners. A list of all such companies is stored in the transport_company table. We’ll use the following attributes to describe each one:

• company_name – The company name, which is not necessarily unique. We can expect that the combination of company_name and HQ_address will uniquely denote each company.
• city_id – The city where the company headquarters is located.
• HQ_address – The address of the company headquarters.
• company_type_id – References the company_type dictionary.
• description – A detailed textual description of the company. If it is a partner company, we could also describe our partnership here.
• is_partner – If the company is our partner.
• active – If the company is currently operating.

The last table in this subject area is the transport_service table. We’ll use it to record prices for all available ticket types. For each ticket type, we’ll store:

• transport_company_id – The ID of the transport_company that is offering the ticket.
• ticket_type_id – References the ticket_type dictionary.
• from_city_id and to_city_id – Are both references to the city table and denote the starting and ending points for that ticket.
• service_price – The actual price the customer should pay for that ticket.
• active – If this ticket type is active or not.

An actual schedule is not stored in this table; that would require having schedules for every line from every partner company in our database. I would avoid this because we’d have to be updating the schedules quite a lot (and regularly checking all partner sites for changes). I would leave checking actual schedules as an action to be performed by an operator when an offer is created.

Please notice we don’t store exact room or ticket numbers in the hotel_service and transport_service tables. Instead, we store information about the companies that provide such arrangements. We can assume that we’ll check room and ticket availability directly using the company’s system. Still, we need to know what services they offer to say (for example) to a customer: “You can fly first-class from Hamburg to Havana with Air France on May 25^th, stay at the “Iberostar Parque Central” hotel for 2 weeks and fly back to Hamburg on June 6^th for $3,000 US”.

We simply cannot store all room and ticket availability data. That would require that we have direct access to all the data from all our partners.

Section 3: Customers & Agents

This subject area consists of two tables that relate offers and contracts with customers and agents.

The customer table stores a list of every customer we have ever had contact with. For each customer, we’ll have the following values:

• first_name and last_name – The first and the last name of that customer.
• address, phone, mobile and email – All contact details for that customer.
• details – An unstructured textual description for that customer.
• customer_from – When this customer’s record was inserted into our system.

The second table in this subject area is the The second table in this subject area is the “agent” table. It lists all previous and current employees who have made offers to customers on behalf of our agency. For each employee, we’ll store: table. It lists all previous and current employees who have made offers to customers on behalf of our agency. For each employee, we’ll store:

• agent_code – A UNIQUE code for that agent.
• first_name and last_name – The first and last name of that agent.
• active – If the agent is currently employed by our agency.

Section 4: Offers & Contracts

The last subject area in our model is Offers & contracts. It holds seven tables that contain the details of all offers and contracts we have made for our customers. I will focus only on offers and contracts for individual customers here. Two tables, the hotel_service table and the transport_service table are copies used here to simplify the model.

I’ll describe promo offers first, then move on to other offers and finally to contracts.

Before a customer even enters our agency, we could already have some predefined offers available – our promos, or promotional offers. Creating such offers simplifies our business. We can present these quickly and many customers will select one. All such offers are recorded in the promo_offer table. For each promo, we’ll store:

• promo_offer_code – An internal code that UNIQUELY identifies a promo.
• promo_offer_name – The full name of that promo offer.
• active_from – The date when we started or can start offering this promo to customers.
• active_to – The last day when this promo could or can be offered. Note that this field could contain a NULL value. We may want to offer this promo indefinitely, or we may not have the ending date yet.

Each promo offer contains a list of services, usually including (at a minimum) accommodation and transportation. We’ll use the promo_offer_hotel_services and promo_offer_transport_services tables to store this data. The structure of both tables is almost the same, so I’ll describe them together. For each promo-related service, we’ll store:

• promo_offer_id – References the related promo offer.
• hotel_service_id or transport_service_id – References the related hotel or transportation service.
• price – The price of the service at the moment when the promo offer was created. We should save this price because the service_price could change along the way.
• discount_percent – The price discount percentage we’ll offer to a customer for that service (if the customer accepts this promo offer).
• final_service_price – The service price less the discount amount.
• description –All details, in unstructured format, related to this service and promo offer.

We will also need to create offers “from scratch” – that is, not related to any promo offer. Both “from scratch” and promo offers are covered in the offer table. For each offer, we’ll store:

• offer_code – An internal code that UNIQUELY denotes an offer.
• offer_name – The full name of that offer.
• time_created – The exact time when the offer was created.
• active_from – The date when this offer became active for that customer.
• active_to – The date when this offer is no longer active for that customer. This could be NULL if an offer is available indefinitely. We can expect that we’ll give the customer a timeframe to accept an offer; if it expires before the customer acts, the offer won’t be available anymore.
• time_accepted – When the customer accepted this offer (if any).
• accepted – If the offer was accepted.
• promo_offer_id – References the promo_offer table. If the customer selected one of our promo offers, we’ll store the ID of that offer here. If we have created a custom offer, this value will be NULL.
• agent_id – References the agent who created that offer.
• customer_id – References the customer for whom the offer was created.

As with promo offers, customized offers will also include accommodation and transport services. We’ll use the offer_hotel_services and offer_transport_services tables for this information. Their structure and attributes are almost identical to the promo_offer_hotel_services and promo_offer_transport_services tables, so I won’t explain them again.

The last table in our model is the contract table. The contract is generated after the client accepts an offer. This table will store all details related to contracts signed by our customers. It contains these values:

• contract_code – An internal code that UNIQUELY identifies each contract.
• customer_id – The ID of the relevant customer.
• agent_id – The ID of the agent who signed that contract on behalf of our agency.
• offer_id – The ID of the related offer. This provides us with information about all the services included in the contract.
• time_signed – When this contract was signed.
• total_price – The sum of all prices for all services included in the offer.
• payment_date – The date when the client paid for this contract.
• payment_time – The actual time when the client paid for this contract.
• payment_amount – The amount paid for the contract.
• refunded – If a refund was made for the contract.
• refunded_time – When the refund was issued (if any).
• refunded_amount – The amount of the refund (if any).

The attributes for payments, refunds, times, and amounts can contain NULL values. They will be updated as needed.

What Else Could We Do With This Model?

Today we have discussed a very basic data model that could help manage a travel agency. All the most important parts of a travel business – like the customers, agents, accommodation services, and transport services – are covered. Still, there is much more that could be added.

For example, what about group reservations? This would let us book multiple rooms and tickets for a group of tourists. In that case, we would need modifications in the tables handling promo offers, offers, and contracts.

What would you add to this model? What would you change? Would you remove anything? Tell us in the comments section.