Building an Order Fulfilment Database for an E-Commerce Platform

1. categories and brands

CREATE TABLE categories (
    category_id   INT NOT NULL,
    category_name VARCHAR(64) NOT NULL,
    CONSTRAINT categories_PK PRIMARY KEY (category_id),
    CONSTRAINT category_name_unique UNIQUE (category_name)
);

CREATE TABLE brands (
    brand_id   INT NOT NULL,
    brand_name VARCHAR(64) NOT NULL,
    CONSTRAINT brands_PK PRIMARY KEY (brand_id),
    CONSTRAINT brand_name_unique UNIQUE (brand_name)
);

These are simple lookup tables that exist to avoid repeating string values inside the products table. Each category and brand name is stored only once and referenced by a foreign key. This is a classic example of database normalisation specifically Third Normal Form (3NF), where repeated non-key data is extracted into its own table.

The UNIQUE constraints on category_name and brand_name ensure there are no duplicates (e.g., two records both called "Electronics").

2. products

CREATE TABLE products (
    product_id          INT NOT NULL,
    category_id         INT NOT NULL,
    brand_id            INT NOT NULL,
    product_name        VARCHAR(255) NOT NULL,
    product_description VARCHAR(255) NOT NULL,
    product_price       DECIMAL(10,2) NOT NULL,
    product_image       VARCHAR(255) NOT NULL,
    product_pack_size   INT NOT NULL,
    product_weight      DECIMAL(8,2) NOT NULL,
    product_origin      VARCHAR(32) NOT NULL,
    product_added_date  TIMESTAMP NOT NULL,
    CONSTRAINT products_PK PRIMARY KEY (product_id),
    CONSTRAINT products_categories_FK FOREIGN KEY(category_id)
        REFERENCES categories(category_id) ON DELETE SET NULL,
    CONSTRAINT products_brands_FK FOREIGN KEY(brand_id)
        REFERENCES brands(brand_id) ON DELETE SET NULL,
    CONSTRAINT product_price_check CHECK (product_price > 0),
    CONSTRAINT product_pack_size_check CHECK (product_pack_size > 0),
    CONSTRAINT product_weight_check CHECK (product_weight > 0)
);

The products table is the product catalogue at the heart of the system. A few design decisions stand out:

• DECIMAL(10,2) for price ensures monetary values are stored precisely, avoiding the floating-point rounding errors you'd get with FLOAT.
• ON DELETE SET NULL on the foreign keys to categories and brands means that deleting a category or brand won't cascade and destroy all associated product records it simply nullifies the reference.
• CHECK constraints enforce that price, pack size, and weight can never be zero or negative basic but important business rules enforced at the database level.

3. warehouses and postcode_areas

CREATE TABLE warehouses (
    warehouse_id       VARCHAR(10) NOT NULL,
    warehouse_location VARCHAR(255) NOT NULL,
    CONSTRAINT warehouse_PK PRIMARY KEY(warehouse_id)
);

CREATE TABLE postcode_areas (
    postcode_area    VARCHAR(5) NOT NULL,
    postcode_location VARCHAR(64) NOT NULL,
    CONSTRAINT postcode_areas_PK PRIMARY KEY(postcode_area),
    CONSTRAINT postcode_area_check CHECK (LENGTH(postcode_area) >= 2)
);

These two tables represent the geographic layer of the database. Warehouses are the physical fulfilment centres. Postcode areas are UK postcode prefixes (e.g., SW1, M1, EH4) that map to geographic regions.

Note that warehouse_id uses a VARCHAR(10) rather than an auto-incrementing integer this allows for human-readable codes like WH-LON-01, which is more practical in a logistics context.

4. postcode_area_coverage The Many-to-Many Bridge

