On this page
Final Project: E-Commerce Database
Final Project — E-Commerce Database
Congratulations on reaching the final lesson! This project synthesizes everything you have learned throughout the course: schema design, normalization, constraints, indexes, Row Level Security, CTEs, window functions, and analytical queries — all in a single, production-quality PostgreSQL 18 database.
What You Are Building
A complete e-commerce database that supports:
- Product catalog: categories (hierarchical), brands, products, and variants with JSONB attributes
- User management: accounts with addresses and UUID primary keys
- Order processing: full order lifecycle from pending to delivered, with a generated total column
- Reviews and wishlists: user-generated content with RLS isolation
- Analytics: revenue reports, customer LTV, stock forecasting, and moving averages
Architecture Decisions Explained
UUID Primary Key for Users
Users have a UUID primary key (gen_random_uuid()). This is intentional: user IDs appear in API responses, JWT tokens, and URLs. An integer ID like 5 reveals business metrics (how many users you have) and allows sequential guessing attacks. A UUID like 550e8400-e29b-41d4-a716-446655440000 reveals nothing.
JSONB for Product Variant Attributes
Product variants differ by attributes like ram, storage, color, and size. These attributes vary by product type — a laptop has RAM and storage; a t-shirt has size and color. Using a JSONB column (attributes) is the right choice here because the structure is genuinely flexible per product type.
We index the name column with gin_trgm_ops for fast ILIKE '%keyword%' searches. This is a practical pattern for product search without a dedicated search engine.
Generated Computed Column for total_amount
total_amount NUMERIC(12,2) GENERATED ALWAYS AS
(subtotal - discount_amt + tax_amount + shipping_fee) STOREDThis PostgreSQL 12+ feature computes total_amount automatically from its components and stores the result. You never need to calculate it in application code, and it is always consistent — there is no way to insert an order with a wrong total.
Row Level Security for Data Isolation
Enabling RLS on orders, order_items, addresses, wishlists, and product_reviews ensures users can only access their own data. The application sets the current user at connection time:
-- At the start of each request handler, before running any query:
SET LOCAL app.user_id = '550e8400-e29b-41d4-a716-446655440000';Even if an application bug forgets to filter by user_id, PostgreSQL silently applies the RLS policy.
Extending the Schema
Here are directions to extend this project further:
Add a Coupons System
CREATE TABLE coupons (
coupon_id SERIAL PRIMARY KEY,
code VARCHAR(50) NOT NULL UNIQUE,
discount_type VARCHAR(20) NOT NULL CHECK (discount_type IN ('percentage', 'fixed')),
discount_value NUMERIC(10,2) NOT NULL CHECK (discount_value > 0),
min_order_value NUMERIC(10,2) NOT NULL DEFAULT 0,
max_uses INTEGER,
used_count INTEGER NOT NULL DEFAULT 0,
expires_at TIMESTAMPTZ,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT chk_coupon_usage CHECK (used_count <= COALESCE(max_uses, used_count + 1))
);Add an Inventory Tracking System
CREATE TABLE inventory_movements (
movement_id SERIAL PRIMARY KEY,
variant_id INTEGER NOT NULL REFERENCES product_variants(variant_id),
movement_type VARCHAR(30) NOT NULL CHECK (movement_type IN
('restock', 'sale', 'return', 'adjustment', 'damage')),
quantity_delta INTEGER NOT NULL,
reference_id INTEGER,
notes TEXT,
moved_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Trigger to update stock automatically on inventory movement
CREATE OR REPLACE FUNCTION apply_inventory_movement()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
UPDATE product_variants
SET stock = stock + NEW.quantity_delta
WHERE variant_id = NEW.variant_id;
IF (SELECT stock FROM product_variants WHERE variant_id = NEW.variant_id) < 0 THEN
RAISE EXCEPTION 'Insufficient stock for variant %', NEW.variant_id;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_inventory_movement
AFTER INSERT ON inventory_movements
FOR EACH ROW EXECUTE FUNCTION apply_inventory_movement();Add a Search View
CREATE MATERIALIZED VIEW product_search AS
SELECT
p.product_id,
p.slug,
p.name,
p.base_price,
cat.name AS category,
b.name AS brand,
COALESCE(
ROUND(AVG(r.rating), 1), 0
) AS avg_rating,
COUNT(r.review_id) AS review_count,
to_tsvector('english', p.name || ' ' || COALESCE(p.description, '')) AS search_vector
FROM products p
LEFT JOIN categories cat ON cat.category_id = p.category_id
LEFT JOIN brands b ON b.brand_id = p.brand_id
LEFT JOIN product_reviews r ON r.product_id = p.product_id
WHERE p.is_active = TRUE
GROUP BY p.product_id, p.slug, p.name, p.base_price, cat.name, b.name;
CREATE INDEX ON product_search USING GIN (search_vector);
CREATE UNIQUE INDEX ON product_search (product_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY product_search;What You Have Learned
Over 15 lessons, you have mastered:
- Relational fundamentals: tables, keys, relationships, and ER diagrams
- SQL DML: SELECT, INSERT, UPDATE, DELETE with all their clauses and options
- Aggregations: COUNT, SUM, AVG, GROUP BY, HAVING, CASE WHEN, and FILTER
- JOINs: INNER, LEFT, RIGHT, FULL OUTER, CROSS, self-joins, and anti-joins
- Advanced querying: subqueries, EXISTS, correlated queries, CTEs, and recursive CTEs
- Views and functions: regular views, materialized views, SQL functions, and PL/pgSQL
- Normalization: 1NF through BCNF, anomalies, and when to denormalize
- Constraints: primary keys, foreign keys, unique, check, not null, UUID, and CASCADE
- Indexes: B-tree, GIN, partial, composite, and reading EXPLAIN ANALYZE
- Transactions: BEGIN/COMMIT/ROLLBACK, savepoints, MVCC, isolation levels, and locking
- Security: roles, GRANT/REVOKE, Row Level Security, and multi-tenant patterns
- Backups: pg_dump, pg_restore, pg_dumpall, and PITR with WAL archiving
This is a complete, professional toolkit for working with PostgreSQL 18 in production. The schema you built in this project is the kind of solid foundation that scales from your first user to your first million.
Keep practicing. Open psql, design schemas for problems you find interesting, write queries, and read their execution plans. The best way to master SQL is to use it every day.
GENERATED ALWAYS AS STORED
The total_amount column in the orders table uses GENERATED ALWAYS AS ... STORED — a computed column whose value is automatically calculated from other columns and physically stored. This ensures the total is always consistent with its components without requiring application-level logic. PostgreSQL 12+ supports this SQL-standard feature.
Window Functions for Analytics
Query #4 uses a window function AVG(...) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to compute a 3-month moving average. Window functions operate on a 'window' of rows relative to each output row without collapsing the result set like GROUP BY does. They are essential for time-series analytics.
Test Your Schema With Real Data Volumes
A schema that works perfectly with 100 rows may perform poorly with 10 million rows. After designing your schema, load realistic data volumes and run EXPLAIN ANALYZE on your most critical queries. Identify missing indexes early, before they become production incidents.
-- ================================================
-- FINAL PROJECT: Complete E-Commerce Database
-- PostgreSQL 18 — Bemore Learn SQL Course
-- ================================================
-- This script builds a production-quality schema
-- including normalization, constraints, indexes,
-- RLS, and analytical queries.
-- ================================================
-- 0. Extensions
CREATE EXTENSION IF NOT EXISTS pgcrypto; -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- trigram full-text search
-- ================================================
-- SCHEMA: Core Catalog
-- ================================================
CREATE TABLE categories (
category_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(100) NOT NULL UNIQUE,
parent_id INTEGER REFERENCES categories(category_id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE brands (
brand_id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL UNIQUE,
website VARCHAR(500),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
category_id INTEGER NOT NULL REFERENCES categories(category_id),
brand_id INTEGER REFERENCES brands(brand_id) ON DELETE SET NULL,
name VARCHAR(300) NOT NULL,
slug VARCHAR(300) NOT NULL UNIQUE,
description TEXT,
sku VARCHAR(100) UNIQUE,
base_price NUMERIC(12,2) NOT NULL CHECK (base_price >= 0),
cost_price NUMERIC(12,2) CHECK (cost_price >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE product_variants (
variant_id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(product_id)
ON DELETE CASCADE,
sku VARCHAR(100) NOT NULL UNIQUE,
attributes JSONB NOT NULL DEFAULT '{}',
price_delta NUMERIC(10,2) NOT NULL DEFAULT 0,
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
weight_kg NUMERIC(8,3) CHECK (weight_kg > 0)
);
-- ================================================
-- SCHEMA: Users and Addresses
-- ================================================
CREATE TABLE users (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(320) NOT NULL UNIQUE,
password_hash VARCHAR(255),
full_name VARCHAR(200) NOT NULL,
phone VARCHAR(30),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE addresses (
address_id SERIAL PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(user_id)
ON DELETE CASCADE,
label VARCHAR(50) NOT NULL DEFAULT 'Home',
street VARCHAR(300) NOT NULL,
city VARCHAR(100) NOT NULL,
state VARCHAR(100),
postal_code VARCHAR(20),
country_code CHAR(2) NOT NULL DEFAULT 'US',
is_default BOOLEAN NOT NULL DEFAULT FALSE
);
-- ================================================
-- SCHEMA: Orders
-- ================================================
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(user_id),
address_id INTEGER REFERENCES addresses(address_id)
ON DELETE SET NULL,
status VARCHAR(30) NOT NULL DEFAULT 'pending'
CHECK (status IN (
'pending','confirmed','processing',
'shipped','delivered','cancelled','refunded'
)),
coupon_code VARCHAR(50),
discount_amt NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK (discount_amt >= 0),
subtotal NUMERIC(12,2) NOT NULL CHECK (subtotal >= 0),
tax_amount NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
shipping_fee NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK (shipping_fee >= 0),
total_amount NUMERIC(12,2) GENERATED ALWAYS AS
(subtotal - discount_amt + tax_amount + shipping_fee)
STORED,
notes TEXT,
ordered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES orders(order_id)
ON DELETE CASCADE,
variant_id INTEGER NOT NULL REFERENCES product_variants(variant_id),
product_id INTEGER NOT NULL REFERENCES products(product_id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0),
UNIQUE (order_id, variant_id)
);
-- ================================================
-- SCHEMA: Reviews and Wishlists
-- ================================================
CREATE TABLE product_reviews (
review_id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(product_id)
ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(user_id)
ON DELETE CASCADE,
rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
title VARCHAR(200),
body TEXT,
is_verified BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (product_id, user_id)
);
CREATE TABLE wishlists (
user_id UUID NOT NULL REFERENCES users(user_id)
ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES products(product_id)
ON DELETE CASCADE,
added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (user_id, product_id)
);
-- ================================================
-- INDEXES
-- ================================================
-- Products
CREATE INDEX idx_products_category ON products (category_id);
CREATE INDEX idx_products_brand ON products (brand_id);
CREATE INDEX idx_products_active ON products (is_active) WHERE is_active = TRUE;
CREATE INDEX idx_products_price ON products (base_price);
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
-- Orders
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_date ON orders (ordered_at DESC);
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- Order items
CREATE INDEX idx_order_items_order ON order_items (order_id);
CREATE INDEX idx_order_items_variant ON order_items (variant_id);
CREATE INDEX idx_order_items_product ON order_items (product_id);
-- Reviews
CREATE INDEX idx_reviews_product ON product_reviews (product_id);
CREATE INDEX idx_reviews_user ON product_reviews (user_id);
-- ================================================
-- ROW LEVEL SECURITY
-- ================================================
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE addresses ENABLE ROW LEVEL SECURITY;
ALTER TABLE wishlists ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_reviews ENABLE ROW LEVEL SECURITY;
-- Users can only access their own data
CREATE POLICY orders_user_policy ON orders
USING (user_id = current_setting('app.user_id', TRUE)::UUID);
CREATE POLICY order_items_policy ON order_items
USING (order_id IN (
SELECT order_id FROM orders
WHERE user_id = current_setting('app.user_id', TRUE)::UUID
));
CREATE POLICY addresses_policy ON addresses
USING (user_id = current_setting('app.user_id', TRUE)::UUID);
CREATE POLICY wishlists_policy ON wishlists
USING (user_id = current_setting('app.user_id', TRUE)::UUID);
CREATE POLICY reviews_select_policy ON product_reviews
FOR SELECT USING (TRUE); -- everyone can read reviews
CREATE POLICY reviews_write_policy ON product_reviews
FOR INSERT WITH CHECK (
user_id = current_setting('app.user_id', TRUE)::UUID
);
-- ================================================
-- SEED DATA
-- ================================================
INSERT INTO categories (name, slug) VALUES
('Electronics', 'electronics'),
('Computers', 'computers'),
('Accessories', 'accessories'),
('Furniture', 'furniture'),
('Stationery', 'stationery');
-- Set parent categories
UPDATE categories SET parent_id = (SELECT category_id FROM categories WHERE slug = 'electronics')
WHERE slug = 'computers';
INSERT INTO brands (name, website) VALUES
('TechCorp', 'https://techcorp.example.com'),
('ErgoDesign', 'https://ergodesign.example.com'),
('OfficeMax', 'https://officemax.example.com');
INSERT INTO products (category_id, brand_id, name, slug, base_price, cost_price, sku) VALUES
(2, 1, 'Laptop Pro 15', 'laptop-pro-15', 1299.99, 850.00, 'LP-15-001'),
(1, 1, 'Wireless Mouse', 'wireless-mouse', 29.99, 12.00, 'WM-001'),
(1, 1, 'Mechanical Keyboard', 'mechanical-keyboard', 89.99, 40.00, 'MK-001'),
(4, 2, 'Standing Desk', 'standing-desk', 349.99, 180.00, 'SD-001'),
(4, 2, 'Ergonomic Chair', 'ergonomic-chair', 499.99, 250.00, 'EC-001'),
(5, 3, 'Notebook A5', 'notebook-a5', 4.99, 1.50, 'NB-A5-001'),
(3, 1, 'USB-C Hub 7-in-1', 'usbc-hub-7in1', 49.99, 22.00, 'HUB-7C-001');
-- Variants for Laptop Pro 15
INSERT INTO product_variants (product_id, sku, attributes, price_delta, stock, weight_kg) VALUES
(1, 'LP-15-8GB-256', '{"ram":"8GB","storage":"256GB","color":"silver"}', 0, 15, 1.8),
(1, 'LP-15-16GB-512', '{"ram":"16GB","storage":"512GB","color":"silver"}', 200, 10, 1.8),
(1, 'LP-15-32GB-1TB', '{"ram":"32GB","storage":"1TB","color":"space-gray"}', 500, 5, 1.8);
-- Variants for Mechanical Keyboard
INSERT INTO product_variants (product_id, sku, attributes, price_delta, stock) VALUES
(3, 'MK-RED', '{"switch":"red","layout":"US"}', 0, 30),
(3, 'MK-BLUE', '{"switch":"blue","layout":"US"}', 10, 25),
(3, 'MK-BROWN', '{"switch":"brown","layout":"US"}', 10, 20);
-- Sample users
INSERT INTO users (email, full_name) VALUES
('[email protected]', 'Alice Johnson'),
('[email protected]', 'Bob Smith'),
('[email protected]', 'Charlie Brown');
-- ================================================
-- ANALYTICAL QUERIES
-- ================================================
-- 1. Revenue by category (last 90 days)
SELECT
cat.name AS category,
COUNT(DISTINCT o.order_id) AS orders,
SUM(oi.quantity) AS units_sold,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS gross_revenue,
ROUND(AVG(oi.unit_price), 2) AS avg_selling_price
FROM categories cat
JOIN products p ON p.category_id = cat.category_id
JOIN order_items oi ON oi.product_id = p.product_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'delivered'
AND o.ordered_at >= NOW() - INTERVAL '90 days'
GROUP BY cat.name
ORDER BY gross_revenue DESC;
-- 2. Top customers by lifetime value with tier classification
WITH customer_ltv AS (
SELECT
u.user_id,
u.full_name,
u.email,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(o.total_amount), 2) AS lifetime_value,
MAX(o.ordered_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id AND o.status = 'delivered'
GROUP BY u.user_id, u.full_name, u.email
)
SELECT
full_name,
email,
order_count,
lifetime_value,
CASE
WHEN lifetime_value >= 2000 THEN 'VIP'
WHEN lifetime_value >= 500 THEN 'Regular'
WHEN lifetime_value > 0 THEN 'New'
ELSE 'Never Ordered'
END AS tier,
RANK() OVER (ORDER BY lifetime_value DESC) AS rank
FROM customer_ltv
ORDER BY lifetime_value DESC NULLS LAST
LIMIT 20;
-- 3. Products with low stock and their sales velocity
SELECT
p.name,
pv.sku,
pv.attributes,
pv.stock AS current_stock,
COALESCE(SUM(oi.quantity), 0) AS sold_last_30d,
CASE
WHEN SUM(oi.quantity) IS NULL THEN 'no sales'
WHEN pv.stock < SUM(oi.quantity) * 0.5 THEN 'critical'
WHEN pv.stock < SUM(oi.quantity) THEN 'low'
ELSE 'adequate'
END AS stock_status
FROM product_variants pv
JOIN products p ON p.product_id = pv.product_id
LEFT JOIN order_items oi ON oi.variant_id = pv.variant_id
LEFT JOIN orders o ON o.order_id = oi.order_id
AND o.ordered_at >= NOW() - INTERVAL '30 days'
AND o.status NOT IN ('cancelled', 'refunded')
WHERE p.is_active = TRUE
GROUP BY p.name, pv.sku, pv.attributes, pv.stock
HAVING pv.stock < 20 OR SUM(oi.quantity) IS NULL
ORDER BY current_stock ASC, sold_last_30d DESC;
-- 4. Monthly revenue trend with moving average
WITH monthly AS (
SELECT
DATE_TRUNC('month', ordered_at) AS month,
COUNT(*) AS order_count,
ROUND(SUM(total_amount), 2) AS revenue
FROM orders
WHERE status IN ('delivered', 'shipped')
GROUP BY DATE_TRUNC('month', ordered_at)
)
SELECT
TO_CHAR(month, 'YYYY-MM') AS month,
order_count,
revenue,
ROUND(AVG(revenue) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_3m
FROM monthly
ORDER BY month;
Sign in to track your progress