Let's design a hotel reservation system

similar to Booking.com or Airbnb, which

is a typical system design interview

problem. The mistake that most

developers make here is when they ask to

design such a system, they treat it like

a typical CRUD application with some

caching and replication for the

availability.

But that's not the correct way to think

about it. For instance, one of the

problems that you might have is a moment

when two users are trying to book the

last room available at the same time and

you try your simple select then insert

approach which creates the double

booking problem. So you end up charging

two credit cards for one room and now

you're dealing with refunds with angry

customers and the system that cannot be

trusted for the next time. That's why

hotel reservation systems are hard

because they also test the exact skills

that separate mid-level developers from

seniors. Things like handling

concurrency without breaking it under

heavy load, keeping the data consistent

across multiple systems, and also

designing for failure in case something

goes wrong in parts of the system. And

it's not about memorizing these

patterns, but rather understanding the

actual problems that might occur. What

are the solutions to those? and what are

the trade-offs between those solutions.

So, we're going to design this system in

four simple steps. First, we will gather

the design requirements, estimate the

storage and queries per second,

transactions per second, and anything

else that we need for designing the

system. Then, we'll get into the basic

highle design including the API design,

database, schema, reservation flows, and

so on. And in the first step, we will

scale the system to handle 1,00 queries

per second and 17 transactions per

second, which are the numbers that are

close to the realbook.com scale. And

then we will handle the failures under

heavy load and discuss the system

recovery whenever somethings or some

parts of the system fail. So if you're

tired of system design explanations that

skip the hard part and only give you the

surface level, then this one is for you.

Let's start with the first step which is

the design requirements. In such

challenges, we usually follow a

framework to come up with the solution.

And the very first step is always to ask

clarifying questions to lock in the

scope that we are going to design and

then also about functional and

non-functional requirements. So let's

start with the clarifying questions that

we might have. For example, one of the

questions can be about the payments

which is are we going to allow them to

pay at the platform or it is going to be

a payment in checking and this will let

us know whether we support payments in

the platform or not. For this one let's

say yes. So we are going to allow them

to pay at the platform which means we

need to have some sort of payment system

inside. Another question can be about

booking channels. So is this a website

only web app where they are going to

book the hotels or we also support the

mobile app which might have different

rates and different pricings. So let's

say we are going to design both like web

version and mobile version of this

application. Then about cancellations

and over booking. So do we support

cancellations of the reservations and

also over booking because hotels

typically allow some sort of over

booking taking into account that there

might be some cancellations. So if they

are getting let's say 10 bookings per

month then out of these like two or

three are going to be cancelled. So they

allow users to also overbook because of

the cancellations. Once they get some

cancellations the room is free now. So

they will allow over booking as well.

Then about the dynamic pricing. So are

the prices fixed or the price changes

based on the demand and the dates. And

for this let's say we are going to have

dynamic pricing. Whenever the demand is

higher so it's let's say Christmas time

which is around now. We are going to

increase the prices. For example this

room where I'm recording from it was

triple of the price as in usual because

that is the Christmas time. So they are

going to increase the numbers and

similar to that any other popular dates

that people are reserving we are going

to increase the prices based on the

demand that we are getting. Then we can

get to the functional requirements. So

one question might be about the hotel

management. Are we going to support the

admin panel like adding updating

removing hotels or we are only focused

on the user part? Admin panels might be

included in this, but the core

functionality evolves usually around the

users which are booking the hotels,

cancelling them. Let's say we are going

to also support some sort of admin panel

in this system. Then we get to the

search and discovery questions like what

are the core functionalities we are

going to support. And a way you can

think about this is what are the

functionalities that if we remove from

booking.com

it doesn't become booking.com anymore.

This might be a good approach for

identifying what are the core

functionalities. For example, if users

are not able to search hotels by

location, then it becomes some sort of

useless application, right? So of course

one of the core functionalities we will

support is that users need to be able to

search by location, by dates, by guest

number, price range and some other

filters that we might set. Then about

the reservation flow, how usually it

happens is users book the rooms. Then

they view the reservations, they should

be able to cancel the bookings because

we also clarified it above. And also the

system should prevent double bookings.

This is one of the challenges we need to

identify here so that we also solve it

