SQL Normalization: Syntax, Usage, and Examples

SQL normalization is a process used to organize a relational database by reducing redundancy and improving data integrity. It involves breaking down large tables into smaller, related tables while defining relationships between them. This minimizes data anomalies and ensures efficient database design.

How to Use SQL Normalization

Normalization in SQL follows a set of rules known as normal forms (NF). Each form eliminates specific types of redundancy and dependency issues. The most common normal forms are:

• First Normal Form (1NF) – Eliminates duplicate columns and ensures that each column contains atomic (indivisible) values.
• Second Normal Form (2NF) – Removes partial dependencies by ensuring that all non-key attributes depend on the entire primary key.
• Third Normal Form (3NF) – Eliminates transitive dependencies, meaning non-key attributes should not depend on other non-key attributes.
• Boyce-Codd Normal Form (BCNF) – A stricter version of 3NF, ensuring that every determinant is a candidate key.
• Fourth Normal Form (4NF) and Fifth Normal Form (5NF) – Further eliminate redundancy by dealing with multi-valued dependencies and join dependencies.

When to Use SQL Normalization

Reducing Data Redundancy

Normalization prevents duplicate data by ensuring that each piece of information is stored in only one place. This reduces storage costs and improves efficiency.

Example: Instead of storing customer information repeatedly in an Orders table, it can be stored in a separate Customers table.

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Maintaining Data Integrity

When data is duplicated, it increases the risk of inconsistencies. Normalization ensures that updates, deletions, and insertions occur in a controlled manner, preventing anomalies.

Example: If a customer changes their email, the update needs to happen in only one place instead of multiple locations.

UPDATE Customers SET email = 'newemail@example.com' WHERE customer_id = 1;

Improving Query Performance

Smaller, well-structured tables allow SQL queries to execute faster, especially for SELECT, JOIN, and WHERE operations.

Example: Instead of scanning a large Orders table with repeated customer data, we can use a JOIN to retrieve the necessary information efficiently.

SELECT Orders.order_id, Customers.name, Customers.email
FROM Orders
JOIN Customers ON Orders.customer_id = Customers.customer_id;

Examples of SQL Normalization in Action

First Normal Form (1NF) – Ensuring Atomicity

A table must not contain repeating columns or groups of values.

Before 1NF:

| OrderID | CustomerName | Items | | --- | --- | --- | | 1 | John Doe | Phone, Laptop | | 2 | Jane Smith | Tablet, Headphones |

After 1NF (Separate Table for Orders and Items):

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE OrderItems (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    item_name VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

Second Normal Form (2NF) – Removing Partial Dependencies

All non-key columns must depend on the whole primary key, not just part of it.

Before 2NF:

| OrderID | CustomerID | CustomerName | ItemID | ItemName | | --- | --- | --- | --- | --- | | 1 | 101 | John Doe | 201 | Phone | | 2 | 102 | Jane Smith | 202 | Tablet |

After 2NF (Separate Customers, Orders, and Items Tables):

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE OrderItems (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    item_name VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

Third Normal Form (3NF) – Eliminating Transitive Dependencies

Non-key attributes should not depend on other non-key attributes.

Before 3NF:

| EmployeeID | EmployeeName | DepartmentID | DepartmentName | | --- | --- | --- | --- | | 1 | Alice | D01 | HR | | 2 | Bob | D02 | IT |

After 3NF (Separate Departments Table):

CREATE TABLE Departments (
    department_id VARCHAR(10) PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id VARCHAR(10),
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

Learn More About SQL Normalization

Understanding Normalization vs. Denormalization

• Normalization minimizes redundancy and improves data integrity but can slow down read-heavy queries due to multiple joins.
• Denormalization reduces joins by allowing some redundancy, improving read performance but increasing storage requirements.

For example, denormalization might store the customer’s name in the Orders table to avoid joining the Customers table every time.

sql
CopyEdit
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100), -- Denormalized column
    order_date DATE
);

When to Normalize and When to Avoid Over-Normalization

• Use normalization for transactional databases (OLTP) where data integrity is critical.
• Use denormalization for analytical databases (OLAP) where read performance matters more than redundancy.

Checking if a Database is Normalized

To check for normalization violations, look for:

• Repeated columns (break into separate tables).
• Partial dependencies (ensure every column depends on the full primary key).
• Transitive dependencies (ensure non-key attributes only depend on the primary key).

Example query to detect redundancy in a customer orders table:

SELECT customer_id, COUNT(DISTINCT customer_name)
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT customer_name) > 1;

If this returns results, customer names are stored redundantly and should be normalized.