CREATE TABLE postcode_area_coverage (
    postcode_area VARCHAR(5) NOT NULL,
    warehouse_id  VARCHAR(10) NOT NULL,
    delivery_time INT,
    CONSTRAINT postcode_area_coverage_PK PRIMARY KEY (postcode_area, warehouse_id),
    CONSTRAINT postcode_areas_warehouses_FK FOREIGN KEY (postcode_area)
        REFERENCES postcode_areas(postcode_area) ON DELETE CASCADE,
    CONSTRAINT postcode_areas_warehouses_FK2 FOREIGN KEY (warehouse_id)
        REFERENCES warehouses(warehouse_id) ON DELETE CASCADE,
    CONSTRAINT delivery_time_check CHECK (delivery_time >= 12)
);

This is one of the most interesting tables in the schema. It solves the many-to-many relationship between warehouses and postcode areas: a warehouse can cover many postcode areas, and a postcode area can be covered by multiple warehouses.

The composite primary key (postcode_area, warehouse_id) elegantly enforces uniqueness you can not have duplicate entries for the same warehouse-postcode pair.

Most importantly, this table includes a delivery_time attribute — how many hours it takes for that warehouse to deliver to that postcode area. The CHECK (delivery_time >= 12) constraint establishes a minimum 12-hour delivery window. This is the engine behind the system's ability to estimate delivery times for each order.

5. customers, customer_addresses, and customer_payment_methods

These three tables model the customer side of the system. Addresses and payment methods are stored in separate tables, allowing a single customer to have multiple saved delivery addresses and payment cards exactly as you'd expect from a modern e-commerce platform. This avoids embedding repeated data into the customers table and cleanly supports one-to-many relationships.

6. orders

CREATE TABLE orders (
    order_id           INT NOT NULL,
    customer_id        INT NOT NULL,
    address_id         INT,
    payment_method_id  INT,
    order_status       VARCHAR(32) NOT NULL,
    order_date         TIMESTAMP NOT NULL,
    CONSTRAINT orders_PK PRIMARY KEY (order_id),
    CONSTRAINT orders_customers_FK FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id) ON DELETE CASCADE,
    CONSTRAINT orders_customer_addresses_FK FOREIGN KEY (address_id)
        REFERENCES customer_addresses(address_id) ON DELETE SET NULL,
    CONSTRAINT orders_customer_payment_methods_FK FOREIGN KEY (payment_method_id)
        REFERENCES customer_payment_methods(payment_method_id) ON DELETE SET NULL
);

The orders table ties a customer, their delivery address, and their payment method together into a single transactional record. The order_status field (e.g., "pending", "dispatched", "delivered") tracks where the order is in the fulfilment pipeline.

The mix of ON DELETE CASCADE (for customer) and ON DELETE SET NULL (for address and payment method) is deliberate: if a customer is deleted, all their orders should go too. But if a customer removes a saved address or card, the historical order records should remain intact.

7. stock_items The Heart of the Fulfilment Logic

CREATE TABLE stock_items (
    stock_id        INT NOT NULL,
    product_id      INT NOT NULL,
    warehouse_id    VARCHAR(10) NOT NULL,
    stock_location  VARCHAR(64) NOT NULL,
    stock_status    INT NOT NULL,
    order_id        INT,
    stock_added_date TIMESTAMP NOT NULL,
    CONSTRAINT stock_items_PK PRIMARY KEY(stock_id),
    CONSTRAINT stock_items_products_FK FOREIGN KEY(product_id)
        REFERENCES products(product_id) ON DELETE SET NULL,
    CONSTRAINT stock_items_warehouses_FK FOREIGN KEY(warehouse_id)
        REFERENCES warehouses(warehouse_id) ON DELETE SET NULL,
    CONSTRAINT stock_items_orders_FK FOREIGN KEY(order_id)
        REFERENCES orders(order_id) ON DELETE CASCADE
);

This is the most logistically significant table in the schema. Rather than tracking stock as a count (e.g., quantity = 50), it models every individual physical item as its own record. This is sometimes called a serialised inventory model.

The nullable order_id field is what links a physical stock item to an order when a customer places an order, a stock item is allocated to it by populating this field. This design makes several things easy to query:

• How many units of a product are available at a given warehouse?
• Which warehouse covering a customer's postcode has stock in hand?
• What items are currently allocated to a specific order?