Creating an Airbnb-like Database: A Learning Journey in Database Design and Implementation

David Patrick Philippe Lupau
Sep 9, 2024
6 min read

May 2024

This project was completed as part of the module Build a Data Mart in SQL in my BSc. Data Science. It allowed me to gain practical experience in database modeling, SQL, and database management systems while working on a real-world inspired project.

Context and problem

The project involved developing a comprehensive database for a platform similar to Airbnb, focusing on the core functionalities of property rental and user management. The aim was to design and implement a robust database capable of handling complex relationships between users (guests and hosts), properties, bookings, and financial transactions. This project required a thorough understanding of relational database design principles, normalization techniques, and SQL to create a system that could efficiently store and retrieve data for an online rental marketplace.

Approach and methodology

The first stage of making a database for a website like Airbnb is all about planning a system that can handle lots of different information neatly and quickly. Such a website and the related database in-volve different layers and types of activities and tasks. The challenge is to design a data management system that is robust, efficient, and tailored to the needs of a dynamic environment. Users should be able find and book places to stay, and property owners can manage their listings, all while the system keeps track of financial transactions and admins help users when they need support.

To solve this, I started by listing the tasks the database should be able to handle. Then I figured out the cornerstones of the system: who the users are, what details about the properties are needed, how bookings work, how to handle money, and how to track help requests. For each part, I thought about what information I need to keep. Then I worked out how these parts connect, for example, how a user's booking relates to a property and how payment for a booking is handled.

When setting up the database, I used joint tables to link information that's related but needs to be flexible. Take, for example, the features of a home, like a pool or Wi-Fi; these are called amenities. A single home can have lots of these amenities, and each amenity can be found in many homes. So, I made a joint table called property_amenities that connects each property to the amenities it offers. This setup makes it really clear which home comes with what features, and it allows any home to have any combination of amenities. It's a simple way to keep track of all the different amenities across all the different properties and retrieve the information to display it to the users.

With all these element, I could build a diagram to show how everything in the database is connected.

Implementation

Based on the entity-relationship model (ERM) designed to capture all necessary data points and relationships, the database schema was constructed in a layered approach:

1. Initial table creation: I started with the creation of simpler tables that did not require foreign keys, ensuring basic entities were defined and ready to interact with more complex tables.

2. Intermediate tables with foreign keys: I then proceeded with the creation of tables that included one foreign key, linking to the initially created tables. This step was essential to start weaving the network of relationships.

3. Core tables development: the following step was to focus on constructing the two pivotal tables (properties and users). These tables are central to the functionality of the platform.

4. Expansion tables: finally, I developed tables that were directly connected to the core properties and users tables. This included tables such as bookings, reviews, and property amenities, which further enriched the database's ability to handle complex queries and operations.

See below examples of SQL queries to create users and properties tables.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone_number VARCHAR(50) NOT NULL,
    address_id INT NOT NULL,
    preferred_currency CHAR(3) NOT NULL,
    password VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    failed_login_attempts INT NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    FOREIGN KEY (address_id) REFERENCES addresses(address_id),
    FOREIGN KEY (preferred_currency) REFERENCES currencies(currency_code)
);

CREATE TABLE properties (
    property_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    address_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    bedrooms INT NOT NULL DEFAULT 1,
    beds INT NOT NULL DEFAULT 1,
    bathrooms INT NOT NULL DEFAULT 1,
    private_bathroom BOOLEAN NOT NULL DEFAULT 0,
    type_id INT NOT NULL,
    surface INT NOT NULL,
    currency_payment CHAR(3) NOT NULL,
    price_per_night DECIMAL(10,2) NOT NULL,
    min_stay INT,
    checkin_time TIME NOT NULL,
    cleaning_fee DECIMAL(10,2),
    pets_allowed BOOLEAN NOT NULL DEFAULT 0,
    house_rules_id INT NOT NULL,
    cancellation_policy_id INT NOT NULL,
    create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT 1,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (address_id) REFERENCES addresses(address_id),
    FOREIGN KEY (type_id) REFERENCES property_types(type_id),
    FOREIGN KEY (currency_payment) REFERENCES currencies(currency_code),
    FOREIGN KEY (house_rules_id) REFERENCES house_rules(house_rule_id),
    FOREIGN KEY (cancellation_policy_id) REFERENCES cancellation_policies(cancellation_policy_id)
);

