.. This file assumes the two SVGs (normalization-ladder.svg and final-schema.svg) live in a ``diagrams/`` folder next to this .rst file. Adjust the image paths below if your project structure differs. .. _database-normalization: ======================== Database Normalization ======================== Database normalization is the process of organizing tables and columns in a relational database to minimize data redundancy and avoid a family of update, insertion, and deletion anomalies. It works by applying a series of rules — called *normal forms* — each stricter than the last, that reshape a single wide table into a set of smaller, well-defined tables connected by keys. This guide starts with the intuition and worked examples in :ref:`part-1-basics`, then moves into the formal theory in :ref:`part-2-deeper` for readers who want the full picture. .. _part-1-basics: Part 1: The Basics =================== Why Normalize? --------------- An unnormalized table tends to produce three kinds of problems, known as **anomalies**: - **Update anomaly** — the same fact is stored in more than one row, so changing it means updating every copy, and it's easy for copies to drift out of sync. - **Insertion anomaly** — you can't record a new fact (a new product, say) until an unrelated fact (an order for it) also exists. - **Deletion anomaly** — deleting one fact (the last order for a customer) accidentally erases another fact entirely (that the customer exists at all). Normalization removes these problems by making sure each table describes exactly one kind of thing, and every column depends on nothing but that table's key. The Starting Point: One Big Table ----------------------------------- Imagine an order-tracking system that started life as a single spreadsheet: .. list-table:: Orders (unnormalized) :header-rows: 1 :widths: 10 18 14 12 26 14 * - OrderID - CustomerName - CustomerCity - CustomerState - Products - OrderDate * - 1001 - Alice Chen - Rochester - NY - Widget A, Gadget B - 2026-01-14 * - 1002 - Alice Chen - Rochester - NY - Gadget B - 2026-02-02 * - 1003 - Marcus Diaz - Buffalo - NY - Widget A, Cable C, Gadget B - 2026-02-10 Already you can spot the trouble: - The ``Products`` column crams a variable-length list into one cell — a **repeating group**. - Alice Chen's city and state are copied on every row she appears in. - There's no way to record a new product's price until someone orders it. .. note:: Every one of these issues maps directly to a normal form below. That's not a coincidence — each normal form exists to close one specific gap. The Normalization Ladder --------------------------- .. image:: diagrams/normalization-ladder.svg :alt: Diagram showing the progression from unnormalized data through 1NF, 2NF, 3NF, to BCNF, with the anomaly each step removes. :align: center :width: 100% Each rung fixes one thing and leaves the fixes below it intact — you can't skip a step, because 2NF is only meaningful once the data is already in 1NF, and so on. Step 1: First Normal Form (1NF) — Atomic Values --------------------------------------------------- **Rule:** every column holds a single, indivisible value. No comma-separated lists, no repeating groups of columns. Splitting the ``Products`` list into one row per product gets us to 1NF: .. list-table:: After 1NF :header-rows: 1 :widths: 10 18 14 12 16 14 * - OrderID - CustomerName - CustomerCity - CustomerState - Product - OrderDate * - 1001 - Alice Chen - Rochester - NY - Widget A - 2026-01-14 * - 1001 - Alice Chen - Rochester - NY - Gadget B - 2026-01-14 * - 1002 - Alice Chen - Rochester - NY - Gadget B - 2026-02-02 * - 1003 - Marcus Diaz - Buffalo - NY - Widget A - 2026-02-10 * - 1003 - Marcus Diaz - Buffalo - NY - Cable C - 2026-02-10 * - 1003 - Marcus Diaz - Buffalo - NY - Gadget B - 2026-02-10 Every cell is now atomic. But notice the row's identity is no longer just ``OrderID`` — it's the *combination* of ``OrderID`` and ``Product``. That composite key is exactly what the next form deals with, and the customer redundancy is now worse, not better. 1NF only ever promises atomic values; it doesn't promise a clean design. Step 2: Second Normal Form (2NF) — Remove Partial Dependencies -------------------------------------------------------------------- **Rule:** applies only when a table's primary key has more than one column. Every other column must depend on the *whole* key, not just part of it. Suppose we also track each product's price on the order-line table, which now has the composite key ``(OrderID, Product)``: .. list-table:: OrderDetails (before 2NF) :header-rows: 1 :widths: 15 25 25 15 * - OrderID (PK) - Product (PK) - ProductPrice - Quantity * - 1001 - Widget A - 12.50 - 2 * - 1001 - Gadget B - 7.00 - 1 * - 1002 - Gadget B - 7.00 - 3 ``ProductPrice`` only depends on ``Product`` — it doesn't care what ``OrderID`` it's attached to. That's a **partial dependency**, and it means Widget A's price is duplicated on every order line that includes it. Only ``Quantity`` genuinely depends on the full ``(OrderID, Product)`` pair. The fix is to split off the part that depends on only half the key: .. list-table:: Products :header-rows: 1 :widths: 40 30 * - Product (PK) - ProductPrice * - Widget A - 12.50 * - Gadget B - 7.00 * - Cable C - 4.25 .. list-table:: OrderDetails (after 2NF) :header-rows: 1 :widths: 20 20 15 * - OrderID (PK) - Product (PK, FK) - Quantity * - 1001 - Widget A - 2 * - 1001 - Gadget B - 1 * - 1002 - Gadget B - 3 Now each product's price is stored exactly once. Step 3: Third Normal Form (3NF) — Remove Transitive Dependencies ------------------------------------------------------------------ **Rule:** every non-key column must depend *only* on the key — not on another non-key column. Look back at the customer columns. In a simplified ``Orders`` table: .. list-table:: Orders (before 3NF) :header-rows: 1 :widths: 12 20 16 14 18 * - OrderID (PK) - CustomerName - CustomerCity - CustomerState - OrderDate * - 1001 - Alice Chen - Rochester - NY - 2026-01-14 * - 1002 - Alice Chen - Rochester - NY - 2026-02-02 * - 1003 - Marcus Diaz - Buffalo - NY - 2026-02-10 ``CustomerCity`` and ``CustomerState`` don't really depend on ``OrderID`` — they depend on *which customer* placed the order. In other words: ``OrderID → CustomerName → CustomerCity``. That chain is a **transitive dependency**, and it's why Alice's city and state show up twice. The fix, same pattern as before — split off the part that depends on something other than the key, and give it its own key: .. list-table:: Customers :header-rows: 1 :widths: 15 25 20 15 * - CustomerID (PK) - CustomerName - CustomerCity - CustomerState * - C01 - Alice Chen - Rochester - NY * - C02 - Marcus Diaz - Buffalo - NY .. list-table:: Orders (after 3NF) :header-rows: 1 :widths: 15 20 20 * - OrderID (PK) - CustomerID (FK) - OrderDate * - 1001 - C01 - 2026-01-14 * - 1002 - C01 - 2026-02-02 * - 1003 - C02 - 2026-02-10 Putting It All Together -------------------------- Combine the two decompositions above and the schema settles into four focused tables: .. image:: diagrams/final-schema.svg :alt: Entity-relationship diagram of the final normalized schema with Customers, Orders, OrderDetails, and Products tables connected by foreign keys. :align: center :width: 100% Every table now describes exactly one thing — a customer, an order, a product, or a line item — and every fact lives in exactly one place. Quick Reference ------------------ .. list-table:: :header-rows: 1 :widths: 15 45 40 * - Step - Rule - Removes * - → 1NF - Every column holds one atomic value; no repeating groups. - Multi-valued / repeating-group columns. * - → 2NF - Every non-key column depends on the *whole* primary key (matters only for composite keys). - Partial dependencies. * - → 3NF - Every non-key column depends *only* on the key, not on another non-key column. - Transitive dependencies. .. _part-2-deeper: Part 2: Going Deeper ======================= Everything above works from intuition. The formal theory beneath it — built on **functional dependencies** — is what lets you *prove* a design is correct instead of just eyeballing it, and it's what the stricter normal forms (BCNF, 4NF, 5NF) are built on. Functional Dependencies, Formally ------------------------------------- A **functional dependency** ``X → Y`` holds on a table when, for any two rows that share the same value of ``X``, they must also share the same value of ``Y``. Read it as "X determines Y." ``ProductID → ProductPrice`` is a functional dependency: given a product, its price is fixed. ``X`` is called the **determinant**. Keys: Superkey, Candidate Key, Primary Key ----------------------------------------------- - A **superkey** is any set of columns that uniquely identifies a row. - A **candidate key** is a *minimal* superkey — remove any column from it and it stops being unique. - A **primary key** is simply the candidate key a designer chooses as the table's main identifier. A table can have several candidate keys but only one primary key. Full vs. Partial Dependency -------------------------------- For a table with composite key ``(A, B)``, a non-key column ``C`` is: - **Fully dependent** on ``(A, B)`` if it depends on the pair together, and not on ``A`` or ``B`` alone. - **Partially dependent** if it actually only depends on ``A`` or only on ``B``. 2NF is precisely the requirement that no partial dependencies exist. Transitive Dependency, Formally ------------------------------------ Given ``X → Y`` and ``Y → Z``, where ``X`` is the key and ``Y`` is a non-key column, ``Z`` is **transitively dependent** on ``X`` through ``Y``. Codd's original definition of 3NF forbids this for any ``Z`` that isn't itself part of some candidate key — in practice, that exception rarely matters for the tables most applications design. Boyce-Codd Normal Form (BCNF) ---------------------------------- **Rule:** for every functional dependency ``X → Y`` in the table, ``X`` must be a superkey. No exceptions. BCNF is 3NF with the loophole closed. It's needed when a table has more than one *overlapping* candidate key. Classic example — a table tracking which instructor teaches which course to which student, where: - Each course can be taught by several instructors. - Each instructor teaches only one course. - A student can take a course from any of its instructors. .. list-table:: StudentCourseInstructor :header-rows: 1 :widths: 33 33 34 * - Student - Course - Instructor * - Alice Chen - Databases - Dr. Kim * - Alice Chen - Databases - Dr. Okafor * - Marcus Diaz - Databases - Dr. Kim Here, ``(Student, Course)`` is a candidate key, but so is ``(Student, Instructor)`` — and ``Instructor → Course`` holds on its own (each instructor belongs to one course). That table is already in 3NF (``Course`` is part of a candidate key, satisfying Codd's exception), but it fails BCNF, because ``Instructor`` is a determinant and not a superkey by itself. The fix is the same decomposition pattern as before: .. list-table:: StudentInstructor :header-rows: 1 :widths: 50 50 * - Student - Instructor * - Alice Chen - Dr. Kim * - Alice Chen - Dr. Okafor * - Marcus Diaz - Dr. Kim .. list-table:: InstructorCourse :header-rows: 1 :widths: 50 50 * - Instructor - Course * - Dr. Kim - Databases * - Dr. Okafor - Databases Beyond BCNF: 4NF and 5NF ------------------------------ These come up rarely in ordinary application design, but are worth knowing by name: - **Fourth Normal Form (4NF)** eliminates *multivalued dependencies* — cases where a table stores two independent multi-valued facts about the same entity (say, a person's phone numbers and their email addresses) in one table, which forces every combination to be spelled out. The fix is to split the two facts into separate tables. - **Fifth Normal Form (5NF)**, also called *Project-Join Normal Form*, handles tables that can only be losslessly reconstructed by joining three or more smaller tables, not two. It mostly matters for ternary relationships like supplier–part–project associations, and is largely a theoretical concern for most schemas. Denormalization: Breaking the Rules on Purpose ---------------------------------------------------- Once a schema is properly normalized, it's common to *deliberately* reintroduce some redundancy — this is **denormalization**, and it's a performance trade-off, not a shortcut around design: - **Read-heavy reporting** workloads may pre-join or cache aggregates to avoid expensive joins on every query. - **Star-schema data warehouses** keep wide, redundant fact and dimension tables because they're optimized for analytical scans, not transactional integrity. - **Materialized views** store the result of an expensive query so it doesn't need to be recomputed every time. The rule of thumb: normalize first, understand exactly what redundancy you're reintroducing, and document why. Summary Table of All Normal Forms --------------------------------------- .. list-table:: :header-rows: 1 :widths: 15 85 * - Normal Form - Requirement * - 1NF - Every column holds an atomic value; no repeating groups. * - 2NF - 1NF, plus no non-key column partially depends on a composite key. * - 3NF - 2NF, plus no non-key column transitively depends on another non-key column. * - BCNF - 3NF, plus every determinant is a candidate key (no exceptions). * - 4NF - BCNF, plus no multivalued dependencies. * - 5NF - 4NF, plus no join dependencies beyond what the candidate keys already imply. .. tip:: In practice, most well-designed application schemas stop at 3NF or BCNF. 4NF and 5NF are good to recognize when you see the symptoms, but rarely something you need to design toward from scratch.