Database Normalization

Before (Inefficient Query)

-- Unnormalized table with redundant data
CREATE TABLE orders_bad (
    order_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_address VARCHAR(200),
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    quantity INT,
    order_date DATE
);
-- Insert with massive redundancy
INSERT INTO orders_bad VALUES 
(1, 'John Doe', 'john@email.com', '123 Main St', 'Laptop', 999.99, 1, '2024-01-15'),
(1, 'John Doe', 'john@email.com', '123 Main St', 'Mouse', 29.99, 2, '2024-01-15'),
(1, 'John Doe', 'john@email.com', '123 Main St', 'Keyboard', 79.99, 1, '2024-01-15');

After (Optimized Query)

-- Normalized tables following 3NF
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_address VARCHAR(200)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10,2)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT FOREIGN KEY REFERENCES customers(customer_id),
    order_date DATE
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT FOREIGN KEY REFERENCES orders(order_id),
    product_id INT FOREIGN KEY REFERENCES products(product_id),
    quantity INT
);

Why This Optimization Works

1. Eliminate Data Redundancyi: Normalization organizes data into separate tables, each with a specific purpose. This prevents the same information from being stored multiple times.

2. Improve Data Integrityi: With normalized tables, updates only need to happen in one place. This reduces the risk of inconsistent data.

3. Better Storage Efficiencyi: Normalized databases typically use less storage because redundant data is eliminated.

4. Easier Maintenancei: Changes to the database structure are easier to make when data is properly organized.

Normalization Forms

First Normal Form (1NF)i

A table is in 1NF when:

• Each column contains atomic (indivisible) values
• Each column contains values of a single type
• Each row is unique
• Order of data doesn't matter

-- Bad: Non-atomic values
CREATE TABLE employees_bad (
    id INT,
    name VARCHAR(100),
    phones VARCHAR(50)  -- Contains multiple phone numbers
);

-- Good: 1NF compliant
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE phone_numbers (
    employee_id INT,
    phone_number VARCHAR(20),
    PRIMARY KEY (employee_id, phone_number)
);

Second Normal Form (2NF)i

A table is in 2NF when:

• It is in 1NF
• All non-key columns are fully dependent on the entire primary key

-- Bad: Partial dependency on composite key
CREATE TABLE order_items_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- Depends only on product_id, not the full key
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Good: 2NF compliant (split into two tables)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Third Normal Form (3NF)i

A table is in 3NF when:

• It is in 2NF
• No transitive dependencies (non-key columns should not depend on other non-key columns)

-- Bad: Transitive dependency
CREATE TABLE employees_bad (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100)  -- Depends on department_id, not employee_id
);

-- Good: 3NF compliant
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT REFERENCES departments(department_id)
);

Boyce-Codd Normal Form (BCNF)i

A table is in BCNF when:

• It is in 3NF
• For every dependency A → B, A should be a super key

-- Bad: Violates BCNF
CREATE TABLE course_assignments_bad (
    student_id INT,
    course_id INT,
    instructor_id INT,
    PRIMARY KEY (student_id, course_id)
);
-- Problem: instructor_id → course_id (instructor teaches one course)
-- But instructor_id is not a super key

-- Good: BCNF compliant
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor_id INT
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT PRIMARY KEY,
    PRIMARY KEY (student_id, course_id)
);

Fourth Normal Form (4NF)i

A table is in 4NF when:

• It is in BCNF
• No multi-valued dependencies exist (each attribute depends on the key, the whole key, and nothing but the key)

-- Bad: Multi-valued dependency
CREATE TABLE student_skills_bad (
    student_id INT,
    skill VARCHAR(50),
    hobby VARCHAR(50),
    PRIMARY KEY (student_id, skill, hobby)
);
-- Problem: student can have multiple skills AND hobbies
-- These are independent multi-valued facts

-- Good: 4NF compliant (separate into two tables)
CREATE TABLE student_skills (
    student_id INT,
    skill VARCHAR(50),
    PRIMARY KEY (student_id, skill)
);

CREATE TABLE student_hobbies (
    student_id INT,
    hobby VARCHAR(50),
    PRIMARY KEY (student_id, hobby)
);

Fifth Normal Form (5NF)i

A table is in 5NF when:

• It is in 4NF
• It cannot be decomposed into smaller tables without losing data

-- Bad: Cannot be decomposed further without losing information
CREATE TABLE orders_products (
    order_id INT,
    product_id INT,
    supplier_id INT,
    PRIMARY KEY (order_id, product_id, supplier_id)
);
-- The relationship between order, product, and supplier
-- requires all three to be meaningful

-- Good: 5NF compliant (decompose into three tables)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE product_suppliers (
    product_id INT,
    supplier_id INT,
    PRIMARY KEY (product_id, supplier_id)
);

When NOT to Normalize

Sometimes denormalization is better:

Scenario	Why Denormalize
Read-heavy applications	Fewer JOINs mean faster readsi
Analytics/Reporting	Aggregated data performs betteri
Caching frequently accessed data	Reduces query complexityi
Legacy systems	Migration may be impracticali
Specific performance needs	Benchmark-driven decisionsi

Normalization vs Denormalization

Aspect	Normalized	Denormalized
Data Redundancy	Low	High
Write Performance	Good	Poor
Read Performance	Poor (JOINs)	Goodi
Storage	Efficient	May waste space
Integrity	Easy to maintain	Harder to maintain

Quick Reference

Form	Key Rule	Eliminates
1NF	Atomic values	Repeating groups
2NF	Full dependency	Partial dependencies
3NF	No transitive deps	Transitive dependencies
BCNF	Super key only	BCNF violations
4NF	No multi-valued deps	Multi-valued dependencies
5NF	No join anomalies	Reconstruction loss