Database Design & Modeling - ER diagrams, normalization, and schema patterns

Module 2: Database Design & Modeling

Great database design is the foundation of every successful application. A well-designed schema makes queries fast, code simple, and scaling possible. A poorly designed one creates endless headaches.

Estimated Time: 10-12 hours
Hands-On: Schema design exercises + E-commerce project
Key Skill: Translating business requirements into efficient schemas

2.1 Entity-Relationship Modeling

Before writing any SQL, understand the data you’re modeling.

The ER Modeling Process

Identify Entities

What are the main “things” in your system?

Users, Products, Orders, Categories, Reviews
Each becomes a table

Define Attributes

What properties does each entity have?

User: name, email, password_hash, created_at
Product: name, description, price, stock

Establish Relationships

How do entities relate to each other?

A User places many Orders (1:N)
An Order contains many Products (M:N)

Determine Cardinality

How many of each entity can relate?

One-to-One (1:1)
One-to-Many (1:N)
Many-to-Many (M:N)

Relationship Types

┌─────────────────────────────────────────────────────────────────────────┐
│                      RELATIONSHIP CARDINALITIES                         │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  ONE-TO-ONE (1:1)                                                       │
│  ┌──────────┐         ┌──────────────┐                                 │
│  │   User   │────────▶│ UserProfile  │  Each user has exactly one     │
│  └──────────┘         └──────────────┘  profile                         │
│                                                                         │
│  ONE-TO-MANY (1:N)                                                      │
│  ┌──────────┐         ┌──────────────┐                                 │
│  │   User   │────────▶│    Orders    │  One user can have many        │
│  └──────────┘    │    └──────────────┘  orders                          │
│                  │    ┌──────────────┐                                 │
│                  └───▶│    Orders    │                                  │
│                       └──────────────┘                                 │
│                                                                         │
│  MANY-TO-MANY (M:N)                                                     │
│  ┌──────────┐         ┌──────────────┐         ┌──────────┐            │
│  │  Orders  │◀───────▶│ Order_Items  │◀───────▶│ Products │            │
│  └──────────┘         └──────────────┘         └──────────┘            │
│              (Junction/Bridge Table)                                    │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

E-Commerce ER Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                        E-COMMERCE ENTITY RELATIONSHIPS                       │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  ┌────────────────┐                                                          │
│  │   categories   │                                                          │
│  ├────────────────┤        ┌─────────────────┐                              │
│  │ id (PK)        │───┐    │    products     │                              │
│  │ name           │   │    ├─────────────────┤      ┌─────────────────┐     │
│  │ parent_id (FK) │◀──┘    │ id (PK)         │      │  order_items    │     │
│  │ description    │◀───────│ category_id (FK)│      ├─────────────────┤     │
│  └────────────────┘        │ name            │◀─────│ id (PK)         │     │
│         ▲                  │ description     │      │ order_id (FK)   │────▶│
│         │ Self-reference   │ price           │      │ product_id (FK) │     │
│         └──────────────────│ stock           │      │ quantity        │     │
│                            │ created_at      │      │ unit_price      │     │
│                            └─────────────────┘      └─────────────────┘     │
│                                    │                        │               │
│                                    │ reviews                │               │
│  ┌────────────────┐                ▼                        │               │
│  │     users      │        ┌─────────────────┐              │               │
│  ├────────────────┤        │    reviews      │              │               │
│  │ id (PK)        │───┐    ├─────────────────┤              │               │
│  │ email          │   │    │ id (PK)         │              │               │
│  │ name           │   │    │ user_id (FK)    │◀────┐        │               │
│  │ password_hash  │   │    │ product_id (FK) │     │        │               │
│  │ created_at     │   │    │ rating          │     │        │               │
│  └────────────────┘   │    │ comment         │     │        │               │
│         │             │    │ created_at      │     │        │               │
│         │             │    └─────────────────┘     │        │               │
│         │             │                            │        │               │
│         │             └────────────────────────────┘        │               │
│         │                                                   │               │
│         │    ┌─────────────────┐                           │               │
│         └───▶│     orders      │                           │               │
│              ├─────────────────┤                           │               │
│              │ id (PK)         │◀──────────────────────────┘               │
│              │ user_id (FK)    │                                            │
│              │ status          │                                            │
│              │ total           │                                            │
│              │ shipping_address│                                            │
│              │ created_at      │                                            │
│              └─────────────────┘                                            │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  NORMALIZE (3NF) when:                 DENORMALIZE when:               │
│  ────────────────────                  ──────────────────               │
│  • Data integrity is critical          • Read performance is critical   │
│  • Frequent updates to data            • Data rarely changes            │
│  • Storage is a concern                • Complex JOINs hurt performance │
│  • OLTP (transactional) systems        • OLAP (analytical) systems      │
│  • Multiple apps share the DB          • Reporting/dashboards           │
│  • You need flexibility                • You know all query patterns    │
│                                                                         │
│  ┌─────────────────────────────────────────────────────────────────┐   │
│  │                     PRACTICAL APPROACH                          │   │
│  ├─────────────────────────────────────────────────────────────────┤   │
│  │                                                                 │   │
│  │  1. Start with 3NF - it's the safe default                     │   │
│  │  2. Measure query performance                                   │   │
│  │  3. Denormalize strategically when you have evidence           │   │
│  │  4. Use materialized views for read optimization               │   │
│  │  5. Keep the normalized source of truth                        │   │
│  │                                                                 │   │
│  └─────────────────────────────────────────────────────────────────┘   │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

