Relational Data Modeling

-- Users table
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255) NOT NULL UNIQUE,
  created_at  TIMESTAMP DEFAULT NOW()
);

-- Products table
CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  price       DECIMAL(10,2) NOT NULL CHECK (price > 0),
  stock       INTEGER NOT NULL DEFAULT 0
);

-- Orders table (one-to-many: user → orders)
CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER NOT NULL REFERENCES users(id),
  status      VARCHAR(20) DEFAULT 'pending',
  total       DECIMAL(10,2) NOT NULL,
  created_at  TIMESTAMP DEFAULT NOW()
);

-- Order items (many-to-many: orders ↔ products)
CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id),
  product_id  INTEGER NOT NULL REFERENCES products(id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  DECIMAL(10,2) NOT NULL
);

orders_flat table:
┌──────────┬───────────┬─────────────────┬──────────────┬───────┬─────────┐
│ order_id │ user_name │ user_email      │ product_name │ price │ qty     │
├──────────┼───────────┼─────────────────┼──────────────┼───────┼─────────┤
│ 1        │ Alice     │ alice@mail.com  │ Laptop       │ 999   │ 1       │
│ 1        │ Alice     │ alice@mail.com  │ Mouse        │ 29    │ 2       │
│ 2        │ Alice     │ alice@mail.com  │ Keyboard     │ 79    │ 1       │
│ 3        │ Bob       │ bob@mail.com    │ Laptop       │ 999   │ 1       │
└──────────┴───────────┴─────────────────┴──────────────┴───────┴─────────┘

Problems:
  ❌ Alice's email is stored 3 times (redundancy)
  ❌ "Laptop" price is stored 2 times (redundancy)
  ❌ If Alice changes email → update 3 rows (update anomaly)
  ❌ If we delete order #3 → we lose Bob's info (delete anomaly)
  ❌ Can't add a product without an order (insert anomaly)

-- Each entity in its own table, no redundancy

users:       id | name  | email
products:    id | name  | price
orders:      id | user_id (FK) | status | total | created_at
order_items: id | order_id (FK) | product_id (FK) | quantity | unit_price

✅ Alice's email stored once (in users)
✅ Laptop price stored once (in products)
✅ Update email → change 1 row
✅ Delete an order → user data is safe
✅ Add a product without needing an order

-- Normalized: to show "Alice — 47 orders" you need a JOIN + COUNT
SELECT u.name, COUNT(o.id) as order_count
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.id = 42
GROUP BY u.name;

-- Denormalized: add order_count directly to users table
ALTER TABLE users ADD COLUMN order_count INTEGER DEFAULT 0;

-- Now the query is instant:
SELECT name, order_count FROM users WHERE id = 42;

-- Trade-off: you must update order_count every time an order
-- is created or deleted. If you forget → stale data.

-- Place an order: multiple operations that MUST succeed together

BEGIN TRANSACTION;

  -- 1. Check stock
  SELECT stock FROM products WHERE id = 42 FOR UPDATE;
  -- stock = 5, we need 2 → OK

  -- 2. Reduce stock
  UPDATE products SET stock = stock - 2 WHERE id = 42;

  -- 3. Create order
  INSERT INTO orders (user_id, total, status)
  VALUES (7, 199.98, 'confirmed');

  -- 4. Create order items
  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (currval('orders_id_seq'), 42, 2, 99.99);

  -- 5. Charge payment (if this fails, EVERYTHING rolls back)
  -- ... payment logic ...

COMMIT;

-- If ANY step fails → ROLLBACK
-- Stock is restored, order is not created, payment is not charged
-- The database is back to its original state

Table: products (id=42, stock=10, price=99.99)

Two concurrent transactions:

READ UNCOMMITTED:
  Tx A: UPDATE products SET price = 79.99 WHERE id = 42;  (not committed yet)
  Tx B: SELECT price FROM products WHERE id = 42;
  Tx B sees: 79.99  ← dirty read! Tx A might roll back.
  Tx A: ROLLBACK;
  Tx B used a price that never existed.

READ COMMITTED:
  Tx A: UPDATE products SET price = 79.99 WHERE id = 42;  (not committed yet)
  Tx B: SELECT price FROM products WHERE id = 42;
  Tx B sees: 99.99  ← only sees committed data ✅
  Tx A: COMMIT;
  Tx B: SELECT price FROM products WHERE id = 42;
  Tx B sees: 79.99  ← different value on second read (non-repeatable)

REPEATABLE READ:
  Tx B: SELECT price FROM products WHERE id = 42;
  Tx B sees: 99.99  ← snapshot taken
  Tx A: UPDATE products SET price = 79.99 WHERE id = 42; COMMIT;
  Tx B: SELECT price FROM products WHERE id = 42;
  Tx B sees: 99.99  ← still sees snapshot value ✅

SERIALIZABLE:
  Tx A and Tx B both try to update stock:
  Tx A: UPDATE products SET stock = stock - 1 WHERE id = 42;
  Tx B: UPDATE products SET stock = stock - 1 WHERE id = 42;
  → One succeeds, the other is ABORTED and must retry.
  → Guarantees no lost updates, no anomalies.

Table: users (1,000,000 rows)
Query: SELECT * FROM users WHERE email = 'alice@mail.com'

Without index (full table scan):
  → Read all 1,000,000 rows
  → Check each row's email
  → Time: O(n) = ~1,000,000 comparisons

With B-tree index on email:
  Level 1 (root):     "Is 'alice' < 'm'?"  → Go left
  Level 2:            "Is 'alice' < 'f'?"  → Go left
  Level 3:            "Is 'alice' < 'c'?"  → Go left
  Level 4 (leaf):     Found! Row pointer → disk location
  → Time: O(log n) = ~20 comparisons

