Designing an Online Food Delivery System

Relational Database Design and Modeling for Software Engineers

Ask Author

Back to course home

0% completed

Vote For New Content

Designing an Online Food Delivery System

Table of Contents

Contents are not accessible

An online food delivery system connects customers with restaurants, allowing them to browse menus, place orders, and have food delivered to their doorsteps. Efficient database design ensures that these operations are streamlined, scalable, and reliable.

In this case study, we’ll model an online food delivery system using Entity-Relationship (ER) diagrams and then map it to a relational schema.

Requirements Analysis

For our case study, let's outline the key functionalities that the HMS should support:

Customer Management: Storing customer details and their delivery addresses.
Restaurant Management: Managing restaurant details, menus, and ratings.
Order Processing: Handling orders, payments, and delivery.
Delivery Tracking: Tracking delivery statuses and assigning delivery personnel.
Reviews and Feedback: Allowing customers to leave reviews for restaurants.
Promotions: Managing discounts and promotional offers.

Step-by-Step ER Diagram Creation

We will build the ER diagram for the Hospital Management System through the following four steps.

Step 1: Identify Entities

Entities represent objects or concepts in the system that have data stored about them.

Customer
Address
Restaurant
Menu
Menu_Item
Order
Order_Item
Payment
Delivery_Person
Delivery
Review
Category
Coupon

ER Diagram Entities for Online Food Delivery System

Step 2: Detailing Attributes

1. Customer

Customer_ID (PK)
Full_Name
Email
Phone_Number
Password
Date_Joined

2. Address

Address_ID (PK)
Customer_ID (FK)
Street
City
Postal_Code
Address_Type (Home, Work, etc.)

3. Restaurant

Restaurant_ID (PK)
Name
Location
Contact_Number
Rating

Menu_ID (PK)
Restaurant_ID (FK)

5. Menu_Item

Item_ID (PK)
Menu_ID (FK)
Name
Description
Price
Category_ID (FK)

6. Category

Category_ID (PK)
Name
Description

7. Order

Order_ID (PK)
Customer_ID (FK)
Address_ID (FK)
Order_Date
Total_Amount
Status (Pending, Confirmed, Delivered, Cancelled)

8. Order_Item

Order_Item_ID (PK)
Order_ID (FK)
Item_ID (FK)
Quantity
Subtotal

9. Payment

Payment_ID (PK)
Order_ID (FK)
Payment_Date
Payment_Method (Credit Card, Cash, etc.)
Payment_Status (Paid, Pending, Failed)

10. Delivery_Person

Delivery_Person_ID (PK)
Full_Name
Phone_Number
Vehicle_Details

11. Delivery

Delivery_ID (PK)
Order_ID (FK)
Delivery_Person_ID (FK)
Delivery_Status (Pending, In Transit, Delivered)
Delivery_Date

12. Review

Review_ID (PK)
Customer_ID (FK)
Restaurant_ID (FK)
Rating
Review_Text
Review_Date

13. Coupon

Coupon_ID (PK)
Code
Discount_Percentage
Expiry_Date
Maximum_Usage

Attributes for ER Diagram Entities for Online Food Delivery System

Step 3: Defining Relationships

1. Customer and Address

A customer can have multiple addresses.
Each address belongs to one customer.
Type: One-to-Many (Customer to Address)

2. Restaurant and Menu

A restaurant can have one menu.
A menu belongs to one restaurant.
Type: One-to-One (Restaurant to Menu)

3. Menu and Menu_Item

A menu can have multiple items.
Each item belongs to one menu.
Type: One-to-Many (Menu to Menu_Item)

4. Customer and Order

A customer can place multiple orders.
Each order belongs to one customer.
Type: One-to-Many (Customer to Order)

5. Order and Order_Item

An order can contain multiple items.
Each order item belongs to one order and one menu item.
Type: One-to-Many (Order to Order_Item)

6. Order and Payment

An order can have one payment.
Each payment is linked to one order.
Type: One-to-One (Order to Payment)

7. Order and Delivery

An order can have one delivery.
Each delivery is linked to one order.
Type: One-to-One (Order to Delivery)

8. Delivery and Delivery_Person

A delivery is assigned to one delivery person.
A delivery person can handle multiple deliveries.
Type: One-to-Many (Delivery_Person to Delivery)

9. Restaurant and Review

A restaurant can have multiple reviews.
Each review belongs to one restaurant.
Type: One-to-Many (Restaurant to Review)

10. Customer and Review

A customer can leave multiple reviews.
Each review belongs to one customer.
Type: One-to-Many (Customer to Review)

11. Menu_Item and Category

A menu item belongs to one category.
A category can have multiple items.
Type: Many-to-One (Menu_Item to Category)

12. Order and Coupon

An order can use one coupon.
A coupon can be used in multiple orders.
Type: Many-to-One (Order to Coupon)

13. Menu_Item and Order_Item

A menu item belongs to multiple orders.
An order item can have only one menu item.
Type: Many-to-One (Menu_Item to Order_Item)

