System requirements

Functional:

• users can search for reservations
  • date range
  • hotel name
  • room type
• hotels can overbook by 10%. after that, rooms can be booked out
• prices can be dynamic
• pay online

Non-Functional:

• reliability - super high consistency. we need the reserved rooms communicated to be accurate
• semi low latency
• durable - data is persistent even in partial failures

Capacity estimation

Entity Range

• Ok lets assume that our visitor is 1% of the us population of 300 million. thats 3 million users
• our hotel space could be 1 million rooms and 5k hotels

Compute

• lets say at peak we have 10% visitors = 300k visitors on the site
  • lets say that each visitor is searching every minute. thats like 5k qps
• if we have a xlarge instance with 4 cores (each core can conservatively handle 1 k qps) thats like 4k qps per instance. So like 3-4 instances. We can try to have these autoscale.
• the caveat is that traffic pattern is in 3 different types
  • visitors searching
  • visitors reserving, or updating or deleting
• the visitors searching is what we are most concerned after

Storage

• think it shouldnt be too much of a concern
• 1 million rooms with 700 days options thats like 1 kb. 700 GB can fit into one db forsure. (though we want redundancy here too)

API design

Lets classify the CRUD flows

Search

• based on region
• alternatively based on brand, or rating, or review

GET /v1/hotels

Query params:

• checkIn
• checkOut
• lattude&long or address
• brand
• page
• limit

json response

{

hotels:[{id, name, location, rating, base price}]

pagination: {}

}

GET /v1/hotels/

response

{

id

name

description

amenenties

policies

review count

review_previews:[]

}

GET /v1/hotels//rooms

query params:

• checkin
• checkout

response // give back the room tyes

{

rooms:[{id, type, amentities, price, available}]

}

<<<<< Reserve >>>>>

POST /v1/reservations

{

hotelId

roomId

checkIn

checkout

guest_info: {first, last, email,phone}

}

response

201

{

id

status

expiresat

}

Checkout

Idempotency-Key: 123

POST /v1/reservations//payment

{

payment_id:

}

response

201

{

success

}

Database design

Ok lets tackle the database design now. Since we are bounded by the number or reservations in the hotel catalog we aren't worried about unbounded rapid writes. And this seems like a read intensive application. I think that relational would be a good value here.

For example MySQL. WIth its B+ tree, all leaf nodes are on the same level and aid in range queries. Additionally WAL logging ensures durability and in addition to 2PC it ensures atomicity. In a cluster, it has async replication so we need to consider tradeoffs with a highly read heavy app. We can have a buffer on the frontend show n-1 available spots to account for inaccuracy.

Hard to guess hypothetically but we can user cockroachdb if we want strongly consistent reads and distributed nodes.

• but higher latency for simple queries

Since were working with relational lets talk about the db schema

User

• id (pk)
• first name
• last name
• email (unique, index)
• wallet_id

Hotel # for data

• id (pk)
• name
• location (no index if we use a geohash or quad tree)
• images
• star rating
• rating_count
• contact
• attributes-JSON (persiste here as well for resilience, access, and data integrity)

Room # for data

• id
• type
• capacity
• images

Inventory

• id (pk)
• hotel_id (fk)
• room_id (fk)
• date (composite index with room_id)
• price
• available_count

Booking

• id
• state (PENDING, PAID, REFUNDED)
• user_id
• check_in_date
• check_out_date
• payment_id

Reviews

• id
• booking_id
• user_id
• hotel_id

Elasticsearch storage

Comments:

• inventory is tricky because it relies on dates. If you want to find rooms over 4 nights, but only 3 are free then that is not available.
• theres also a caveat for searching. if you want to search on different dimensions. for a small business you do a SQL like query, for medium you maybe have formal text search like postgresql tsearch. but micht as well scale it out to a dedicated cluster or have some enterprise search engine with ML and embedding based search.

High-level design

There is also an optimization component we can do with caching. Obviously we can cache the business and reservation data, but we can use the cache to interact and keep up to data for bookings. Since Redis can do atomic updates. Lets have the key hold inventory counts per day for a room that is queries. This is a write through cache where the redis counter is decremented and then the booking is sent async to a log based queue. The key can be

• hotel_id/room_type/room_category/date -> int.

This will also help with high volume of writes in that the atonomicity of the counter as well with the high throughput of redis can handle, for exampe, 100k qps. To be atomic on both operations we can do a redis decrement, then a queue push. and if that fails rollback to an increment. If the queue push fails worst case we recover and be down 1 free room rather than be up a non-free room.

Request flows

Lets talk about the request flows and how it interacts with the DB.

-> Searching based on location and dates

There is a whole other can of worms with the searching on the map. We can get to that later. Lets just say based on the search query we have a couple of hotel IDs. To hydrate the hotel information from the ids we can do a fetch from the hotel table in the db. It can contain things like CDN image, the name, rating.

query can be something like

SELECT h.* r.*

FROM hotel h

JOIN room r on

JOIN inventory i on

WHERE geohash LIKE 'dr5'%

WHERE inventory date between

WHERE inventory.available_count > 0

-> Searching for rooms on the specific hotel

Chances are that the user is going to click around hotels for dates around that time. So we can cache the previous query per date. For looking at room info, we can do something like

Select r.*

From room r

JOIN inventoy i on

WHERE inventory date between

WHERE inventory available_count > 0

-> Reserving

For the reservation flow we need to have the web app do a couple things. First it should decrement the redis room availability in the cache (if it exists). and in the same block add an entry to a booking log based event queue like kafka. This will 1) update the redis queue so that fetches for dates are accurate. This is helpful specifically on high traffic rooms so that visitors can get latest availability without pounding the database.

The event consumer can process the booking request async things it can do

• update the availability in the db
• create a row thats "RESERVED" state in the DB
• publish events to subscribers for the room (for notifications)

Detailed component design

Dynamic Pricing and Updates

• ok heres a question, can we do dynamic pricing? lets think naively that there is a base pricing per room and that there is a multiplier based on available count. I think that should suffice, if we want to do something more analytical and realtime....

Idempotency

• we can do a couple of things that involve design and business logic
  • we can have the client generate a unique id for each reservation attempt
  • we can also have business logic that prevents a session from reserving more than once
    • this session can be based on something like a generated cookie from the server

Realtime updates

• like mentioned before we have a event queue processing reservations and bookings
• if we have session webhooks or notifications we can have these workers update the subscribers on low inventory

Search by name and category

• we store the categories in the db for durability and source of truth, but we also have the elasticsearch cluster for optimized searching for the user

Trade offs/Tech choices

Explain any trade offs you have made and why you made certain tech choices...

Failure scenarios/bottlenecks

Try to discuss as many failure scenarios/bottlenecks as possible.

Future improvements

What are some future improvements you would make? How would you mitigate the failure scenario(s) you described above?