in the next steps. Then about the

payment processing since we clarified

that we are going to support payments in

the app then we need to integrate some

sort of payment processing like Stripe,

PayPal or something else. And optionally

it might also include notifications. So

sending confirmations for bookings or

cancellations or payments to both user

and the admins brokers who are uploading

these hotels. Then we get into the

non-functional requirements which are

more about the consistency, load and

latency that we are going to support.

Let's start with the consistency. We are

going to have strong consistency because

two users shouldn't be able to book the

same room for the same dates. That is

non-negotiable.

But whenever they are searching or

discovering the rooms there the eventual

consistency is fine and you will see why

also when we get to the designing phase.

Then about the concurrency. So we need

to handle thousands of users trying to

book the same hotel simultaneously and

without it breaking the system. Also

about the latency we need to provide low

latency. So search results should appear

under 500 milliseconds and booking

confirmations are fine if they are

handled between two or 3 seconds. You

should also clarify the availability and

scale. Let's say for availability we are

going to support the free 9 uptime. And

when it comes to scale we need to be

able to scale this horizontally without

redesigning the system from scratch in

the future. Once we gather the design

requirements, we can move on to the

estimation of data and capacity and

after that we can start designing the

system. Now when it comes to the data

estimation, the most important things we

are interested here is the queries per

second that are going to happen in the

system and also transactions per second

which are the rights to the database

when users are booking hotels. So in

this kind of system you can think about

it as this funnel here. The users that

come uh to the platform are at the top

here. So these are the users who are

viewing the hotel room. This is the 100%

of the traffic somewhere here. And then

next we have out of these users probably

like only 10% will go into browsing the

hotels and searching for some good

matches and booking the hotel. So that

will be the second layer here. And then

third we will have the reservations. So

only out of these only the 10% are

actually going to book anything because

most of the visitors are tire kickers.

They are not looking to book anything.

They are just browsing. But we still

need to design the systems for them

because they are also going to increase

the queries per second that are

happening in the system. So based on

this there are two ways for us to

calculate the load. One approach is

let's say to take a look at the

booking.com and you can use some sort of

extension like similar web to see the

traffic that it's getting per month. For

example, this month it got 500 million

visitors in the website. That means that

500 million users were browsing the

system this month. So at the top and

then you can start calculating it down

based on this to see also the

transactions per second and what are the

queries per second based on this load

and the other way is the bottom up

approach which is to get the number of

reservations per year let's say and for

booking.com that is 1.1 billion

reservations per year and then we can

calculate this upwards so starting from

here how many visits we get per month or

per second to the platform and we will

also be able to calculate the

transactions per second because we have

the number for the year reservation. So

each of these numbers is fine. You just

need to get one of them from the

interviewer and then you can start the

calculations and also take into account

that these numbers are also rough

estimates. So you might get these

numbers exactly to calculate or you

might get just the visits per year and

the bookings per year and you should be

able to calculate it after that. The

other important metric that we need is

the average stay. So what is the average

stay that users are booking the hotels?

Let's just say two or three nights per

booking. So let's go with the two nights

per booking and then we will be able to

calculate the rest. So let's start with

the daily reservations. We have the

number of bookings per year which is 1.5

billion rooms per night. Let's just

start with this approach. Then we can

divide this by the nights per booking

and that comes down to 550 million

bookings per year because this is the

room nights in total. This is not the

bookings. So we need to divide this by

the nights per booking to get the actual

number that we are getting booked per

year. Then we can divide this by 365

which is the days in the year and we'll

get the number of bookings per day which

is 1.5 million. And after that we can

divide the 1.5 million by the seconds

per day which is 86,400

and that means that we are getting 17

bookings per second which is the

transactions per second that we are

going to support in the database. Then

when it comes to the traffic funnel part

and the reads as you saw with similar

web we are getting something like 500

million visits per month in the

booking.com with about eight pages per

visit which means that on each visit the

users are going to browse around eight

pages before they leave the website.

Based on this, we can first get the

visits per day and we can get that by

dividing the monthly traffic with 40

which are the days in the month and

we'll get to 17 million visits per day.

After that, we can get the visits per

second. So, if we divide that by the

seconds in the day, we will get uh 200

