Loading video player...
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.
Become a Senior Software Engineer - https://tinyurl.com/86e7casf This video is a deep dive into designing a highly scalable hotel reservation system, focusing on solving critical senior-level engineering challenges like concurrency, data consistency and preventing double bookings. š Sections 00:00 - Introduction 01:17 - The 4-Step Design Framework 02:04 - Step 1: Clarifying Questions & Functional Requirements 03:39 - Dynamic Pricing & Demand Factors 04:44 - Core Search & Discovery Functionalities 06:13 - Non-Functional Requirements: Consistency & Latency 07:27 - Data Estimation: QPS and TPS 08:43 - Real-world Traffic Funnel Analysis (Booking.com Scale) 13:03 - Step 2: High-Level System Architecture 15:50 - Scaling Search with Elasticsearch 18:41 - Monolith vs. Microservices 20:11 - API Design & Restful Endpoints 25:35 - Preventing Double Bookings 30:37 - Deep Dive into Search Service Logic 31:44 - Database Design & Schema Architecture 35:22 - Managing Room Inventory & Availability 38:50 - Logic for Checking Availability & Overbooking 40:57 - Why SQL? 42:15 - Handling Concurrency & Race Conditions 43:50 - Final Summary & Scaling Challenges