2.3 Keys and Constraints

Constraints enforce data integrity at the database level.

Primary Keys

-- Auto-incrementing integer (common)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

-- BigInt for large scale
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL
);

-- UUID for distributed systems
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Integer vs UUID: Use integers for simplicity and performance. Use UUIDs when you need to generate IDs in distributed systems or expose IDs in URLs (harder to guess).

Foreign Keys

-- Basic foreign key
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- With explicit ON DELETE behavior
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT
);

-- ON DELETE options:
-- CASCADE    - Delete child rows when parent is deleted
-- RESTRICT   - Prevent deletion of parent if children exist
-- SET NULL   - Set foreign key to NULL when parent is deleted
-- SET DEFAULT- Set to default value when parent is deleted
-- NO ACTION  - Similar to RESTRICT (default)

Unique Constraints

-- Single column unique
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

-- Multi-column unique (composite unique)
CREATE TABLE product_variants (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES products(id),
    size VARCHAR(10),
    color VARCHAR(20),
    UNIQUE (product_id, size, color)  -- Each combination must be unique
);

-- Partial unique (unique with condition)
CREATE UNIQUE INDEX unique_active_email 
ON users(email) 
WHERE deleted_at IS NULL;  -- Only active users must have unique emails

Check Constraints

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    status VARCHAR(20) CHECK (status IN ('draft', 'active', 'archived')),
    
    -- Multi-column check
    sale_price DECIMAL(10, 2),
    CONSTRAINT valid_sale_price CHECK (sale_price IS NULL OR sale_price < price)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total DECIMAL(12, 2) NOT NULL,
    shipped_at TIMESTAMPTZ,
    
    -- Logical constraint
    CONSTRAINT shipped_orders_have_date 
        CHECK (status != 'shipped' OR shipped_at IS NOT NULL)
);

Not Null Constraints

-- Explicit NOT NULL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    bio TEXT,  -- This can be NULL
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Default values reduce NULL needs
CREATE TABLE settings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    theme VARCHAR(20) NOT NULL DEFAULT 'light',
    notifications_enabled BOOLEAN NOT NULL DEFAULT true,
    timezone VARCHAR(50) NOT NULL DEFAULT 'UTC'
);

2.4 Schema Design Patterns

Common patterns for real-world applications.

Soft Deletes

Keep deleted records for auditing/recovery instead of hard deleting.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMPTZ,  -- NULL means active
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Unique email only for non-deleted users
CREATE UNIQUE INDEX idx_users_email_active 
ON users(email) 
WHERE deleted_at IS NULL;

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 123;

-- Query active users only (create a view for convenience)
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

Audit Trail / History Tables

Track all changes to important data.

-- Main table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- History table
CREATE TABLE products_history (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changed_by INTEGER REFERENCES users(id),
    operation VARCHAR(10) NOT NULL  -- INSERT, UPDATE, DELETE
);

-- Trigger to auto-populate history
CREATE OR REPLACE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO products_history (product_id, name, price, operation)
        VALUES (OLD.id, OLD.name, OLD.price, 'UPDATE');
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO products_history (product_id, name, price, operation)
        VALUES (OLD.id, OLD.name, OLD.price, 'DELETE');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_audit_trigger
AFTER UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION log_product_changes();

Polymorphic Associations

When multiple tables can be “commentable” or “taggable”.