visits per second to our website. And if

we assume that they are going to browse

around eight pages per visit before they

leave, let's say that it comes down to

five backend reads per visit, which can

be things like search queries,

availability checks. So we need to just

increase this number by multiplying it

with the five and we'll get around 1,000

read requests per second. And based on

these calculations, we can clearly see

that this is going to be a strong read

heavy system because we have 1,000 QPS

queries per second that we need to

support and only 17 TPS which are the

transactions per second that we will

get. So our challenge here will be to

optimize the architecture for reads

while still keeping the rights

consistent to avoid the double booking

problem. These are the core numbers we

need to start designing the system and

then the rest we can look at while

designing the API database and if there

are any more things that we need it's

fine to calculate those there but at

least we need to get the main things

that we need before starting with the

highle design. After the scale and data

size calculations we can now design an

architecture that fits these numbers. So

we can start by using a serviceoriented

architecture with clear boundaries but

also keep the tightly coupled data in

the same database. So as a starting

point let's say we have the mobile app

or the web app where users are browsing

these hotels and making the

reservations. We'll use CDN in front

here for the static assets like

rendering the images, media files, also

the JavaScript content and the hotel

images. Then we have an API gateway

which will be the single entry point to

our application. This is where we will

handle the authentication, the rate

limiting and also routing the requests

between all the services that we have.

It can also serve as a load balancer

across service instances. So if you have

multiple instances of hotel service,

let's say, this API gateway can serve as

a load balancer and route the traffic

between the instance one and instance

two. When it comes to services, we can

separate this with boundaries for hotel

service. This is where we will have

hotel and room information, mostly

static and heavy cachable service. An

important service here is a reservation

service. This will handle the booking

logic, inventory checks, reservation

creation and cancellation. And this is

also where concurrency will be handled

in the application. Next, we have a rate

service. This will be for room pricing

by date because depending on the date

and when you book the hotel, the price

will be different. So this will be

applying dynamic pricing based on the

demand of the rooms. To integrate the

payment gateways, we will also need a

payment service that will implement

let's say Stripe payments, PayPal

payments, Apple Pay, Google Pay and

other providers. And we also have a

notification service that talks with the

reservation service and with the payment

service. This is what sends the emails

and push notifications for the data

layer. Initially we can start by using

some sort of SQL database like possql

primarily because we will need the acit

guarantees and this will be also useful

for the transaction store and we will be

using many transactions. So we need the

consistency of SQL databases that's why

we go with SQL in this case. We also

need some sort of caching like radius

for the data that doesn't change that

often. But we can also improve this a

bit further. So first of all if we think

about the scale of the reads that we

will have which is around thousand

queries per second postsql database

won't be able to handle that load. So we

need to give some sort of solution to

this. For such situations, tools like

elastic search are a good solution for

handling the massive scale for the

search. So we will introduce a search

service that will be responsible for

retrieving the hotels by the filters

that are set on the front end. And

elastic search will handle the full text

based search on hotel names and

descriptions. Also, it will be able to

filter hotels, let's say within 5

kilometers of location or within some

range to that location that is set and

it will be able to filter by the price,

range, ratings and any other filters

that we need. The thing with elastic

search is that it's way faster than the

postgrql database that we will use let's

say for listing the hotels and also it

is eventually consistent meaning that

eventually it gets updated with the

database and gets the latest data in the

elastic search and indexes everything

properly to be able to search quickly

based on locations or based on the price

range and any other filters. We can also

decouple the services a bit. Another

approach is to use different databases

for each microservices. Like for the

payment service, we can have payment

database. For the hotel service, we can

have hotel database. And we'll discuss

what's the pros and cons of this. But to

also decouple them, we can introduce a

message cube that will be responsible

for instance for payments. Whenever a

payment is processed in a payment

service, we will process that and

publish a message in a Kafka rabbit MQ

or some other messaging queue. And this

will first of all send email push

notification to the user or to the owner

as well as an email or as an inop

notification that the payment was

successful and the hotel is reserved.

And it will also update the availability

meaning the status of the hotel. So it

will push that to the search service

which will update it in the elastic

search and that is again getting

eventually consistent. So right after

the payment maybe if users search they

