Design OYO-Airbnb Part 1 - Database Modelling

Features Included:

1. User Authentication: Secure login and registration.

2. Room Listings: Hosts can list their rooms with details such as location, amenities, and pricing.

3. Bookings: Users can book rooms for specific dates.

4. Reviews and Ratings: Users can review and rate rooms and hosts.

5. Messaging: Communication between hosts and guests.

6. Search Functionality: Users can search for available rooms based on location, dates, and amenities.

7. Transaction Processing: Handling room reservations and payments.

8. Dynamic Pricing of Rooms: Room prices can change based on demand, season, etc.

9. Multi-Currency Support: Room pricing can be in different currencies.

10. Inventory Management: Manage the availability of rooms based on bookings.

Entity/Table with Detailed Info about Every Value

1. Users Table:

   • user_id (Primary Key): Unique identifier for each user.

   • username: Username chosen by the user.

   • email: User's email address.

   • password_hash: Encrypted password.

   • created_at: Timestamp of user account creation.

2. Rooms Table:

   • room_id (Primary Key): Unique identifier for each room.

   • host_id (Foreign Key): User ID of the host.

   • location: Location of the room.

   • amenities: Amenities offered by the room.

   • base_price_per_night: Base price per night for the room in the default currency.

   • currency: Currency of the base price.

   • created_at: Timestamp of when the room was listed.

3. DynamicPricing Table:

   • pricing_id (Primary Key): Unique identifier for each pricing entry.

   • room_id (Foreign Key): Room ID.

   • start_date: Start date for the dynamic pricing.

   • end_date: End date for the dynamic pricing.

   • price_per_night: Dynamic price per night for the specified date range.

   • currency: Currency of the dynamic price.

4. Bookings Table:

   • booking_id (Primary Key): Unique identifier for each booking.

   • room_id (Foreign Key): Room ID of the booked room.

   • guest_id (Foreign Key): User ID of the guest.

   • check_in_date: Check-in date for the booking.

   • check_out_date: Check-out date for the booking.

   • total_price: Total price for the booking in the currency of the room.

   • currency: Currency of the total price.

   • created_at: Timestamp of when the booking was made.

5. Reviews Table:

   • review_id (Primary Key): Unique identifier for each review.

   • room_id (Foreign Key): Room ID being reviewed.

   • user_id (Foreign Key): User ID of the reviewer.

   • rating: Rating given by the user.

   • comment: Review comment.

   • created_at: Timestamp of when the review was created.

6. Messages Table:

   • message_id (Primary Key): Unique identifier for each message.

   • sender_id (Foreign Key): User ID of the sender.

   • receiver_id (Foreign Key): User ID of the receiver.

   • content: Message content.

   • created_at: Timestamp of when the message was sent.

7. Currencies Table:

   • currency_code (Primary Key): Currency code (e.g., USD, EUR).

   • exchange_rate_to_base: Exchange rate to convert to the base currency.

Details about 1-NF, 2-NF, etc., of Each Table in Tabular Format with Explanation Details

Table	1-NF	2-NF	3-NF	Explanation
Users	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by user_id. Non-key attributes (username, email, password_hash, created_at) depend solely on the primary key. No transitive dependencies.
Rooms	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by room_id. Non-key attributes (host_id, location, amenities, base_price_per_night, currency, created_at) depend solely on the primary key. No transitive dependencies.
DynamicPricing	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by pricing_id. Non-key attributes (room_id, start_date, end_date, price_per_night, currency) depend solely on the primary key. No transitive dependencies.
Bookings	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by booking_id. Non-key attributes (room_id, guest_id, check_in_date, check_out_date, total_price, currency, created_at) depend solely on the primary key. No transitive dependencies.
Reviews	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by review_id. Non-key attributes (room_id, user_id, rating, comment, created_at) depend solely on the primary key. No transitive dependencies.
Messages	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by message_id. Non-key attributes (sender_id, receiver_id, content, created_at) depend solely on the primary key. No transitive dependencies.
Currencies	Yes	Yes	Yes	All attributes are atomic and each record is uniquely identified by currency_code. Non-key attributes (exchange_rate_to_base) depend solely on the primary key. No transitive dependencies.

All Queries Required for Applications

1. Retrieve available rooms for a given location and date range:

   SELECT r.*
   FROM Rooms r
   LEFT JOIN Bookings b ON r.room_id = b.room_id
      AND :check_in_date < b.check_out_date
      AND :check_out_date > b.check_in_date
   WHERE r.location = :location
   AND b.booking_id IS NULL;
   

2. Retrieve dynamic pricing for a room and date range:

   SELECT dp.*
   FROM DynamicPricing dp
   WHERE dp.room_id = :room_id
   AND dp.start_date <= :check_in_date
   AND dp.end_date >= :check_out_date;
   

3. Retrieve bookings for a specific user:

   SELECT * FROM Bookings
   WHERE guest_id = :user_id;
   

4. Retrieve reviews for a specific room:

   SELECT * FROM Reviews
   WHERE room_id = :room_id;
   

5. Retrieve messages between two users:

   SELECT * FROM Messages
   WHERE (sender_id = :user_id1 AND receiver_id = :user_id2)
      OR (sender_id = :user_id2 AND receiver_id = :user_id1);
   

6. Convert room pricing to a different currency:

   SELECT r.*, r.price_per_night * c.exchange_rate_to_base AS price_in_target_currency
   FROM Rooms r
   JOIN Currencies c ON r.currency = c.currency_code
   WHERE r.room_id = :room_id
   AND c.currency_code = :target_currency;
   

All Required Indexing or Background Jobs

1. Indexing:

   • Index on location in the Rooms table for efficient location-based searches:

     CREATE INDEX idx_location ON Rooms (location);
     

   • Index on room_id in the Bookings table for optimizing room availability queries:

     CREATE INDEX idx_room_id ON Bookings (room_id);
     

   • Index on check_in_date and check_out_date in the Bookings table for optimizing date range queries:

     CREATE INDEX idx_booking_dates ON Bookings (check_in_date, check_out_date);
     

   • Index on user_id in the Reviews table for fast review retrieval:

     CREATE INDEX idx_user_id ON Reviews (user_id);
     

   • Index on sender_id and receiver_id in the Messages table for efficient message retrieval:

     CREATE INDEX idx_sender_receiver ON Messages (sender_id, receiver_id);
     

   • Index on room_id, start_date, and end_date in the DynamicPricing table for efficient dynamic pricing queries:

     CREATE INDEX idx_dynamic_pricing ON DynamicPricing (room_id, start_date, end_date);
     

2. Background Jobs:

   • Regularly update room availability based on bookings and check-out dates.

   • Periodic maintenance tasks to analyze and optimize query performance.

   • Background tasks for handling transactions and updating user-related statistics.

   • Regularly update currency exchange rates in the Currencies table.

Query Plan Optimization

1. Regularly Review and Optimize:

   • Periodically review and optimize the execution plans of the search queries using tools like EXPLAIN in MySQL.

2. Database Profiling:

   • Use a database profiler to identify slow-performing queries and optimize accordingly.

3. Caching:

   • Implement caching for frequently accessed data to reduce database load.

4. Denormalization:

   • Consider denormalizing data for frequently accessed information, such as precomputed availability for popular locations.

5. Partitioning:

   • Partition large tables based on criteria such as location or date to improve query performance.

6. Load Balancing:

   • Distribute the database load using load balancing techniques to handle high traffic efficiently.

We have launch a LLD platform code.lldcoding.com where you can practice LLD question. For now "Design pizza builder" question is working". you can practice there.