-- Approach 1: Separate foreign keys (recommended for smaller cases)
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    body TEXT NOT NULL,
    -- One of these will be set
    post_id INTEGER REFERENCES posts(id),
    product_id INTEGER REFERENCES products(id),
    article_id INTEGER REFERENCES articles(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Ensure exactly one is set
    CONSTRAINT one_parent CHECK (
        (post_id IS NOT NULL)::int + 
        (product_id IS NOT NULL)::int + 
        (article_id IS NOT NULL)::int = 1
    )
);

-- Approach 2: Generic reference (more flexible, less type-safe)
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    body TEXT NOT NULL,
    commentable_type VARCHAR(50) NOT NULL,  -- 'Post', 'Product', 'Article'
    commentable_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_comments_polymorphic 
ON comments(commentable_type, commentable_id);

Self-Referential Relationships

For hierarchies like categories, org charts, or threaded comments.

-- Categories with unlimited nesting
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(id),
    depth INTEGER NOT NULL DEFAULT 0,
    path TEXT  -- Materialized path for efficient queries
);

-- Create index for hierarchy queries
CREATE INDEX idx_categories_parent ON categories(parent_id);
CREATE INDEX idx_categories_path ON categories(path);

-- Example data
INSERT INTO categories (id, name, parent_id, path) VALUES
(1, 'Electronics', NULL, '1'),
(2, 'Computers', 1, '1.2'),
(3, 'Laptops', 2, '1.2.3'),
(4, 'Gaming Laptops', 3, '1.2.3.4');

-- Query: Get all descendants
SELECT * FROM categories 
WHERE path LIKE '1.2.%'  -- All under Computers
ORDER BY path;

-- Query: Get ancestors (breadcrumb)
SELECT * FROM categories
WHERE '1.2.3.4' LIKE path || '.%' OR id = 4
ORDER BY depth;

Tagging System

Many-to-many with flexible metadata.

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (post_id, tag_id)
);

-- Indexes for common queries
CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);

-- Find posts with specific tags
SELECT p.* FROM posts p
INNER JOIN post_tags pt ON p.id = pt.post_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE t.slug IN ('javascript', 'react');

-- Find posts with ALL specified tags
SELECT p.* FROM posts p
INNER JOIN post_tags pt ON p.id = pt.post_id
INNER JOIN tags t ON pt.tag_id = t.id
WHERE t.slug IN ('javascript', 'react')
GROUP BY p.id
HAVING COUNT(DISTINCT t.id) = 2;  -- Must have both tags

2.5 JSONB for Flexible Data

When you need schema flexibility within a relational database.

When to Use JSONB

Good Use Cases

User preferences/settings
Product attributes (vary by category)
API response caching
Metadata and tags
Form submissions

Avoid JSONB For

Core business data you query often
Data with relationships
Data you need to aggregate
Anything that should be normalized

JSONB Operations

-- Create table with JSONB
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    base_price DECIMAL(10, 2) NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'
);

-- Insert with JSONB
INSERT INTO products (name, base_price, attributes) VALUES
('Gaming Laptop', 1500.00, '{
    "brand": "TechCorp",
    "screen_size": 15.6,
    "specs": {
        "ram": "32GB",
        "storage": "1TB SSD",
        "gpu": "RTX 4080"
    },
    "colors": ["black", "silver"],
    "gaming_features": true
}');

-- Query: Access nested values
SELECT 
    name,
    attributes->>'brand' AS brand,
    attributes->'specs'->>'ram' AS ram,
    attributes->'specs'->>'gpu' AS gpu
FROM products
WHERE attributes->>'brand' = 'TechCorp';

-- Query: Check if key exists
SELECT * FROM products 
WHERE attributes ? 'gaming_features';

-- Query: Contains value
SELECT * FROM products 
WHERE attributes @> '{"gaming_features": true}';

-- Query: Array contains
SELECT * FROM products 
WHERE attributes->'colors' ? 'silver';

-- Update: Set a nested value
UPDATE products 
SET attributes = jsonb_set(attributes, '{specs,ram}', '"64GB"')
WHERE id = 1;

-- Update: Add a new field
UPDATE products 
SET attributes = attributes || '{"warranty_years": 3}'
WHERE id = 1;

-- Index for containment queries (very fast)
CREATE INDEX idx_products_attrs ON products USING gin(attributes);

-- Index specific paths
CREATE INDEX idx_products_brand ON products ((attributes->>'brand'));

Hybrid Schema Pattern

Combine normalized columns with JSONB for flexibility.

CREATE TABLE products (
    -- Normalized: data you query/filter often
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    base_price DECIMAL(10, 2) NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- JSONB: flexible, category-specific attributes
    attributes JSONB NOT NULL DEFAULT '{}',
    
    -- Indexes
    CONSTRAINT valid_status CHECK (status IN ('draft', 'active', 'archived'))
);