will still see that hotel available but

they won't be able to book it because we

are using the hotel database to double

check whether it's really available or

not. And eventually after some time this

will be persisted with the database and

it will have the same data as the

postgrql database. Next when it comes to

choosing whether all these services will

be like microservices or a single

monolith or they can also be pragmatic

microservices. For example, in our case,

a single monolith would simplify the

transactions, but it will become hard to

scale and deploy independently because

if many developers, let's say hundreds

of developers are working on this single

monolith, then it becomes very hard to

deploy independently and work on things

without stepping on each other. Another

approach is full microservices like this

where you have rate service,

notification service and so on with

separate databases which will introduce

distributed transactions and consistency

problems that don't exist at the

monolith level. That is because each

service as you can see like hotel

service owns its own database,

reservation service owns its own

database. So another approach is to keep

the tightly coupled data together in one

database and share it between the

services. For example, in our case, the

reservations and the hotel databases can

be kept together. This will let us to

use the local ACIT transactions instead

of the distributed transactions. And we

will have a service level separation at

the application layer but we will still

have the shared data for tightly coupled

entities. After you agree on the highle

design the next step is the API and

database design and you can start with

API design then database or vice versa

whichever way it's easier for you to

talk about the flow. So in our case

let's start with the API design. We will

use restful APIs for all the operations

because that is what makes sense in this

kind of system. Although as you can see

here the front end directly makes calls

to hotel API to other APIs as well that

we have. Maybe sometimes it will make

calls to reservation API. Another

approach here a very common one is when

you have multiple microservices usually

you would use a GraphQL layer in front

of the front end. So front end will only

make requests to the graphql and it also

will know the schema based on the

graphql schema and then the graphql

layer will be responsible for making

calls to all these services that you

have behind the scenes. So it kind of

extracts away the complexity and the

routing between these services. But to

keep it simple on the diagram, let's

just assume that front end makes the

calls directly. So we have here admins

who are listing the properties. We have

users who are browsing the hotels and

making reservations and we have the

front end app which is either a mobile

app or the web version. The very first

step for admins is they need to be able

to loged in to be able to use the

features for the admins while users can

be browsing the hotels without being

logged in. But whenever it comes the

point for the reservation they need to

also create an account or login. For

listing the available hotels, we will

make a get request to slash hotels with

any necessary filters that the user sets

and we will get the available hotels and

then if they want to browse a specific

hotel then we will make a request same

get request to / hotel/sp specific ID of

the hotel which will return us the hotel

that has that specific ID. Now how it

will happen behind the scenes is hotel

API will fetch the hotel details from

the database. So for specific hotel we

can also read this from the poster SQL

database. But in the initial call where

we are making a request to / hotels that

is where we will be using the elastic

search to quickly filter based on the

filters that are set on the front end.

And at this step for a single hotel we

just basically forward the hotel details

to the front end and then front end is

responsible for parsing and showing the

details in the page. For admins who want

to add a new hotel, they will need to

make a post request to slash hotels

endpoint. And this is where we will

insert it into the SQL first and then

once we get a successful insert, we will

just forward the 2011 status that the

hotel is created and later this will be

eventually persisted with the elastic

search database. But we will show the

confirmation at this step to the admin

that the hotel has been created. And

similarly, if they want to update the

hotel information and they make some

edits, we can make a put or patch

request to hotels/ ID of that hotel and

again update it in the database, get the

update result and send the confirmation

and show it for the admin. A similar

flow for removing hotels. Again, a

delete request in this case to SL hotel/

ID of that hotel. We remove it from the

database and we show it to the user and

later this will be removed from the

elastic search. And within hotels, we

will also have rooms because one hotel

has many available rooms. We will treat

them as a sub resource. So under hotels

and specific ID of the hotel we will

have / rooms first of all which will

give you all the rooms that are

available under this hotel and to fetch

the specific hotel you can make a

request to / ID and get more details

from that hotel which will again fetch

from the database and then you will

forward it to the client and basically

here we are just repeating whatever we

have for the hotels but for the rooms.

So if you want to add a room you will

make a request to post/ id of the hotel/

the rooms. This is again following the

general restful principles. We will

insert the room in the database and

provide with a successful response. Of

