SQL - Normalization & Design

Overview

Normalization reduces redundancy and anomalies by structuring data into well-defined relations. Most OLTP schemas aim for 3NF or BCNF; analytics may denormalize for performance.

Normal Forms

• 1NF: Atomic columns, no repeating groups.
• 2NF: 1NF + no partial dependency on subset of a composite key.
• 3NF: 2NF + no transitive dependencies (non-key depends only on key).
• BCNF: Every determinant is a candidate key.

Example: Orders Schema

customers(customer_id PK, name, email)
products(product_id PK, name, price)
orders(order_id PK, customer_id FK, order_date)
order_items(order_id FK, product_id FK, qty, unit_price, PK(order_id, product_id))

Denormalization

Trade storage and write complexity to speed reads: add aggregates, pre-join tables, or cache JSON blobs. Ensure consistency via triggers, app logic, or batch recomputation.

-- Constraints and indexes example
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(100)
);
CREATE INDEX idx_orders_cust ON orders(customer_id);

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(100)
);
CREATE INDEX IX_Orders_Customer ON dbo.orders(customer_id);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT UNIQUE,
  name TEXT
);
CREATE INDEX idx_orders_cust ON orders(customer_id);