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.

1. User
2. Address
3. Product
4. Category
5. Inventory
6. Order
7. Order_Item
8. Payment
9. Review
10. Delivery
11. Coupon

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

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.

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.

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