course you need to also handle the

failures whenever something goes wrong

showing the error on why something went

wrong in the API. But when talking about

the happy paths or at least the

available endpoints, these are what we

will have. We will also have a put

endpoint which is for editing the room

type again / rooms. In this case, we

need to provide an ID because we are not

creating a new one but we are editing

one. So we need to specify which ID is

that room that we are editing. And same

goes for deleting the same URL as we

have for the put. But in this case the

method will be delete which will remove

the listing from the hotel.

Then for the reservations this is where

we will make requests to the reservation

service. Let's say a user wants to see

their reservation history. We need to

provide an endpoint here to fetch the

reservations. So a get request to

/reservations which will read it from

the SQL database and then forward the

response to the client. Then they want

to see let's say a reservation details

and the invoice that they paid. So that

is where we will have the get endpoint

to /reservations/

ID of a single reservation which will

fetch the details from the database. we

will get the details back and we will

forward it to the front end and we will

parse and show it to the client. Next,

when creating new reservations, they

will fill the reservation form in the

front end. Then we will send this form

details in a post request to

reservations. One important thing here

is that at this step we can already

prevent double booking for single user.

There are two scenarios like one user

can try to doubleclick or click the same

reservation room twice. So that's one

scenario which we can handle here. The

other one is that two users can try to

book the same room twice. We will handle

that in the later step. But at this step

for example one solution is we can

include a key like a reservation ID and

this will be generated on the front end

which will be used to prevent double

bookings when user clicks submit

multiple times. So if we have the hotel

ID and the room ID that this user is

trying to book basically we generate a

reservation ID once and then if they try

to double book it like book it again we

will be able to catch it in the database

and see that the reservation ID is the

same. So we will block them from making

a request or we can even do it on the

front end. Another solution is you can

also do it on the bacon site. For

example, when they make a request to

book something, you can combine the

hotel ID in combination with room ID.

The user ID who tries to book this and

also the date or some sort of time

stamp. Why? Because in the future this

user might try to book it again. And if

you just use these in combination then

basically you are blocking them to make

a future reservation on the same hotel.

So you can also use these in combination

to generate some sort of hash and

whenever the hash matches from the

database then you block them from making

the reservation. But this is way simpler

solution on the front end side to just

generate some sort of UYU ID uh for the

reservation ID. If you are talking only

about a single user and preventing them

from booking again, then this is a very

lightweight solution and way simpler

than inserting this hash into the

database and checking it every time in

the back end. So here we will first

check for existing reservation ID. If

it's not found, then we will insert the

new reservation. So assuming it's not a

double booking and we get the

reservation confirmation, we will just

show them the confirmation and behind

the scenes the Kafka message will be

published and it will send the

notifications to the user and to the

owner of the hotel. But if it's a

duplicate reservation attempt, then they

will make a request and this will

contain that reservation ID. So we will

check for the existing reservation ID

and if a transaction like that already

happened and we have the reservation and

it already exists then we will return

the existing reservation and show it to

the user that this is already reserved.

Similarly they might want to either

cancel the reservation or change the

dates of the reservation. So we need to

provide endpoints for that as well. In

case of cancelling, it will be a delete

request to reservations slash ID of that

reservation. And in the database, we

just need to check whether it's allowed

to cancel because if they try to cancel

a week before the appointment, then it's

allowed or if they try to cancel it last

minute, then it's probably not allowed.

So in case it is allowed, we will just

send the confirmation. Otherwise we will

send a response that we cannot delete

the reservation meaning we cannot cancel

it and show the options to the user.

Similar to this you might also have edit

reservation with put request which will

update the dates. For example if plans

have changed and you want to update the

dates then we will make a put request to

that ID reservation. Let's also see how

the search will happen. So another

service we will have for this is the

search service which will be connected

with the elastic search. So let's say

user sets the filters like the location

where they want this hotel to be located

at the dates start and end date and so

on and we make a get request to /

search. These will include the location,

the check-in, checkout time, how many

guests you have, how many beds you want,

the price range, and any other necessary

filters. This is where we will query the

available hotels and room types. But an

important thing is we will query this

from the elastic search. In this case,

we won't query it from the database

because it's way faster and that's the

point of us setting up elastic search so

