Normalization (1NF–3NF)
Overview
Normalization is the academic process of mathematically structuring a database to eliminate redundant data and ensure data dependencies make sense. If you store a user's address directly on 50 different Order rows, and they move to a new house, you have to update 50 rows (an 'Update Anomaly'). Normalization splits data into specialized tables, linked by Foreign Keys, so every piece of data is stored exactly once.
Syntax
-- THE PROBLEM (Unnormalized Data):
-- Table: Orders
-- | order_id | customer_name | customer_email | product_name | product_price |
-- |----------|---------------|------------------|--------------|---------------|
-- | 1 | Alice | alice@mail.com | Laptop | $1000 |
-- | 2 | Alice | alice@mail.com | Mouse | $20 |
-- BAD! Alice's email is duplicated. The Laptop's price is duplicated.
-- THE SOLUTION (3rd Normal Form):
-- Table 1: Customers (Stores Alice ONCE)
-- Table 2: Products (Stores Laptop ONCE)
-- Table 3: Orders (Just maps IDs together!)
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id),
product_id INT REFERENCES products(id),
created_at TIMESTAMP
);Common Pitfalls
- Over-normalizing. The academic standard goes up to 6NF (Sixth Normal Form), but industry standard stops at 3NF (Third Normal Form). If you normalize every single detail into its own isolated table, a simple query to fetch a user profile might require joining 15 tables together, making the database agonizingly slow.
- Violating 1NF by storing arrays or JSON blobs when you should use rows. If a user has 3 phone numbers, do not store
phone: '123, 456, 789'in one column. You cannot effectively index or query comma-separated strings. Create a separateuser_phonestable.
Interview Questions
1NF: Ensure all columns are atomic (no arrays/comma-separated values). 2NF: Remove partial dependencies (every column must rely on the ENTIRE primary key, not just part of a composite key). 3NF: Remove transitive dependencies (non-key columns must not rely on other non-key columns; e.g., don't store 'State' and 'Country' if you already store 'Zip Code').
Real-World Example
How modern Postgres allows you to intentionally violate 1NF using native JSONB columns when strict relational logic isn't needed.
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
-- Instead of creating a massive EAV (Entity-Attribute-Value) relational nightmare,
-- modern DBs allow querying directly into JSON objects!
metadata JSONB
);
-- You can literally query inside the JSON natively!
SELECT * FROM products WHERE metadata->>'color' = 'red';Check Your Knowledge
Test your understanding of Normalization (1NF–3NF) with these quick questions.