Normalization in SQL

The Theory: Why Normalization Exists Database Normalization is the formal process of structuring a relational database to reduce data redundancy and improve data integrity. It is governed by a set of rules called Normal Forms (NF) . The Problem: Why we normalize If data is not normalized, you encounter Data Anomalies during your INSERT , UPDATE , and DELETE operations: Insertion Anomaly: You cannot add data because a required field is missing. (Example: You cannot add a student to the system until they are enrolled in a class). Update Anomaly: You have redundant data. If you change a value in one place but not another, your database becomes inconsistent. (Example: Updating a teacher's name in one record but not another). Deletion Anomaly: You lose data you didn't intend to delete. (Example: Deleting a student who is the only person in a class results in the total loss of information about that class). 2. The Normal Forms: The Hierarchy of Integrity Normalization is cumulative. To be in 3NF, you must first satisfy 1NF and 2NF. First Normal Form (1NF): Atomicity The Rule: Data must be atomic (indivisible). No lists, no arrays, and no repeating columns (e.g., phone1 , phone2 ). Every row must be identified by a Primary Key (PK). Violated State: A cell contains "Math, Science, History". 1NF State: Each class is in its own row. Second Normal Form (2NF): Full Dependency The Rule: Must be in 1NF, and all non-key columns must depend on the entire Primary Key. This only applies when you have a Composite Primary Key (a key made of multiple columns). Violated State: Table has PK (Student_ID, Class_ID) and includes Teacher_Name . The Teacher_Name only depends on Class_ID , not the Student_ID . This is a partial dependency . 2NF State: Move Teacher_Name to a separate Classes table. Third Normal Form (3NF): Direct Dependency Third Normal Form is a database normalization standard that builds on the first two normal forms. A table is in 3NF when it satisfies two conditions: It's already in Second Normal Form (2NF) No transitive dependencies exist — non-key columns must depend only on the primary key, not on other non-key columns What's a Transitive Dependency? A transitive dependency occurs when: Column A → Column B → Column C meaning Column C depends on Column B, which depends on Column A (the primary key). Column C should not be in the same table. Example: Violating 3NF Consider an Orders table: OrderID (PK) CustomerID CustomerCity ProductID 1 C01 Mumbai P10 2 C02 Delhi P11 3 C01 Mumbai P12 The problem: CustomerCity depends on CustomerID, not directly on OrderID. This is a transitive dependency: OrderID → CustomerID → CustomerCity Issues this causes: Update anomaly — if a customer moves cities, you must update multiple rows Deletion anomaly — deleting an order could lose the city information Insertion anomaly — you can't store a customer's city without an order The Rule: Must be in 2NF, and no non-key column should depend on another non-key column. All columns must relate only to the Primary Key. Violated State: Table has (Student_ID, ZipCode, City) . City depends on ZipCode , which depends on Student_ID . This is a transitive dependency . 3NF State: Move ZipCode and City to a separate Locations table. 4. When to stop (Denormalization) While 3NF is the gold standard for Data Integrity , it can hurt performance in massive databases because you must run many JOIN operations to get a complete view of the data. Denormalization is the strategic, intentional process of putting redundant data back in to speed up "Read" operations (like reporting). For example, if you have millions of rows, you might store the Instructor name directly in the Enrollments table just to avoid the JOIN with the Classes table, acknowledging that you will now need extra code to keep that data in sync.