Database Design for a food delivery app like Zomato/Swiggy

After reading my last blog on Database Design of systems like LinkedIn. I hope you got an understanding of the basic building of database designing or data modeling.

Today in this blog I will discuss one more real-life interview problem. Let’s design the database for a food delivery app like Zomato or Swiggy.

Let’s start with all the required tables in the database design.

Here is a basic database design for a delivery app like Zomato:

1. Users: This table will store information about app users, including their name, email address, password, phone number, and delivery address.
2. Restaurants: This table will store information about restaurants, including their name, address, phone number, and menu items.
3. Orders: This table will store information about orders, including the user who placed the order, the restaurant from which the order was placed, the menu items included in the order, the order total, and the delivery status of the order.
4. Drivers: This table will store information about drivers, including their names, phone numbers, and current locations.
5. Payment: This table will store information about payments, including the payment method used (e.g. credit card, cash), the amount paid, and the status of the payment (e.g. pending, paid, refunded).
6. Rating: This table will store information about user ratings, including the user who left the rating, the restaurant that is rated, and the rating (e.g. 1–5 stars).

These are the main tables you’ll need to create a functional delivery app. You may also need to create additional tables to store other information, such as information about promotions, discounts, and other types of deals. But initially let’s consider only these tables.

Let’s Create all the Relationships between the tables.

Here are the relationships between the tables in the database design:

1. Users — Orders: A user can place multiple orders, and each order is placed by a single user. This is a one-to-many relationship.
2. Restaurants — Orders: A restaurant can have multiple orders placed for it, and each order is placed at a single restaurant. This is a one-to-many relationship.
3. Orders — Payment: Each order will have a single payment associated with it, and each payment is for a single order. This is a one-to-one relationship.
4. Orders — Drivers: Each order will have a single driver associated with it, and each driver is assigned to multiple orders.
5. Users — Rating: A user can rate multiple restaurants, and each restaurant can be rated by multiple users. This is a many-to-many relationship.
6. Restaurants — Rating: A restaurant can be rated multiple times by different users, and each rating belongs to a single restaurant. This is a one-to-many relationship.
7. Users — Address: A user can have multiple delivery addresses, and each address belongs to a single user. This is a one-to-many relationship.
8. Restaurants — Menu: A restaurant can have multiple menu items, and each menu item belongs to a single restaurant. This is a one-to-many relationship.

This is a basic relationship for a delivery app like Zomato, you may need to add or update the relationship based on your requirement.

Let’s create the structures of all the required tables.

1. Users:

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    UNIQUE (email)
);

2. Restaurants :

CREATE TABLE Restaurants (
    restaurant_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL
);

3. Orders:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    restaurant_id INT NOT NULL,
    order_total DECIMAL(10,2) NOT NULL,
    delivery_status VARCHAR(20) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id)
);

4. Drivers:

