• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to secondary sidebar

GoHired

Interview Questions asked in Google, Microsoft, Amazon

Join WeekEnd Online Batch from 4-April-2020 on How to Crack Coding Interview in Just 10 Weeks : Fees just 20,000 INR

  • Home
  • Best Java Books
  • Algorithm
  • Internship
  • Certificates
  • About Us
  • Contact Us
  • Privacy Policy
  • Array
  • Stack
  • Queue
  • LinkedList
  • DP
  • Strings
  • Tree
  • Mathametical
  • Puzzles
  • Graph

System Design: Designing a LLD for Hotel Booking

February 3, 2020 by Arshdeep Singh

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!

Similar Articles

Filed Under: Uncategorized

Reader Interactions

Comments

  1. Akash says

    March 1, 2020 at 2:01 pm

    Nice explanation.

Primary Sidebar

Join WeekEnd Online/Offline Batch from 4-April-2020 on How to Crack Coding Interview in Just 10 Weeks : Fees just 20,000 INR

Join WeekEnd Online/Offline Batch from 4-April-2020

WhatsApp us

Secondary Sidebar

Custom Search

  • How I cracked AMAZON
  • LeetCode
  • Adobe
  • Amazon
  • Facebook
  • Microsoft
  • Hacker Earth
  • CSE Interview

Top Rated Questions

C++ OOPs Part2

LeetCode: Container With Most Water

Find an index i such that Arr [i] = i in array of n distinct integers sorted in ascending order.

Python Array String

Find two non repeating elements in an array of repeating elements

Sort Stack in place

Common Ancestor in a Binary Tree or Binary Search Tree

Client Server C program

Search element in a matrix with all rows and columns in sorted order

Mirror of Tree

Longest Increasing Subsequence

Subset Sum Problem Dynamic programming

Printing intermediate Integers between one element & next element of array

Right view of Binary tree

SAP Off Campus Hiring_ March 2015 Sample Questions

TicTacToe Game As Asked in Flipkart

Password Predictor

Puzzle : 100 doors in a row Visit and Toggle the door. What state the door will be after nth pass ?

CodeChef’ RRCOPY

Spanning Tree

Coin Collection Dynamic Programming

SAP Off Campus Hiring_ March 2015 Analytical Aptitude

SAP Interview Questions

Flipkart Set 1 On Campus with Answers

simple sql injection

Reliance Jio Software Developer Interview Experience

Find Nearest Minimum number in left side in O(n)

K’th Largest Element in BST when modification to BST is not allowed

Memory Efficient LinkedList

Doubly linked list

Copyright © 2023 · Genesis Framework · WordPress · Log in