# Database Normalization

Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.

## 1. The Goals

1.  **Eliminate Redundant Data:** Storing the same data in more than one place wastes space and creates maintenance nightmares (Update Anomalies).
2.  **Ensure Data Dependencies Make Sense:** Data should be stored logically.

## 2. The Normal Forms

Normalization is progressive. To be in 2NF, you must first be in 1NF.

### First Normal Form (1NF)
*   **Atomicity:** Each column must contain atomic values (no lists or comma-separated strings).
*   **Uniqueness:** Each row must be unique (Primary Key).
*   **Example Violation:** Storing "Red, Blue" in a `Colors` column.
*   **Fix:** Create a separate row for "Red" and "Blue", or a separate table.

### Second Normal Form (2NF)
*   **Prerequisite:** Must be in 1NF.
*   **No Partial Dependencies:** All non-key attributes must depend on the *entire* primary key. This applies mainly when the Primary Key is composite (made of multiple columns).
*   **Example Violation:** Table `OrderItems` has PK `(OrderId, ItemId)` and columns `ItemName`, `ItemPrice`. `ItemPrice` depends only on `ItemId`, not `OrderId`.
*   **Fix:** Move `ItemName` and `ItemPrice` to a separate `Items` table.

### Third Normal Form (3NF)
*   **Prerequisite:** Must be in 2NF.
*   **No Transitive Dependencies:** Non-key attributes must not depend on other non-key attributes.
*   **Example Violation:** Table `Users` has `ZipCode` and `City`. `City` depends on `ZipCode`. If `ZipCode` changes, `City` must change.
*   **Fix:** Move `ZipCode` and `City` to a `Locations` table.

### Boyce-Codd Normal Form (BCNF)
A slightly stronger version of 3NF. It handles rare cases where a non-key attribute determines a part of the primary key.

## 3. Denormalization

Normalization is great for data integrity (Write-Optimized), but it can hurt read performance because retrieving data requires joining many tables.

**Denormalization** is the process of intentionally adding redundancy to the database to speed up reads.
*   **Example:** Storing `UserCount` on a `Group` table instead of running `SELECT COUNT(*) FROM Users WHERE GroupID = ?` every time.
*   **Trade-off:** You gain read speed but lose write speed (you have to update `UserCount` every time a user joins/leaves) and risk data inconsistency.

## 4. Summary

| Form | Rule | Mnemonic |
| :--- | :--- | :--- |
| **1NF** | Atomic values | The Key |
| **2NF** | No partial dependencies | The Whole Key |
| **3NF** | No transitive dependencies | And Nothing But The Key |

> "The data depends on the key, the whole key, and nothing but the key, so help me Codd."

[[programming/database-basics]]
[[programming/database-indexing]]
[[programming/database-sharding]]