Designing a system to get a travel itinerary

The travel domain is interesting in terms of data modeling challenges. Throughout this book, we will be modeling systems that work together in a website that can be used for planning flight travel. Travelers would like to look at the options for an itinerary before booking any particular set of flights, especially if there is no direct flight from the traveler's current city to the destination city. Normally, an itinerary includes total duration, layover duration, and the number of hops it takes to reach the destination. We cannot, however, derive the itinerary without modeling cities and flights, which brings us to our first data modeling problem.

Introduction to modeling flights and cities

If we were to explain the problem of cities and the number of flights between them, we could start with drawing cities as nodes. In case two cities have two or more direct flights between them, we connect those two cities with an undirected relationship, as shown in the following figure:

Figure 2.1: Cities and flight routes

In the situation depicted by Figure 2.1, there is a direct route between New York and the following cities: Chicago, Los Angeles, Washington, and Las Vegas, for instance. However, if a traveler has to fly to New York from Istanbul, they have to choose from among the following routes:

• Istanbul—London—New York
• Istanbul—Athens—Paris—New York

In Figure 2.1, an undirected relationship between cities that are connected by flights is an abstraction stating that there are at least two direct flights between those two cities. For example, between New York and Los Angeles, there should be one flight from New York to Los Angeles and vice versa. In reality, there might be multiple flights, operated by different operators, travelling to and fro between two cities that are directly connected in Figure 2.1. This complicates the problem of presenting the traveler with a good itinerary.

Identifying the entities

Before we jump to modeling, we need to identify the interacting entities involved in this problem. For the problem of creating an itinerary, we need cities and flights connecting them. Cities form hubs, which one can travel to or transit through. Flights fly from one city to another.

Figure 2.2: Cities and flights

The preceding figure represents the information that is needed and applicable to both the entities. While city names aren't unique in the real world, we could use city names to uniquely identify the city in our domain. In addition to the name, a city will also have the name of the country it belongs to.

A flight can be uniquely identified by its code. Other parameters include duration of the flight (in minutes), departure and arrival time (in minutes within the day where 0000 hrs will be 0 minutes and 2359 hrs will be 1439 minutes). We capture information about the airports between which the flight operates in source_airport_code and destination_airport_code.

In the RDBMS world, an attribute that uniquely identifies a record is called a primary key. More often than not, this primary key attribute is denoted by prefixing pk_ to its name. Also, primary keys are mostly some sort of long integer. In this book, we will use UUIDs as primary keys wherever other attributes can't be used as primary keys. However, in cases where we can use any property as a key, we will (for convenience) use that field as a key. Please note that in production systems, the key should be a distinct property serving a single purpose—uniquely identifying the node/relationship. Also, having either long integers or UUIDs as IDs allows us to use them in URLs where other properties might not be usable.