Designing an E-commerce Platform

Relational Database Design and Modeling for Software Engineers

Ask Author

Back to course home

0% completed

Vote For New Content

Designing an E-commerce Platform

Table of Contents

Contents are not accessible

An e-commerce platform is a digital marketplace where customers can browse, purchase, and review products. The platform involves multiple entities like users, products, orders, payments, and delivery management. Designing a robust database is critical for handling large-scale operations efficiently.

In this case study, we’ll model an e-commerce platform’s database 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:

User Management: Handling customer details and addresses.
Product Catalog: Storing product details, categories, and inventory.
Order Management: Processing orders, payments, and deliveries.
Reviews and Ratings: Allowing customers to leave feedback on products.
Cart Management: Managing items customers add to their carts before purchase.
Payment Processing: Supporting multiple payment methods and tracking transactions.

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.

User
Address
Product
Category
Inventory
Order
Order_Item
Payment
Review
Delivery
Coupon

ER Diagram Entities for an Ecommerce Platform

Step 2: Detailing Attributes

1. User

User_ID (PK)
Full_Name
Email
Phone_Number
Password
Date_Joined

2. Address

Address_ID (PK)
User_ID (FK)
Street
City
State
Country
Postal_Code
Address_Type (Home, Work, etc.)

3. Product

Product_ID (PK)
Name
Description
Price
Category_ID (FK)

4. Category

Category_ID (PK)
Name
Description

5. Inventory

Inventory_ID (PK)
Product_ID (FK)
Quantity_In_Stock
Reorder_Level

6. Order

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

7. Order_Item

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

8. Payment

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

9. Review

Review_ID (PK)
User_ID (FK)
Product_ID (FK)
Rating
Review_Text
Review_Date

10. Delivery

Delivery_ID (PK)
Order_ID (FK)
Delivery_Status (Pending, Shipped, Delivered)
Delivery_Date
Delivery_Partner

11. Coupon

Coupon_ID (PK)
Code
Discount_Percentage
Expiry_Date
Maximum_Usage

Attributes for an Ecommerce Platform

Step 3: Defining Relationships

1. User and Address

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

2. Product and Category

A product belongs to one category.
A category can include multiple products.
Type: Many-to-One (Product to Category)

3. Product and Inventory

A product can have one inventory entry.
Inventory tracks the stock of one product.
Type: One-to-One (Product to Inventory)

4. User and Order

A user can place multiple orders.
Each order is associated with one user.
Type: One-to-Many (User to Order)

5. Order and Order_Item

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

6. Order and Payment

An order can have one payment.
A payment is associated with one order.
Type: One-to-One (Order to Payment)

7. Order and Delivery

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

8. Product and Review

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

9. User and Review

A user can write multiple reviews.
Each review is associated with one user.
Type: One-to-Many (User to Review)

10. Order and Coupon

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

11. Product and Order_Item

An order_item can contain only 1 product.
Each product belongs to multiple order items.
Type: One-to-Many (Product to Order_Item)

Here is the final ER diagram.

ER Diagram for Ecommerce Platform

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 User, Product, Order, 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 Ecommerce Platform

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

1. User Table

CREATE TABLE User (
    User_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,
    User_ID INT,
    Street VARCHAR(255),
    City VARCHAR(50),
    Country VARCHAR(50),
    Postal_Code VARCHAR(10),
    Address_Type VARCHAR(20),
    FOREIGN KEY (User_ID) REFERENCES User(User_ID)
);

3. Product Table

CREATE TABLE Product (
    Product_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description TEXT,
    Price DECIMAL(10, 2),
    Category_ID INT,
    FOREIGN KEY (Category_ID) REFERENCES Category(Category_ID)
);

4. Category Table

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

5. Inventory Table

CREATE TABLE Inventory (
    Inventory_ID INT PRIMARY KEY,
    Product_ID INT,
    Quantity_In_Stock INT,
    Reorder_Level INT,
    FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);

6. Order Table

CREATE TABLE Order (
    Order_ID INT PRIMARY KEY,
    Order_Date DATE,
    User_ID INT,
    Coupon_ID INT
    Total_Amount DECIMAL(10, 2),
    Status VARCHAR(20),
    FOREIGN KEY (User_ID) REFERENCES User(User_ID),
    FOREIGN KEY (Coupon_ID) REFERENCES User(Coupon_ID),
);

7. Order_Item Table

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

8. 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)
);

9. Review Table

CREATE TABLE Review (
    Review_ID INT PRIMARY KEY,
    User_ID INT,
    Product_ID INT,
    Rating INT,
    Review_Text TEXT,
    Review_Date DATE,
    FOREIGN KEY (User_ID) REFERENCES User(User_ID),
    FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);

10. Delivery Table

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

11. 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!

Table of Contents

Contents are not accessible