Week 3, Day 2: Database Normalization Explained

Welcome to Day 2 of Database Decoded Week 3! Today, we’re tackling database normalization—a systematic way to structure your tables to reduce redundancy and ensure data integrity. But we’ll also address a critical question: When does normalization hurt performance? Let’s dive in with real-world examples and practical SQL code.

Why Normalization Matters

Imagine an e-commerce app where:

• A single Orders table stores customer names, product details, and shipping addresses repeatedly.

• Every time a customer updates their address, you must edit 100+ orders.

• A typo in a product name creates inconsistent reports.

Normalization fixes these issues by:

• Eliminating duplicate data.

• Preventing update anomalies (e.g., inconsistent addresses).

• Simplifying queries.

But over-normalization can lead to excessive joins and slow performance. Let’s find the balance!

Topics Covered

1. First Normal Form (1NF)

Rules:

• Each column holds atomic values (indivisible).

• No repeating groups.

Real-World Example:
Unnormalized Table: Orders

Problem: The Products column stores multiple values as a string.

1NF Solution:

CREATE TABLE Orders (  
    OrderID INT,  
    CustomerID INT,  
    OrderDate DATE,  
    PRIMARY KEY (OrderID)  
);  

CREATE TABLE OrderDetails (  
    OrderDetailID INT PRIMARY KEY,  
    OrderID INT,  
    ProductName VARCHAR(50),  
    Quantity INT,  
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)  
);  

Now, each product is a separate row:

2. Second Normal Form (2NF)

Rules:

• Meet 1NF.

• Remove partial dependencies (all non-key columns depend on the entire primary key).

Real-World Example:
Violation: In OrderDetails, if ProductPrice depends only on ProductName (not the full key OrderDetailID), it creates redundancy.

2NF Solution:
Split into two tables:

CREATE TABLE Products (  
    ProductID INT PRIMARY KEY,  
    ProductName VARCHAR(50),  
    Price DECIMAL(10,2)  
);  

CREATE TABLE OrderDetails (  
    OrderDetailID INT PRIMARY KEY,  
    OrderID INT,  
    ProductID INT,  
    Quantity INT,  
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),  
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)  
);  

Now, Price is stored once in Products, not repeated in every order.

3. Third Normal Form (3NF)

Rules:

• Meet 2NF.

• Remove transitive dependencies (non-key columns depend only on the primary key).

Real-World Example:
Violation: A Customers table with City and ZipCode, where City depends on ZipCode, not directly on CustomerID.

3NF Solution:

CREATE TABLE Customers (  
    CustomerID INT PRIMARY KEY,  
    Name VARCHAR(50),  
    ZipCode VARCHAR(10),  
    FOREIGN KEY (ZipCode) REFERENCES Locations(ZipCode)  
);  

CREATE TABLE Locations (  
    ZipCode VARCHAR(10) PRIMARY KEY,  
    City VARCHAR(50),  
    State VARCHAR(50)  
);  

Now, updating a city name requires changing only one row in Locations.

Balancing Normalization with Performance

When to Denormalize:

• Reporting Databases: Frequent reads with complex joins? Duplicate data for faster queries.

• Caching: Store precomputed results (e.g., total sales per month).

• Small Tables: Over-normalizing tiny lookup tables adds unnecessary complexity.

Example:
A SalesSummary table for dashboards:

CREATE TABLE SalesSummary (  
    Month DATE,  
    TotalSales DECIMAL(15,2),  -- Denormalized for quick access  
    AverageOrderValue DECIMAL(10,2)  
);  

Practice Tasks

1. Normalize a Table:

   • Take this denormalized EmployeeProjects table and split it into 3NF:

     EmpID	EmpName	ProjectName	ProjectBudget
     1	Alice	Alpha	$50,000
     1	Alice	Beta	$75,000

2. Denormalize for Performance:

   • Create a CustomerOrderSummary table that combines Customers, Orders, and OrderDetails for faster reporting.

3. Analyze a Query:

   • Compare the performance of a JOIN across 3 normalized tables vs. a denormalized table.

Key Takeaways

• 1NF: Eliminate repeating groups and ensure atomic values.

• 2NF: Remove partial dependencies by splitting tables.

• 3NF: Eliminate transitive dependencies.

• Denormalize Strategically: Optimize for read-heavy workloads.