Here is the final ER diagram.

ER Diagram For ER Diagram Entities for Online Food Delivery System

Mapping the ER Diagram to a Relational Schema

When converting the ER diagram into a relational schema for our Ecommerce Platform, we need to carefully handle various components to ensure data integrity and optimal performance. Here are the key considerations:

Entity Tables and Attributes:
- Create a table for each entity identified in the ER diagram, such as Customer, Restaurant, etc.
- Define all attributes for each table, specifying appropriate data types and constraints.
- Set primary keys (PK) for unique identification of records in each table.
Foreign Keys and Relationships:
- Establish foreign keys (FK) to represent relationships between entities, linking tables through common attributes.
- Ensure referential integrity by enforcing foreign key constraints, which maintain consistent and valid references between related tables.

Here is the final relational model for the Ecommerce Platform.

Relational Model for Online Food Delivery System

Now, let's translate the ER model into SQL tables.

1. Customer Table

CREATE TABLE Customer (
    Customer_ID INT PRIMARY KEY,
    Full_Name VARCHAR(100),
    Email VARCHAR(100),
    Phone_Number VARCHAR(15),
    Password VARCHAR(50),
    Date_Joined DATE
);

2. Address Table

CREATE TABLE Address (
    Address_ID INT PRIMARY KEY,
    Customer_ID INT,
    Street VARCHAR(255),
    City VARCHAR(50),
    State VARCHAR(50),
    Postal_Code VARCHAR(10),
    Address_Type VARCHAR(20),
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);

3. Restaurant Table

CREATE TABLE Restaurant (
    Restaurant_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Location VARCHAR(255),
    Contact_Number VARCHAR(15),
    Rating DECIMAL(3, 2)
);

CREATE TABLE Menu (
    Menu_ID INT PRIMARY KEY,
    Restaurant_ID INT,
    FOREIGN KEY (Restaurant_ID) REFERENCES Restaurant(Restaurant_ID)
);

5. Menu_Item Table

CREATE TABLE Menu_Item (
    Item_ID INT PRIMARY KEY,
    Menu_ID INT,
    Name VARCHAR(100),
    Description TEXT,
    Price DECIMAL(10, 2),
    Category_ID INT,
    FOREIGN KEY (Menu_ID) REFERENCES Menu(Menu_ID),
    FOREIGN KEY (Category_ID) REFERENCES Category(Category_ID)
);

6. Category Table

CREATE TABLE Category (
    Category_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Description TEXT
);

7. Order Table

CREATE TABLE Order (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT,
    Address_ID INT,
    Order_Date DATE,
    Total_Amount DECIMAL(10, 2),
    Status VARCHAR(20),
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID),
    FOREIGN KEY (Address_ID) REFERENCES Address(Address_ID)
);

8. Order_Item Table

CREATE TABLE Order_Item (
    Order_Item_ID INT PRIMARY KEY,
    Order_ID INT,
    Item_ID INT,
    Quantity INT,
    Subtotal DECIMAL(10, 2),
    FOREIGN KEY (Order_ID) REFERENCES Order(Order_ID),
    FOREIGN KEY (Item_ID) REFERENCES Menu_Item(Item_ID)
);

9. Payment Table

CREATE

 TABLE Payment (
    Payment_ID INT PRIMARY KEY,
    Order_ID INT,
    Payment_Date DATE,
    Payment_Method VARCHAR(50),
    Payment_Status VARCHAR(20),
    FOREIGN KEY (Order_ID) REFERENCES Order(Order_ID)
);

10. Delivery_Person Table

CREATE TABLE Delivery_Person (
    Delivery_Person_ID INT PRIMARY KEY,
    Full_Name VARCHAR(100),
    Phone_Number VARCHAR(15),
    Vehicle_Details VARCHAR(100)
);

11. Delivery Table

CREATE TABLE Delivery (
    Delivery_ID INT PRIMARY KEY,
    Order_ID INT,
    Delivery_Person_ID INT,
    Delivery_Status VARCHAR(20),
    Delivery_Date DATE,
    FOREIGN KEY (Order_ID) REFERENCES Order(Order_ID),
    FOREIGN KEY (Delivery_Person_ID) REFERENCES Delivery_Person(Delivery_Person_ID)
);

12. Review Table

CREATE TABLE Review (
    Review_ID INT PRIMARY KEY,
    Customer_ID INT,
    Restaurant_ID INT,
    Rating INT,
    Review_Text TEXT,
    Review_Date DATE,
    FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID),
    FOREIGN KEY (Restaurant_ID) REFERENCES Restaurant(Restaurant_ID)
);

13. Coupon Table

CREATE TABLE Coupon (
    Coupon_ID INT PRIMARY KEY,
    Code VARCHAR(50),
    Discount_Percentage DECIMAL(5, 2),
    Expiry_Date DATE,
    Maximum_Usage INT
);

.....

Like the course? Get enrolled and start learning!