that we can quickly return the search

results. And this will return the

available hotels within the range of

that location and within that price

range and the check-in checkout time and

front end will be parsing and showing it

to the users.

Next, let's move on to the data models

and the database design. One important

thing here is that you book the room

type and not the specific room. So you

book a specific type of room like is it

a double queen bed or something else and

not a specific room because the room

numbers are assigned at the check-in and

not here. But let's start from the

hotels because it will make more sense

this way. So we will have of course the

ID of the hotel which is the indicator

of the unique ID for this hotel. It can

have a name, address, location,

description and so on. Now there might

be also hotels that are under the same

brand name. In this case you can either

have the same hotel name but different

hotel IDs. So it will be a different

hotel LLC in US. Let's say it will be a

different hotel LLC in Europe. The name

will be the same but the ID will be

different. Or you can use a helper table

like hotel groups and then group them

under a brand name if there are brands

that have multiple hotels at different

locations. Then the important part is

the room type that we need to have. So

the ID of the room type, its name,

description and then how many people it

can serve, the ID of the hotel which

connects back to the hotel. So one hotel

can have multiple room types and this is

a one to many relation. Then each room

type is also connected with room type

rate because each of these rooms will

have a different rate. Let's say we have

a room which is the basic package. And

as a basic example, let's say it costs

100 bucks per day. So this is the basic

package. Then you can have a room with a

better view. In this case it will be

something like 150 per day. You can have

a hotel room with breakfast included and

that will be something like 200 and so

on. So that is the reason why we have

this room type rates because for

different room type we will have a

different rate that will be multiplied

by the base uh number that we have here.

And this is again one to many relation

because one room type can have multiple

room type rates. Then this is where we

have the actual rooms. So what is the

room ID? This is the exact room that we

are going to assign at the check-in.

Then it will have some more metadata

like floor number whether it's

available. This is the important part

that will be tracking the status. And

this is also what we are reading when

checking the availability

and the room type ID which again refers

back to the room type. So one room type

can have multiple rooms in it which

means you can have a hotel under that

hotel you have multiple room types like

you have let's say the basic type and in

the basic type you might have three

rooms available that are all basic

rooms. This also connects to the hotel

ID so that you can determine which exact

hotel this belongs to. You could also

join it with the room types and then get

the hotel ID like that. But it will be

helpful for us to have the hotel ID

here. So this will be like which hotel

does it belong to the room that we are

assigning at the check-in. Next we will

have the room type inventory. Inventory

is tracked per hotel, per room type and

day. So not the physical room. This will

enable us to have bulk availability

updates also scalable pricing and search

and it will be simple over booking logic

because we will also support over

booking considering the cancellations

that we will have in the rooms. So here

we will track the room ID type, the

hotel ID and this is again one to many

relationships between the room type and

between the hotel and some more things

like the date, the total inventory,

total reserved and so on. Another

important table here is the reservations

because once user selects the hotel room

specific room we will need to reserve

it. So we will track the reservation ID

which we will link later to the

transactions. We will have the room type

ID. This refers to the room types table.

We will have the exact hotel ID where

they booked this which refers to the

hotels table. The guest ID which is the

user who booked it. So for that we will

have a guests table. And we can

additionally have let's say admins or

brokers table which will be the users

who are publishing these rooms. So here

the most important thing for us is the

guest ID the first name last name email

and so on. So we will connect it with

one to many relationship here because

one user one guest can reserve multiple

hotels. So that's why it is one to many.

Then some more metadata like start date,

end date, status, whether it's reserved,

whether it's canceled and so on. And

another important piece here is that we

will separate transactions into a

separate table. The reason here is that

let's say you reserve the hotel first.

So first the status is reserved. Then

the user can either change their mind

and here they can either cancel the

reservation or they might request a

change in the date. So they might

request to change the start and end

date. In the second case, there will be

a price difference that they will either

have to pay or we have to refund them.

So that will be another transaction,

right? And that's why one reservation

can have multiple transactions. So the

first transaction is when they reserve

and then the second one is when they

decide to change the dates let's say and

update the reservation they make another

payment here which is a second

transaction or if they decide to cancel

that is another transaction where the

amount in this case is minus so we are