-- Fast filtering on normalized columns
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_status ON products(status) WHERE status = 'active';

-- GIN index for JSONB queries
CREATE INDEX idx_products_attributes ON products USING gin(attributes);

2.6 Schema Migrations

Evolve your schema safely over time.

Migration Best Practices

Version Control Migrations

Every schema change should be a versioned migration file.

migrations/
├── 001_create_users.sql
├── 002_create_products.sql
├── 003_add_user_phone.sql
├── 004_create_orders.sql
└── 005_add_order_status_index.sql

Each migration has UP (apply) and DOWN (rollback) scripts.

Backward Compatible Changes

When deploying, old and new code may run simultaneously. Safe changes:✅ Safe (backward compatible):

Add new table
Add new nullable column
Add new column with default
Add new index
Rename table/column (with alias/view)

❌ Unsafe (requires coordination):

Drop table/column
Rename without alias
Change column type
Add NOT NULL to existing column

Multi-Step Migrations

For breaking changes, use multiple deployments:Renaming a column:

Deploy: Add new column, backfill data
Deploy: Update code to write both columns
Deploy: Update code to read new column
Deploy: Drop old column

Adding NOT NULL:

Deploy: Add column as nullable
Backfill data (all rows)
Deploy: Add NOT NULL constraint

Safe Migration Examples

-- Adding a column (safe)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Adding with default (safe, but can be slow on large tables)
ALTER TABLE users ADD COLUMN verified BOOLEAN NOT NULL DEFAULT false;

-- PostgreSQL 11+ instant add with default
ALTER TABLE users ADD COLUMN tier VARCHAR(20) NOT NULL DEFAULT 'free';

-- Adding an index concurrently (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);

-- Renaming column safely
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);

-- Step 2: Backfill
UPDATE users SET full_name = name;

-- Step 3: (After code change) Drop old column
ALTER TABLE users DROP COLUMN name;

-- Changing column type (use intermediate column)
ALTER TABLE products ADD COLUMN new_price BIGINT;
UPDATE products SET new_price = (price * 100)::bigint;  -- Convert to cents
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN new_price TO price_cents;

2.7 Practice: E-Commerce Schema Design

Challenge

Design a complete database schema for an e-commerce platform with:

Users (customers and admins)
Products with categories (hierarchical)
Product variants (size, color)
Shopping cart
Orders with items
Reviews and ratings
Wishlist
Coupons/discounts

Requirements

Users can have multiple addresses
Products belong to categories (nested up to 3 levels)
Each product can have variants with different prices and stock
Orders track status history
Reviews are moderated (pending, approved, rejected)
Coupons can be percentage or fixed amount

Solution Schema

-- Users and Authentication
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'customer' CHECK (role IN ('customer', 'admin')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE addresses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    label VARCHAR(50) NOT NULL,  -- 'Home', 'Work', etc.
    street VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(100) NOT NULL,
    is_default BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Categories (hierarchical)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    parent_id INTEGER REFERENCES categories(id),
    depth INTEGER NOT NULL DEFAULT 0 CHECK (depth <= 2),
    path TEXT NOT NULL,  -- Materialized path: '1.5.12'
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Products
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    base_price DECIMAL(10, 2) NOT NULL CHECK (base_price >= 0),
    attributes JSONB NOT NULL DEFAULT '{}',
    status VARCHAR(20) NOT NULL DEFAULT 'draft' 
        CHECK (status IN ('draft', 'active', 'archived')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE product_variants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    sku VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,  -- 'Large / Red'
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB NOT NULL DEFAULT '{}',  -- {"size": "L", "color": "red"}
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (product_id, name)
);

CREATE TABLE product_images (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    url VARCHAR(500) NOT NULL,
    alt_text VARCHAR(255),
    position INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Shopping Cart
CREATE TABLE carts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    session_id VARCHAR(255),  -- For anonymous carts
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT user_or_session CHECK (user_id IS NOT NULL OR session_id IS NOT NULL)
);

CREATE TABLE cart_items (
    cart_id UUID NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
    variant_id UUID NOT NULL REFERENCES product_variants(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (cart_id, variant_id)
);

-- Orders
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    order_number VARCHAR(20) NOT NULL UNIQUE,
    status VARCHAR(30) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'confirmed', 'processing', 
                          'shipped', 'delivered', 'cancelled', 'refunded')),
    subtotal DECIMAL(12, 2) NOT NULL,
    discount_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
    shipping_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
    tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
    total DECIMAL(12, 2) NOT NULL,
    
    shipping_address JSONB NOT NULL,  -- Snapshot of address at order time
    billing_address JSONB,
    
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    variant_id UUID NOT NULL REFERENCES product_variants(id),
    product_name VARCHAR(255) NOT NULL,  -- Snapshot
    variant_name VARCHAR(100) NOT NULL,  -- Snapshot
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(12, 2) NOT NULL
);

