Designing a Relational Schema for E-commerce: Customers, Products, and Orders

This example will include tables for Customers, Products, Orders, and OrderDetails. Each table will have primary keys, appropriate foreign keys, and some additional constraints to ensure data integrity.

Here’s a description of the database schema:

1. Customers: Stores customer information.
2. Products: Contains details about products.
3. Orders: Records orders placed by customers.
4. OrderDetails: Keeps track of which products are in each order and in what quantity.

SQL Script for Creating Tables

-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL,
    Phone VARCHAR(15),
    PRIMARY KEY (CustomerID)
);

-- Create Products table
CREATE TABLE Products (
    ProductID INT AUTO_INCREMENT,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price > 0),
    Stock INT NOT NULL CHECK (Stock >= 0),
    PRIMARY KEY (ProductID)
);

-- Create Orders table
CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT,
    CustomerID INT,
    OrderDate DATE NOT NULL,
    TotalAmount DECIMAL(10, 2),
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
        ON DELETE SET NULL ON UPDATE CASCADE
);

-- Create OrderDetails table
CREATE TABLE OrderDetails (
    OrderDetailID INT AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    PRIMARY KEY (OrderDetailID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    UNIQUE (OrderID, ProductID)
);

Explanation

Each table is designed to serve a specific function within the database, ensuring data integrity and relationships between different types of data.

1. Customers Table

CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL,
    Phone VARCHAR(15),
    PRIMARY KEY (CustomerID)
);

• CustomerID: This is the primary key for the Customers table. It uniquely identifies each customer. The AUTO_INCREMENT attribute ensures that every new customer gets a unique ID automatically.
• FirstName and LastName: These fields store the first and last names of the customer. They are both required (NOT NULL).
• Email: This is a unique identifier for each customer besides their CustomerID. The UNIQUE constraint prevents two customers from having the same email address, which is important for things like login or contact information.
• Phone: This field is optional as it doesn’t have the NOT NULL constraint. It can store the customer’s phone number.

2. Products Table

CREATE TABLE Products (
    ProductID INT AUTO_INCREMENT,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price > 0),
    Stock INT NOT NULL CHECK (Stock >= 0),
    PRIMARY KEY (ProductID)
);

• ProductID: This is the primary key and is auto-incremented for each new product.
• ProductName: Stores the name of the product. This field is required.
• Price: This must be a positive value (CHECK (Price > 0)), ensuring that no products are entered with a zero or negative price.
• Stock: Indicates the available quantity of the product. The CHECK (Stock >= 0) constraint ensures that stock levels do not fall below zero, which would be nonsensical in a real-world scenario.

3. Orders Table

CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT,
    CustomerID INT,
    OrderDate DATE NOT NULL,
    TotalAmount DECIMAL(10, 2),
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
        ON DELETE SET NULL ON UPDATE CASCADE
);

• OrderID: The primary key for the table, uniquely identifying each order.
• CustomerID: A foreign key linking each order to a customer in the Customers table. If a customer is deleted, the CustomerID in Orders is set to NULL (ON DELETE SET NULL), and updates to CustomerID will be reflected in Orders (ON UPDATE CASCADE).
• OrderDate: The date the order was placed. It is a required field.
• TotalAmount: The total monetary amount of the order.

4. OrderDetails Table

CREATE TABLE OrderDetails (
    OrderDetailID INT AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    PRIMARY KEY (OrderDetailID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    UNIQUE (OrderID, ProductID)
);

• OrderDetailID: Primary key for the table.
• OrderID and ProductID: Foreign keys that link back to the Orders and Products tables respectively. The use of CASCADE for OrderID means that deleting or updating an order will cascade these changes to the OrderDetails. RESTRICT on ProductID prevents deletion of a product that is part of an order.
• Quantity: This is the quantity of the product ordered. It must be more than zero (CHECK (Quantity > 0)).
• UNIQUE (OrderID, ProductID): This constraint ensures that the same product cannot appear more than once in the same order, preventing duplicate entries.

Each table includes constraints and keys designed to maintain data integrity and define the relationships between different entities in the database. This structure helps in managing data accurately and efficiently within an e-commerce context.