refunding the payment but that is still

a separate transaction and that's why we

will have separate transactions table

where we will track the ID of

transaction which reservation Does this

belong to the type of the transaction

like refund, change or the new

transaction and the amount which can be

both positive and a negative number.

With this schema, the room type

inventory table has one row per hotel

room type and date combination. We will

prepopulate this for the next two years

and then a daily chrome job can add new

dates as the time moves forward. So we

can also take a look at some example

data here. We'll have the hotel ID where

these room types belong to. Then the

room type ID. Let's say these three

rooms are from the same type like the

basic type. uh dates where we are

checking these for like December 15, 16,

17 and so on and then we will see the

total inventory and also total reserve

which will give us an idea on how many

rooms we have available. When checking

the availability, if you're using the

SQL database, let's say for December 23

to 27, we will select the date, total

inventory, and total reserved, which is

this column here. And then the total

inventory, which is the available. We

will select it from the room type

inventory where the hotel ID and room

type ID match. And we will set the date

to be between the range that we want to.

This will give us the total reserved and

total inventory. And by using this we

can check that if the total reserve plus

rooms to book is still less than equal

to the total inventory then we allow the

booking. Which means if let's say here

we have 100 and here we have 92. So we

still have eight rooms available. And if

user is trying to book just one room

that will be 92 + 1 and we are checking

this against 100. So the condition will

be true and we will allow the booking.

And coming back to the initial

requirements since we allow let's say

10% over booking or whatever that number

is we can include that over booking

factor here. Let's say if we allow 10%

over booking then we will allow 10% like

set the 10% here. Meaning if we have 100

total inventory available, then we will

allow them to book up to 110 rooms

because we know that the other 10 will

probably be cancelled until the date of

the reservation. And lastly, let's also

discuss why we move with relational

database. In this case, we kind of

touched on this, but let's get a bit

deeper in why we choose this. So first

the most important thing we need here is

the acid guarantees and specifically the

consistency of SQL databases. This is

important for us for preventing double

booking issues which we will handle

soon. From the calculations we can also

see that this is a very read heavy

system. So relational databases handle

these kind of reads well and we can

always combine it with elastic search

for scaling it. But with 1,000 reads

queries per second versus 17 writes per

second, this clearly shows that we need

to go with an SQL database here in

combination with the consistency factor

that we need from the ACIT. And then the

relationships are more clear this way

like between rooms, hotels, the room

types, reservations, transactions and so

on. and we'll need to make complex

queries to check the availability of the

rooms or make multiple joins for

inventory with pricing, filter by dates,

aggregate totals and so on. So SQL will

handle this well as well. We also need

to handle the concurrency. We already

discussed the first scenario where the

same user is trying to book the same

room twice and the solution here is just

to have that reservation key. So any

second request will get duplicate key

error and we will return the existing

reservation. So that's the simple one

that we can solve. But then the second

scenario is we have two different users

who are trying to book the same room

type which is only one spot left for

that hotel. And if you know about the

SQL isolation layers, by default this

will be read committed and concurrency

bugs still happen at read committed

isolation layer which means that you

only see data after other transactions

commit. But there is a gap between when

you read like check the availability and

when you write which is you book the

room. And in that gap you might have two

different transaction happening like

user A reads the total reserved. He sees

that one room is available because the

total is 100. And user B also reads and

sees that there are 99 rooms reserved

and 100 available which means that there

is one available. So for both of them

the check shows successful like the

condition is true which means that we

allow them to proceed with the booking.

So read committed by itself is unsafe

for bookings because two transactions

can both read valid data, pass checks

and then overrite each other and that's

exactly how double bookings happen. As a

solution to this we have two main

approaches in this case. The next phase

is scaling the system because as much as

we designed it with the micros service

architecture and talked about the

numbers that we are going to handle, we

still need to scale the database. Talk

about the caching strategy, optimizing

the elastic search queries and more.

From this point forward, this part is

exclusively for the members in my

mentorship program. If you'd like to get

access to these plus more systems and

participate in discussions live and get

to a point where you can confidently

design such systems from scratch and not

just copy paste or follow what senior

engineers do, then there is a link in

the description which you can check out

for more details to become a member in

the program. See you there.