CREATE TABLE order_status_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    status VARCHAR(30) NOT NULL,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES users(id)
);

-- Reviews
CREATE TABLE reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id),
    order_id UUID REFERENCES orders(id),  -- Verified purchase
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(255),
    body TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'approved', 'rejected')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (product_id, user_id)  -- One review per product per user
);

-- Wishlists
CREATE TABLE wishlists (
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, product_id)
);

-- Coupons
CREATE TABLE coupons (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    discount_type VARCHAR(20) NOT NULL CHECK (discount_type IN ('percentage', 'fixed')),
    discount_value DECIMAL(10, 2) NOT NULL CHECK (discount_value > 0),
    minimum_order DECIMAL(10, 2),
    maximum_discount DECIMAL(10, 2),  -- Cap for percentage discounts
    usage_limit INTEGER,
    used_count INTEGER NOT NULL DEFAULT 0,
    valid_from TIMESTAMPTZ NOT NULL,
    valid_until TIMESTAMPTZ NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_coupons (
    order_id UUID NOT NULL REFERENCES orders(id),
    coupon_id UUID NOT NULL REFERENCES coupons(id),
    discount_amount DECIMAL(12, 2) NOT NULL,
    PRIMARY KEY (order_id, coupon_id)
);

-- Essential Indexes
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_status ON products(status) WHERE status = 'active';
CREATE INDEX idx_variants_product ON product_variants(product_id);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_reviews_product ON reviews(product_id) WHERE status = 'approved';
CREATE INDEX idx_coupons_code ON coupons(code) WHERE is_active = true;

Next Module

Module 3: Transactions & ACID

Build reliable applications with proper transaction handling

Testing & Code Quality

Crash Courses

AI Engineering

Math for ML - Understanding Linear Algebra

Probability & Statistics for ML

Math for ML - Understanding Calculus

ML Mastery

Deep Learning Mastery

NestJS Mastery

Microservices Mastery

Low Level Design

OOP Concepts

SOLID Principles

Design Patterns

LLD Case Studies

System Design (HLD)

Senior Level (L5+/Staff)

HLD Case Studies

Engineering Fundamentals

DevOps & Operations

AWS Cloud

AWS Monitoring & Observability

AWS Security Services

AWS Serverless

AWS Operations

AWS Advanced

AWS Case Studies

DevOps Tools

Database Engineering

HIPAA Compliance Mastery

Operating Systems

Linux Internals

Distributed Systems

Networking Mastery

Build Your Own X

Go Lang Mastery

C Programming

Distributed System Tools

​Module 2: Database Design & Modeling

​2.1 Entity-Relationship Modeling

​The ER Modeling Process

​Relationship Types

​E-Commerce ER Diagram

​2.3 Keys and Constraints

​Primary Keys

​Foreign Keys

​Unique Constraints

​Check Constraints

​Not Null Constraints

​2.4 Schema Design Patterns

​Soft Deletes

​Audit Trail / History Tables

​Polymorphic Associations

​Self-Referential Relationships

​Tagging System

​2.5 JSONB for Flexible Data

​When to Use JSONB

Good Use Cases

Avoid JSONB For

​JSONB Operations

​Hybrid Schema Pattern

​2.6 Schema Migrations

​Migration Best Practices

​Safe Migration Examples

​2.7 Practice: E-Commerce Schema Design

​Challenge

​Requirements

​Next Module

Module 3: Transactions & ACID

Module 2: Database Design & Modeling

2.1 Entity-Relationship Modeling

The ER Modeling Process

Relationship Types

E-Commerce ER Diagram

2.3 Keys and Constraints

Primary Keys

Foreign Keys

Unique Constraints

Check Constraints

Not Null Constraints

2.4 Schema Design Patterns

Soft Deletes

Audit Trail / History Tables

Polymorphic Associations

Self-Referential Relationships

Tagging System

2.5 JSONB for Flexible Data

When to Use JSONB

JSONB Operations

Hybrid Schema Pattern

2.6 Schema Migrations

Migration Best Practices

Safe Migration Examples

2.7 Practice: E-Commerce Schema Design

Challenge

Requirements

Next Module