Designing an E-commerce Platform

The one rule we are enforcing

Every table we design will obey a single discipline: every non-key column states a fact about the whole key, and nothing but the key. An e-commerce schema — users, addresses, products, categories, inventory, orders, order items, payments, reviews, deliveries, and coupons — is the perfect place to feel why that rule matters, because the natural first draft of an order is a wide, redundant table that violates it in three different ways at once.

We start from that messy wide table, read its functional dependencies, and let the dependencies themselves tell us where to cut. The result is a set of tables in third normal form (3NF), where no fact is stored twice and no update can leave the data contradicting itself.

The wide table and its functional dependencies

Imagine we tried to record every line of every order in one flat table, OrderLines, keyed by the pair (Order_ID, Product_ID) — one row per product within an order:

OrderLines(Order_ID, Product_ID, Order_Date, User_Email, Product_Name, Category_ID, Category_Name, Unit_Price, Quantity)

Reading off the functional dependencies (FDs):

• Order_ID → Order_Date, User_Email — these depend on only the order half of the key.
• Product_ID → Product_Name, Category_ID, Unit_Price — these depend on only the product half of the key.
• (Order_ID, Product_ID) → Quantity — quantity is the one fact that genuinely needs the whole key.
• Category_ID → Category_Name — a non-key column determining another non-key column.

Three distinct violations live here: two partial dependencies (facts about only part of the key) and one transitive dependency (a non-key column driving another). The diagram below colour-codes each edge.

Cutting along the partial dependencies (reaching 2NF)

Second normal form says: no non-key column may depend on only part of a composite key. So we cut OrderLines along the two red edges. Each partial dependency becomes its own table, and the green full dependency stays behind as the true join table.

The User_Email → User_ID swap, made explicit

Here is the subtle move that is easy to do silently and wrong to leave unexplained. The wide table stored User_Email, and the FD list says Order_ID → Order_Date, User_Email. When we lift the order's facts into an Orders table, we do not keep the email there. Email is a fact about the user, not about the order — re-deriving it gives the chain Order_ID → User_ID → User_Email, which is itself a transitive dependency. So Orders stores a User_ID foreign key into Users, and the email lives once in Users. The post-2NF Orders(Order_ID, Order_Date, User_ID) is therefore not a silent rename of User_Email; it is a deliberate replacement of a copied value with a key reference, which is exactly what keeps the email single-sourced.

Two columns that look identical but are not: Unit_Price vs. Subtotal

After the split, Order_Item holds (Order_ID, Product_ID) → Quantity, Unit_Price. Both of those columns sit next to each other and both look like "derivable" money, yet only one of them belongs.

• Unit_Price stays — it is a deliberate snapshot. The product's current price lives in Products.Price and changes over time. The order item must remember what the customer actually paid, which is a genuine fact about that line at purchase time and is not recoverable from any other current column. Keeping it is intentional denormalization for historical correctness, and we flag it as such.
• Subtotal goes — it is pure redundancy. The original draft stored Subtotal DECIMAL(10,2) in Order_Item. But Subtotal = Quantity × Unit_Price: it is a non-key value computed from two other non-key columns in the same row. That directly contradicts our one rule — a non-key column must state a fact about the key, not about its neighbours — and it is a maintenance hazard, since any edit to Quantity or Unit_Price can silently leave Subtotal wrong. We drop it and compute it on read (SELECT Quantity * Unit_Price AS Subtotal) or in a view.

The contrast is the whole lesson: snapshotting a value that the system would otherwise lose is justified; storing a value the system can always recompute is not. Total_Amount on Orders is the same judgement call — we keep it deliberately as the authoritative charged total (after coupon), the way Unit_Price is kept, rather than silently as a sum.

The full schema in 3NF

