Schema Design & Relationships
Design a production-quality e-commerce database schema — normalized tables, proper relationships, constraints, and data integrity.
55 min•By Priygop Team•Last updated: Feb 2026
Core Schema Design
- Users Table: CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), role VARCHAR(20) DEFAULT 'customer', created_at TIMESTAMP DEFAULT NOW())
- Products Table: CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(200) NOT NULL, slug VARCHAR(200) UNIQUE, description TEXT, price DECIMAL(10,2) NOT NULL CHECK (price > 0), compare_price DECIMAL(10,2), sku VARCHAR(50) UNIQUE, stock INTEGER DEFAULT 0 CHECK (stock >= 0), category_id INTEGER REFERENCES categories(id), is_active BOOLEAN DEFAULT true)
- Orders Table: CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','confirmed','shipped','delivered','cancelled')), subtotal DECIMAL(10,2), tax DECIMAL(10,2), shipping DECIMAL(10,2), total DECIMAL(10,2), shipping_address_id INTEGER REFERENCES addresses(id), created_at TIMESTAMP DEFAULT NOW())
- Order Items: CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE, product_id INTEGER REFERENCES products(id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price DECIMAL(10,2) NOT NULL, UNIQUE(order_id, product_id)) — junction table implementing many-to-many between orders and products
- Categories: CREATE TABLE categories (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(100) UNIQUE, parent_id INTEGER REFERENCES categories(id)) — self-referencing foreign key enables nested categories (Electronics > Phones > Smartphones)
- Addresses: CREATE TABLE addresses (id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id) ON DELETE CASCADE, label VARCHAR(50), street VARCHAR(255), city VARCHAR(100), state VARCHAR(100), postal_code VARCHAR(20), country VARCHAR(2), is_default BOOLEAN DEFAULT false) — one user → many addresses
Data Integrity & Constraints
- CHECK Constraints: price > 0 ensures no free or negative prices. stock >= 0 prevents negative inventory. status IN (...) restricts to valid values. These enforce business rules at the database level
- Foreign Keys: REFERENCES creates relationships — user_id REFERENCES users(id) ensures every order belongs to a real user. ON DELETE CASCADE removes child rows when parent is deleted (order_items when order deleted)
- UNIQUE Constraints: email UNIQUE prevents duplicate accounts. sku UNIQUE ensures product codes are distinct. UNIQUE(order_id, product_id) prevents the same product appearing twice in one order
- NOT NULL: Critical fields like email, password_hash, product name, price must never be empty. Use NOT NULL to enforce at database level — application validation can be bypassed, database constraints cannot
- DEFAULT Values: status DEFAULT 'pending' for new orders, is_active DEFAULT true for new products, created_at DEFAULT NOW() for timestamps. Reduces required fields in INSERT statements
- Indexes: CREATE INDEX idx_products_category ON products(category_id); CREATE INDEX idx_orders_user ON orders(user_id); CREATE INDEX idx_orders_status ON orders(status); — speed up frequent lookups and JOINs