That's 1,000,000 vs 20. This is why indexes matter.

CREATE INDEX idx_user_status ON orders(user_id, status);

-- ✅ Uses the index (leftmost prefix)
SELECT * FROM orders WHERE user_id = 42;

-- ✅ Uses the index (both columns)
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

-- ❌ Cannot use this index (skips user_id)
SELECT * FROM orders WHERE status = 'shipped';
-- For this query, you'd need a separate index on (status)

-- Think of it like a phone book:
-- Sorted by (last_name, first_name)
-- You can look up "Smith" → ✅
-- You can look up "Smith, John" → ✅
-- You can't look up just "John" efficiently → ❌

-- Slow query: "Find all orders for user 42"
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- BAD output (no index):
-- Seq Scan on orders  (cost=0.00..25000.00 rows=47 width=64)
--   Filter: (user_id = 42)
--   Rows Removed by Filter: 999953
--   Execution Time: 450.23 ms
-- ↑ Sequential scan = reading ALL 1M rows, filtering 999,953

-- Fix: add an index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- GOOD output (with index):
-- Index Scan using idx_orders_user_id on orders
--   (cost=0.42..8.44 rows=47 width=64)
--   Index Cond: (user_id = 42)
--   Execution Time: 0.12 ms
-- ↑ Index scan = jumped directly to 47 matching rows

-- ❌ N+1 Problem (application code makes N+1 queries):
-- Query 1: SELECT * FROM users LIMIT 50;
-- Query 2: SELECT * FROM orders WHERE user_id = 1;
-- Query 3: SELECT * FROM orders WHERE user_id = 2;
-- ...
-- Query 51: SELECT * FROM orders WHERE user_id = 50;
-- Total: 51 queries! Each has network round-trip overhead.

-- ✅ Fix: Single query with JOIN
SELECT u.id, u.name, o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (1, 2, 3, ..., 50);
-- Total: 1 query. Database handles the join efficiently.

-- ✅ Alternative fix: Batch query
SELECT * FROM users LIMIT 50;                          -- 1 query
SELECT * FROM orders WHERE user_id IN (1,2,3,...,50);  -- 1 query
-- Total: 2 queries. Application joins the results in memory.

users:          id | name | email (UNIQUE) | created_at
products:       id | name | price | stock | category_id (FK)
categories:     id | name
orders:         id | user_id (FK) | status | total | created_at
order_items:    id | order_id (FK) | product_id (FK) | quantity | unit_price
payments:       id | order_id (FK) | amount | method | status | paid_at
reviews:        id | user_id (FK) | product_id (FK) | rating | comment

-- Denormalize: add order_count to users (dashboard reads it 1000x/sec)
ALTER TABLE users ADD COLUMN order_count INTEGER DEFAULT 0;

-- Denormalize: add avg_rating to products (product listing page)
ALTER TABLE products ADD COLUMN avg_rating DECIMAL(2,1) DEFAULT 0;
ALTER TABLE products ADD COLUMN review_count INTEGER DEFAULT 0;

-- Keep updated via triggers or application logic:
-- On new order  → UPDATE users SET order_count = order_count + 1
-- On new review → UPDATE products SET avg_rating = ..., review_count = ...

-- Users: lookup by email (login)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Orders: filter by user (order history page)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Orders: filter by status (admin dashboard)
CREATE INDEX idx_orders_status ON orders(status);

-- Order items: lookup by order (order detail page)
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Products: filter by category + sort by price (product listing)
CREATE INDEX idx_products_category_price ON products(category_id, price);

-- Reviews: filter by product (product detail page)
CREATE INDEX idx_reviews_product_id ON reviews(product_id);

-- Payments: lookup by order (payment status check)
CREATE INDEX idx_payments_order_id ON payments(order_id);

BEGIN TRANSACTION;

  -- 1. Lock and check stock (FOR UPDATE prevents race conditions)
  SELECT stock FROM products WHERE id = 42 FOR UPDATE;
  -- stock = 5, need 2 → proceed

  -- 2. Reduce stock
  UPDATE products SET stock = stock - 2 WHERE id = 42;

  -- 3. Create order
  INSERT INTO orders (user_id, total, status)
  VALUES (7, 199.98, 'pending')
  RETURNING id;  -- returns order_id = 501

  -- 4. Create order items
  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (501, 42, 2, 99.99);

  -- 5. Create payment record
  INSERT INTO payments (order_id, amount, method, status)
  VALUES (501, 199.98, 'credit_card', 'pending');

  -- 6. Update denormalized count
  UPDATE users SET order_count = order_count + 1 WHERE id = 7;

COMMIT;
-- If ANY step fails → ROLLBACK (stock restored, no orphan records)

-- Product listing (uses composite index on category_id, price)
SELECT id, name, price, avg_rating
FROM products
WHERE category_id = 5
ORDER BY price ASC
LIMIT 20 OFFSET 0;

-- Order history (uses index on user_id)
SELECT o.id, o.total, o.status, o.created_at
FROM orders o
WHERE o.user_id = 7
ORDER BY o.created_at DESC
LIMIT 10;

-- Order detail (uses indexes on order_id for both tables)
SELECT oi.quantity, oi.unit_price, p.name, p.price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 501;

-- Dashboard: top customers (uses denormalized order_count)
SELECT name, order_count
FROM users
ORDER BY order_count DESC
LIMIT 10;
-- Without denormalization, this would need a JOIN + GROUP BY on millions of rows