Normalization vs Denormalization (1NF, 2NF, 3NF, BCNF)

Database Normalization is the systematic process of organizing tables and column structures in a relational schema to:

1. Eliminate Redundant Data: Prevent the same string (e.g., customer address or product name) from being duplicated across millions of rows, conserving storage and memory.
2. Prevent Modification Anomalies:
   • Update Anomaly: If a customer updates their address, but the address is stored in 500 order rows, updating 499 rows and failing on 1 leaves the database corrupted and inconsistent.
   • Insertion Anomaly: Cannot record an item price without inventing a dummy customer order.
   • Deletion Anomaly: Deleting the last order placed by a customer accidentally wipes out the customer's profile from the database.

First Normal Form (1NF: Atomic Values)

• Every column must contain atomic (indivisible) scalar values (no comma-separated arrays or nested objects like phone_numbers = "555-1234, 555-5678").
• Every row must be uniquely identifiable via a Primary Key.

Second Normal Form (2NF: No Partial Dependencies)

• Must be in 1NF.
• All non-key columns must depend on the entire Primary Key, not just a subset of a composite key. (e.g., in a table with composite key (StudentID, CourseID), the column CourseName depends only on CourseID, violating 2NF; it must be split into a separate Courses table).

Third Normal Form (3NF: No Transitive Dependencies)

• Must be in 2NF.
• No non-key column can depend on another non-key column (A → B → C).
• Example: In an Orders table, storing ZipCode and CityName violates 3NF because CityName depends on ZipCode, which depends on OrderID. CityName must be moved to a dedicated ZipCodes lookup table.

Boyce-Codd Normal Form (BCNF)

A stricter version of 3NF where every determinant (a column that determines another column) must be a candidate superkey.

While 3NF is the gold standard for transactional write integrity (OLTP), strictly normalized schemas require heavy multi-table SQL JOINs to reconstruct data for user interfaces:

sql
Copy
-- 3NF Query: Requires 4 table scans and 3 physical join operators
SELECT users.name, orders.id, products.name, order_items.quantity
FROM orders
JOIN users ON orders.user_id = users.id
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON order_items.product_id = products.id
WHERE orders.id = 1042;

When to Denormalize:

1. High Read-to-Write Ratios (e.g., 100:1): If a social media timeline or product catalog is read 1,000,000 times for every 1 write, pre-computing and embedding author data directly in the post document eliminates expensive JOINs at query time.
2. Analytical Data Warehouses (OLAP / Star Schema): Systems like Snowflake, BigQuery, and ClickHouse denormalize data into massive "Fact" and "Dimension" tables (Star/Snowflake Schemas) to maximize columnar scan speeds.
3. Distributed Sharded Databases: In sharded architectures, executing JOINs across servers located in different network racks requires slow cross-network RPCs. Denormalizing data into self-contained documents eliminates cross-shard joins.