Applying the same FD reading to every entity gives eleven tables. Tables are declared in dependency order so that each foreign key references a table that already exists. User and Order are reserved words, so they are quoted. Note the three things the reviewers asked for: the Subtotal column is gone from Order_Item (only the Unit_Price snapshot remains), Orders carries a Coupon_ID foreign key matching the prose, and the Payment, Review, and Delivery tables are all present and complete.

-- Reference / parent tables first
CREATE TABLE "User" (
    User_ID       INT PRIMARY KEY,
    Full_Name     VARCHAR(100),
    Email         VARCHAR(100) UNIQUE,
    Phone_Number  VARCHAR(15),
    Password_Hash VARCHAR(255),
    Date_Joined   DATE
);

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

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

CREATE TABLE Address (
    Address_ID   INT PRIMARY KEY,
    User_ID      INT,
    Street       VARCHAR(255),
    City         VARCHAR(50),
    State        VARCHAR(50),
    Country      VARCHAR(50),
    Postal_Code  VARCHAR(10),
    Address_Type VARCHAR(20),
    FOREIGN KEY (User_ID) REFERENCES "User"(User_ID)
);

CREATE TABLE Inventory (
    Inventory_ID      INT PRIMARY KEY,
    Product_ID        INT UNIQUE,       -- one inventory row per product (1:1)
    Quantity_In_Stock INT,
    Reorder_Level     INT,
    FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);

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

-- Orders reference User, Address and Coupon
CREATE TABLE "Order" (
    Order_ID     INT PRIMARY KEY,
    Order_Date   DATE,
    User_ID      INT,                -- FK replaces the copied User_Email
    Address_ID   INT,
    Coupon_ID    INT,                -- nullable: an order may carry a coupon
    Total_Amount DECIMAL(10, 2),     -- authoritative charged total (post-coupon)
    Status       VARCHAR(20),
    FOREIGN KEY (User_ID)    REFERENCES "User"(User_ID),
    FOREIGN KEY (Address_ID) REFERENCES Address(Address_ID),
    FOREIGN KEY (Coupon_ID)  REFERENCES Coupon(Coupon_ID)
);

CREATE TABLE Order_Item (
    Order_Item_ID INT PRIMARY KEY,
    Order_ID      INT,
    Product_ID    INT,
    Quantity      INT,
    Unit_Price    DECIMAL(10, 2),    -- price snapshot at purchase time (kept)
    -- Subtotal is intentionally NOT stored: it equals Quantity * Unit_Price
    FOREIGN KEY (Order_ID)   REFERENCES "Order"(Order_ID),
    FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);

CREATE TABLE Payment (
    Payment_ID     INT PRIMARY KEY,
    Order_ID       INT UNIQUE,       -- one payment per order (1:1)
    Payment_Date   DATE,
    Payment_Method VARCHAR(50),
    Payment_Status VARCHAR(20),
    FOREIGN KEY (Order_ID) REFERENCES "Order"(Order_ID)
);

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

CREATE TABLE Delivery (
    Delivery_ID      INT PRIMARY KEY,
    Order_ID         INT UNIQUE,     -- one delivery per order (1:1)
    Delivery_Status  VARCHAR(20),
    Delivery_Date    DATE,
    Delivery_Partner VARCHAR(100),
    FOREIGN KEY (Order_ID) REFERENCES "Order"(Order_ID)
);

This script was validated end to end in SQLite: every table creates, and every foreign key resolves because parents are declared before children. Subtotal appears nowhere; the only retained "derivable-looking" columns are the two we justified as snapshots — Order_Item.Unit_Price and Order.Total_Amount.

Source

Adapted and corrected from the Knowledge Guide lesson "Designing an E-commerce Platform" (Databases → Normalization, lesson 013), restructured around the functional-dependency-driven path to third normal form. Normal-form definitions follow the standard treatment in Elmasri & Navathe, Fundamentals of Database Systems (7th ed.), and Silberschatz, Korth & Sudarshan, Database System Concepts (7th ed.). DDL verified against SQLite 3.51.0.