What is Normalization?
Learn what normalization is in SQL database design, the difference between 1NF, 2NF, and 3NF, and when denormalization makes sense instead of it.
Expected Interview Answer
Normalization is the process of organizing a database's tables and columns to reduce data redundancy and prevent update, insert, and delete anomalies, typically by splitting data into related tables linked by foreign keys and applying a series of rules called normal forms.
Each normal form builds on the previous one: First Normal Form (1NF) requires atomic column values with no repeating groups, Second Normal Form (2NF) removes partial dependencies on part of a composite key, and Third Normal Form (3NF) removes transitive dependencies where a non-key column depends on another non-key column rather than the primary key. Higher forms like BCNF and beyond handle edge cases involving overlapping candidate keys, but most production schemas stop at 3NF as a practical balance. Normalization trades some query complexity (more JOINs) for data integrity, since each fact is stored in exactly one place and updating it never risks leaving stale duplicate copies elsewhere. In read-heavy analytical systems, teams sometimes deliberately denormalize afterward to trade some redundancy back for fewer joins and faster reads.
- Eliminates redundant data, saving storage and preventing inconsistency
- Prevents update anomalies where duplicated data goes out of sync
- Makes each table's purpose and dependencies clear and enforceable
- Provides a structured way to reason about schema design
AI Mentor Explanation
Normalization is like keeping one master scoreboard for player details instead of rewriting a player's full bio on every single match sheet. If a player's team changes, you update it once on the master sheet rather than hunting through every match record to fix stale copies.
Splitting a flat table into normalized customer and order tables
Unnormalized orders
- order_id
- customer_name (repeated)
- customer_email (repeated)
- amount
customers (normalized)
- id (PK)
- name
orders (normalized)
- id (PK)
- customer_id (FK)
- amount
Step-by-Step Explanation
Step 1
Start with a flat table
Identify a table where columns repeat or one fact is duplicated across many rows.
Step 2
Apply 1NF
Ensure every column holds a single atomic value, removing repeating groups or comma-separated lists.
Step 3
Apply 2NF
For composite keys, remove columns that depend on only part of the key by moving them to their own table.
Step 4
Apply 3NF
Remove columns that depend on another non-key column rather than the primary key, moving them out too.
Step 5
Link with foreign keys
Connect the split tables back together using primary key to foreign key relationships.
Step 6
Decide on denormalization
For read-heavy reporting needs, selectively reintroduce redundancy where the performance gain outweighs the integrity risk.
What Interviewer Expects
- Defines normalization as reducing redundancy and preventing anomalies
- Can explain 1NF, 2NF, and 3NF in plain terms
- Gives an example of splitting a flat table into related tables
- Understands the tradeoff between normalization and query complexity
- Knows when denormalization might be a deliberate choice
Common Mistakes
- Confusing normalization with simply 'having more tables'
- Not knowing the difference between 1NF, 2NF, and 3NF
- Assuming higher normal forms are always better regardless of use case
- Forgetting that normalization increases the number of JOINs needed to read data
Best Answer (HR Friendly)
“Normalization means organizing a database so each piece of information is stored in exactly one place instead of being copied all over. That way, if something changes, like a customer's address, you only need to update it once, and it stays accurate everywhere.”
Code Example
-- BEFORE (unnormalized): customer info repeated on every order
-- orders_flat: order_id, customer_name, customer_email, amount
-- (1, 'Amit', '[email protected]', 250)
-- (2, 'Amit', '[email protected]', 90) <- name/email duplicated
-- AFTER (normalized into two tables)
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id),
amount DECIMAL(10,2)
);
-- One customer row now serves both orders, updated in one placeFollow-up Questions
- What is the difference between 2NF and 3NF?
- What is a transitive dependency?
- When would you deliberately denormalize a schema?
- What is BCNF and how does it differ from 3NF?
- What update anomalies can occur in an unnormalized table?
MCQ Practice
1. What is the primary goal of normalization?
Normalization organizes data to eliminate redundancy and avoid update, insert, and delete anomalies.
2. Which normal form requires atomic column values with no repeating groups?
First Normal Form (1NF) requires that every column hold a single, atomic value with no repeating groups.
3. What does Third Normal Form (3NF) specifically remove?
3NF eliminates transitive dependencies, where a non-key column depends on another non-key column instead of directly on the primary key.
Flash Cards
What is normalization? — Organizing tables and columns to reduce redundancy and prevent data anomalies, usually by splitting data into related tables.
What does 1NF require? — Atomic column values with no repeating groups or arrays in a single column.
What does 3NF remove? — Transitive dependencies, where a non-key column depends on another non-key column rather than the primary key.
What is the main tradeoff of normalization? — Better data integrity and less redundancy, at the cost of needing more JOINs to reassemble the data.