CREATE TABLE Drivers (
    driver_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    location VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

5. Payment:

CREATE TABLE Payment (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    payment_method VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

6. Rating:

CREATE TABLE Rating (
    rating_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    restaurant_id INT NOT NULL,
    rating INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id)
);

7. Address:

CREATE TABLE Address (
    address_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    state VARCHAR(255) NOT NULL,
    city  VARCHAR(255) NOT NULL,
    street VARCHAR(255) NOT NULL,
    pincode INT NOT NULL
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

8. Menu:

CREATE TABLE Menu (
    menu_id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (restaurant_id)

Let’s create the data model based on the above tables:

Data Model

The above data model consists of several entities:

• Users: This entity represents the users of the app, who can place orders and make payments.
• Restaurants: This entity represents the restaurants that are registered with the app.
• Orders: This entity represents the orders placed by users. It contains information about the user, the restaurant, the order total, and the delivery status.
• Drivers: This entity represents the drivers who are responsible for delivering the orders.
• Payment: This entity represents the payment made by the users for their orders. It contains information about the order, the payment method, the amount, and the status.
• Rating: This entity represents the ratings given by the users to the restaurants.
• Address: This entity represents the addresses of the users.
• Menu: This entity represents the menu of the restaurants.

There are relationships between these entities, such as the “user_id” in the “Orders” table being a foreign key referencing the “user_id” in the “Users” table, and the “restaurant_id” in the “Orders” table is a foreign key referencing the “restaurant_id” in the “Restaurants” table.

This is just an example, and you can add or remove entities or attributes to the data model based on your specific requirements.

Let’s create some SQL Queries to retrieve the data from the Data model that we build so far.

1. To select the name and email of all users who have placed an order:

SELECT Users.name, Users.email
FROM Users
JOIN Orders ON Users.id = Orders.user_id;

2. To select the name and address of all restaurants that have received a rating of 4 or higher:

SELECT Restaurants.name, Restaurants.address
FROM Restaurants
JOIN Rating ON Restaurants.id = Rating.restaurant_id
WHERE Rating.rating >= 4;

3. To select the order id, total, and status of all orders that have been placed by a user with the email ‘johndoe@example.com’:

SELECT Orders.id, Orders.total, Orders.status
FROM Orders
JOIN Users ON Orders.user_id = Users.id
WHERE Users.email = 'johndoe@example.com';

4. To select the name, phone, and rating of all drivers who have delivered an order:

SELECT Drivers.email, Drivers.phone, Orders.rating
FROM Drivers
JOIN Orders ON Drivers.id = Orders.driver_id
JOIN Rating ON Orders.id = Rating.order_id;

5. To select the name, price and status of the payment of all menu items on a particular restaurant:

SELECT Menu.name, Menu.price, Payment.status
FROM Menu
JOIN Payment ON Menu.restaurant_id = Payment.restaurant_id
WHERE Menu.restaurant_id = 1;

6. To select all the addresses of the user who placed a particular order:

SELECT Address.street, Address.city, Address.state, Address.pincode
FROM Address
JOIN Orders ON Address.user_id = Orders.user_id
WHERE Orders.id = 1;

Let’s create some more examples of complex SQL queries that use multiple joins and advanced features such as subqueries, group by, and having clause:

1. To select the average rating for each restaurant, along with the number of ratings they have received:

SELECT Restaurants.name, AVG(Rating.rating) as avg_rating, COUNT(Rating.rating) as rating_count
FROM Restaurants
JOIN Rating ON Restaurants.id = Rating.restaurant_id
GROUP BY Restaurants.name;

2. To select the most popular menu items, based on the number of times they have been ordered:

SELECT Menu.name, COUNT(Orders.menu_id) as times_ordered
FROM Menu
JOIN Orders ON Menu.id = Orders.menu_id
GROUP BY Menu.name
ORDER BY times_ordered DESC
LIMIT 10;

3. To select the names and emails of users who have placed orders from a particular restaurant, along with the total cost of their orders:

SELECT Users.first_name, Users.last_name, Users.email, SUM(Orders.total) as total_spent
FROM Users
JOIN Orders ON Users.id = Orders.user_id
JOIN Restaurants ON Orders.restaurant_id = Restaurants.id
WHERE Restaurants.name = 'Pizza Palace'
GROUP BY Users.email;

4. To select the name and phone number of drivers who have delivered more than 20 orders:

SELECT Drivers.name, Drivers.phone
FROM Drivers
JOIN Orders ON Drivers.id = Orders.driver_id
GROUP BY Drivers.id
HAVING COUNT(Orders.id) > 20;

5. To select the name and phone number of drivers who have delivered more than 20 orders and have a rating of 4 or higher:

SELECT Drivers.name, Drivers.phone
FROM Drivers
JOIN Orders ON Drivers.id = Orders.driver_id
JOIN Rating ON Orders.id = Rating.order_id
GROUP BY Drivers.id
HAVING COUNT(Orders.id) > 20 AND AVG(Rating.rating) >= 4;

6. To select the names of the restaurants that have not received any orders from a particular user:

SELECT Restaurants.name
FROM Restaurants
WHERE Restaurants.id NOT IN (
    SELECT DISTINCT Orders.restaurant_id
    FROM Orders
    JOIN Users ON Orders.user_id = Users.id
    WHERE Users.email = 'johndoe@example.com'
);

Note that the above queries are examples and may need to be modified to fit the specific needs of your application.

Reach out to me

If that article got you interested in reaching out to me, then this section is for you. You can reach out to me on LinkedIn, or If you’d like to discuss more such interview problems, text me on LinkedIn…

Let’s connect on LinkedIn:

https://www.linkedin.com/in/ayush-dixit-dataengineer/