System Design: Designing a LLD for Hotel Booking

Hotel Reservation system such as Oyo, Trivago, Hotels.com etc is a really big project, considering different aspects of System Design. The main challenges included in that is to design HLD of the whole system which might include tasks such as Hotel Booking, Price Comparison, Ratings etc. along with LLD for each of the sub system.

In this article we will discuss about Low Level Design of one such particular system, Hotel Booking.

The main challenge for this LLD is:

Reservation of room/rooms for a particular system should happen.

One of the most popular design patterns which are really popular these days is Object Relational Mapping (ORM). In ORM, the class structure that we use is based on how we have used the DB structure. This promotes rationality between data and design.

Database Schema

So, let’s first understand how to make the database schema:

The main thing to notice is that for a particular transaction(booking of a room), the following database tables should be present:

1. Guests / User: A room can only be booked if a user/guest is booking it.
2. Room: A guest books a particular room. 
3. Hotel: All rooms must belong to some hotel.
4. Reservation: All reservations should also be stored
5. Payment: For each reservation some sort of payment should be done. The following can be the modes:
   1. Partial
   2. Full
   3. No advance

Now lets see each of the db description in details:

User:

The user db should include user details such as user_id, first name, last name, location and phone number. With each entry, corresponds a particular data type.

Room:

A room has its room_id, hotel_id to which it belongs to, its capacity and its prizes. Also there are some particular facilities which can be associated with the room such as wifi, heater, etc. The following can be DB Schema for Room:

Hotel:

A hotel should have a hotel_id, hotel_name, its location and owner details. All rooms belong to a particular hotel. We can also keep a list of rooms a hotel have, but that is redundant information.

Reservation:

Here each reservation is a combination of a user booking a particular room with given check in and check out timings and payment details. We also have to take a design decision that should we keep all the payment transactions in a Reservation as a list, or should make a separate table to associate a reservation_id with multiple tuples for each payment transaction. The latter follows the Normalization rule for SQL (read more here) while the former reduces some query processing. Let’s choose the latter here! We should also keep the balance amount in the schema.

Transaction:

Now each payment entry represents a particular amount being transferred along with its transaction_id and type of method used.

Payment:

Each tuple here connects a particular booking_id with its associated transaction_id.

Now lets see how these schemas connect with each other:

Also notice the relationship that each table schema follows with each other:

• reservation <-> user (1:1)
• room <-> hotel (many:1)
• reservation <-> payment (1:many)
• payment <-> transaction (1:1)
• reservation <-> room (1:many)
• reservation <-> hotel (1:1)

Try to analyze each of the relation type and why such a combination is choosen.

Followups

Usually after this point in the interview, the interview might divert into a particular side or a question like:

Booking systems always book for a particular room type and not a particular room because multiple rooms can have similar room type and anyone of them can be given to the guest during the time of check in.

Now let’s modify the schema according to this particular use case. Here the formation of a new table room_type can be used which describes its capacity, all the facilities a room have such as wifi, breakfast, AC etc. Lets select 5 of them as main types and make them as columns itself and rest and rest as other facilities. Now with each room_type we can associate different room_ids in a separate table. The new two tables formed will be:

Now, the booking system will also change and now reservation will now be based upon room_type rather than room itself, so new schema will be:

Now, in similar ways there can multiple follow ups such as:

• Modify the system in such a way that you can accept cancellations based on different refund policies.
• How to further handle concurrent queries on the booking? Will you use locks here?
• Which Transactions will require lock ? Concurrent request is not handled with lock then explain how it will be handles with Queue .

Some Additional Information

System Design interviews are mostly subjective to what role you are applying for? If you are applying for SDE-1/SDE-2, an HLD/LLD along with some information on distributed systems is usually required. But for higher posts, you are expected to list details for HLD as well as LLD. Also for all cases, discuss with your interviewer all the pros and cons of each and every design decision you make and assumptions you assume.

You can also check out the video here:

Check out other interview questions here.
How to crack Coding interviews in just 10 weeks by GoHired

Best of luck in your preparations!