┌─────────────┐
│ Users │
├─────────────┤
│ id (PK) │
│ email │
│ name │
│ created_at │
└──────┬──────┘
│ 1
│
│ N
┌──────▼──────┐
│ Orders │
├─────────────┤
│ id (PK) │
│ user_id (FK)│
│ total │
│ status │
│ created_at │
└──────┬──────┘
│ N
│
│ M
┌──────▼──────────┐ ┌─────────────┐
│ Order_Items │ N │ Products │
├─────────────────┤──────├─────────────┤
│ id (PK) │ M │ id (PK) │
│ order_id (FK) │ │ name │
│ product_id (FK) │ │ price │
│ quantity │ │ stock │
│ price_at_time │ │ created_at │
└─────────────────┘ └─────────────┘
DDL (PostgreSQL)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULLUNIQUE,
name VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPDEFAULT NOW(),
updated_at TIMESTAMPDEFAULT NOW()
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
stock INTEGERNOT NULLDEFAULT0,
category_id INTEGERREFERENCES categories(id),
created_at TIMESTAMPDEFAULT NOW()
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGERNOT NULLREFERENCES users(id) ONDELETE RESTRICT,
total DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULLDEFAULT'pending',
created_at TIMESTAMPDEFAULT NOW()
);
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGERNOT NULLREFERENCES orders(id) ONDELETE CASCADE,
product_id INTEGERNOT NULLREFERENCES products(id),
quantity INTEGERNOT NULL,
price_at_time DECIMAL(10, 2) NOT NULL, -- Snapshot of product priceUNIQUE(order_id, product_id) -- Can't add same product twice
);
-- Indexes for performanceCREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
Normalization Forms
1NF (First Normal Form)
Atomic values (no arrays/lists)
Each column contains single value
Each row unique
❌ Not 1NF
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER,
products VARCHAR(255) -- "123,456,789" BAD!
);
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
product_name VARCHAR(255), -- Depends only on product_id, not (order_id, product_id)
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);
✅ 2NF
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) -- Moved to products table
);
3NF (Third Normal Form)
2NF + No transitive dependencies
Non-key columns depend only on primary key
❌ Not 3NF
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
city VARCHAR(255),
country VARCHAR(255), -- Depends on city (transitive)
zipcode VARCHAR(10) -- Depends on city (transitive)
);
✅ 3NF
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
address_id INTEGERREFERENCES addresses(id)
);
CREATE TABLE addresses (
id SERIAL PRIMARY KEY,
city VARCHAR(255),
country VARCHAR(255),
zipcode VARCHAR(10)
);
Denormalization for Performance
When to Denormalize
Scenario: Displaying order with user name requires JOIN
-- Normalized (JOIN required)SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.id =123;
Denormalized:
ALTER TABLE orders ADDCOLUMN user_name VARCHAR(255);
-- Query without JOIN (faster)SELECT id, total, user_name
FROM orders
WHERE id =123;
Trade-off:
✅ Faster reads (no JOIN)
❌ Slower writes (update user_name in orders when user changes name)
❌ Data redundancy
Materialized View (Denormalization Alternative)
CREATE MATERIALIZED VIEW order_summary ASSELECT
o.id,
o.total,
o.status,
u.name AS user_name,
u.email AS user_email,
COUNT(oi.id) AS item_count
FROM orders o
JOIN users u ON o.user_id = u.id
LEFTJOIN order_items oi ON o.id = oi.order_id
GROUPBY o.id, u.id;
-- Refresh periodically
REFRESH MATERIALIZED VIEW order_summary;
-- Query (fast)SELECT*FROM order_summary WHERE id =123;
-- Add column with default (safe)ALTER TABLE users ADDCOLUMN phone VARCHAR(20) DEFAULTNULL;
-- Add NOT NULL column (unsafe, requires data)-- Step 1: Add nullableALTER TABLE users ADDCOLUMN status VARCHAR(20) DEFAULTNULL;
-- Step 2: Backfill dataUPDATE users SET status ='active'WHERE status ISNULL;
-- Step 3: Add NOT NULL constraintALTER TABLE users ALTERCOLUMN status SETNOT NULL;
Versioned Schema (API Evolution)
// V1: Single name field
{
id: 1,
name: "John Doe",
email: "john@example.com"
}
// V2: Split name into first/last
{
id: 1,
first_name: "John",
last_name: "Doe",
email: "john@example.com",
// Keep old field for backward compatname: "John Doe"// Computed from first_name + last_name
}
Use cases: Social networks, recommendation engines, fraud detection
Time-Series Model
-- Wide table (one row per metric per hour)CREATE TABLE metrics (
timestamp TIMESTAMPTZ NOT NULL,
server_id INTEGERNOT NULL,
cpu_usage REAL,
memory_usage REAL,
disk_usage REAL,
PRIMARY KEY (timestamp, server_id)
);
-- Partition by time (TimescaleDB)SELECT create_hypertable('metrics', 'timestamp');
-- Efficient time-range queriesSELECTAVG(cpu_usage)
FROM metrics
WHEREtimestamp>= NOW() -INTERVAL'24 hours'AND server_id =123;
Indexing Strategy
-- Single column indexCREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Covers query: WHERE user_id = X AND status = Y-- Also covers: WHERE user_id = X-- Does NOT cover: WHERE status = Y (status not first)-- Partial index (smaller, faster)CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status ='pending';
-- Full-text search indexCREATE INDEX idx_products_search ON products
USING gin(to_tsvector('english', name ||' '|| description));
Rules
Normalize to 3NF first, denormalize for performance — start correct, optimize later.
Access patterns drive schema design — optimize for most frequent queries.
Foreign keys enforce referential integrity — prevents orphaned records.
Index foreign keys always — joins on unindexed columns are slow.
Composite indexes ordered by selectivity — most selective column first.
Denormalize read-heavy data — user names, product titles in orders.
Price snapshots for historical accuracy — store price_at_time in order_items.
UUID for distributed systems — auto-increment IDs cause collisions in multi-region.