Following the schema development, dummy data was added to each table in the same phased manner. This approach not only facilitated a systematic verification of each table’s and relationship’s integrity but also prepared the database for a comprehensive set of test cases.

To validate the implementation, test cases that involve more than one table were crafted and executed. These tests were crucial in demonstrating the functionality and integrity of the database, ensuring that relationships were correctly enforced, and that the data could be efficiently retrieved and manipulated as expected.

Example: Guest Alice Johnson (id 25) is seeking to find a property that can accommodate 4 people and a dog, with at least 2 bedrooms, available for a stay from the 4th to the 8th of August.

SELECT p.property_id, p.title, p.description, p.price_per_night, c.currency_code AS currency,
       a.city,
       co.country_name AS country,
       AVG(r.rating) AS average_rating
FROM properties AS p
JOIN currencies AS c ON p.currency_payment = c.currency_code
JOIN addresses AS a ON p.address_id = a.address_id
JOIN countries AS co ON a.country = co.country_code
LEFT JOIN bookings AS b ON p.property_id = b.property_id
LEFT JOIN reviews AS r ON b.booking_id = r.booking_id
WHERE p.beds >= 4
  AND p.is_active = TRUE
  AND p.pets_allowed = TRUE
  AND p.property_id NOT IN (SELECT pa.property_id
                            FROM property_availability AS pa
                            WHERE (pa.starts_date <= '2024-08-04' AND pa.end_date > '2024-08-04')
                               OR (pa.starts_date < '2024-08-08' AND pa.end_date >= '2024-08-08')
                               OR (pa.starts_date >= '2024-08-04' AND pa.end_date <= '2024-08-08'))
GROUP BY p.property_id;

This systematic, layered approach to database construction not only ensured a solid foundation for the Airbnb-like platform but also streamlined the development process, allowing for thorough documentation and testing at each stage. This database is now well-equipped to handle typical operations of an online property rental platform, supporting both the management of property listings and the processing of user interactions comprehensively.

Results

Database overview

Total size: 0.844 Mb

Number of tables: 24

Total number of entries: 714

Key Features

User management: the database stores comprehensive information about users then split into profiles for both guests and hosts, encompassing personal details, contact information, and preferences. Additionally, it securely manages user credentials and facilitates social media integration for enhanced user experience.

Property listings: hosts can create and manage detailed property listings, specifying property types, amenities, house rules, cancellation policies, and availability. This information is crucial for guests to make informed booking decisions.

Booking and availability management: the database efficiently handles bookings, tracking reservation details, guest information, and financial transactions. It also maintains real-time availability calendars to prevent double bookings and ensure accurate occupancy rates.

Financial transactions: securely recording and processing payments, refunds, and commission fees are key tasks. The database provides a transparent record of all financial interactions.

Reviews and ratings: guests and hosts can leave reviews and ratings for each other, fostering trust and transparency within the platform's community. The database aggregates this feedback to showcase property and host reputations.

Communication and support: the platform facilitates communication between guests and hosts through an internal messaging system. Additionally, it manages support tickets to address user inquiries and resolve disputes effectively.

Lesson learned - take-aways

Importance of thorough planning: The time invested in creating a detailed Entity-Relationship Model paid off significantly during the implementation phase, reducing the need for major structural changes later.

Practical SQL skills: Gaining hands-on experience with complex SQL queries, stored procedures, and optimization techniques has greatly enhanced my database management skills.

Real-world application: Working on a project inspired by a real-world platform like Airbnb helped me understand the practical challenges of database design in the industry.

Performance considerations: Learning to balance normalization with performance needs taught me the importance of considering query patterns and data access frequency in database design.

Documentation importance: Maintaining clear documentation throughout the process not only helped in the project's development but also prepared me for real-world scenarios where documentation is crucial for team collaboration and system maintenance.

6. Adaptability: The need to adjust the design as new requirements or challenges emerged taught me the importance of flexibility in database design and development.

Tools used

MySQL Workbench for database modeling and management
DataGrip for SQL script editing
Lucidchart for creating the Entity-Relationship Diagram
Github Desktop version

Project links

This project has been a significant step in my journey towards becoming a proficient database designer and developer. It has reinforced my understanding of relational database concepts and provided me with practical experience that will be invaluable in my future career in data science and software development.

David Lupau