Definition
- 1NF – atomic values, no repeating groups; a comma-separated list of phone numbers violates 1NF.
- 2NF – removes partial dependencies on part of a composite key; every non-key column depends on the whole key.
- 3NF – removes transitive dependencies; non-key columns depend only on the key, not on other non-key columns.
- BCNF – every determinant is a candidate key; closes an edge case 3NF allows with overlapping candidate keys.
- Denormalization – deliberately adding redundancy (duplicated columns, precomputed aggregates) to speed up reads, at the cost of extra write work and keeping duplicates consistent.
- Guiding rule – normalize for writes, denormalize for reads.
Normalizing is like National Bank of India keeping each fact in exactly one filing cabinet: customers in one, branches in another, accounts in a third. Denormalizing is like photocopying the branch name onto every account statement so the teller does not have to walk to the branch cabinet each time: faster to read, but now every copy must be updated when the branch is renamed.
Understanding the Concepts
What is First Normal Form (1NF)?
1NF requires that each column holds a single atomic value and there are no repeating groups. A column storing multiple values violates 1NF.
-- VIOLATION: comma-separated phone numbers
CREATE TABLE customers_bad (
customer_id BIGINT PRIMARY KEY,
full_name VARCHAR(100),
phones VARCHAR(200) -- '9811111111, 9822222222, 9833333333'
);
-- FIX: separate table with one number per row
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL
);
CREATE TABLE customer_phones (
customer_id BIGINT REFERENCES customers(customer_id),
phone_number VARCHAR(15) NOT NULL,
PRIMARY KEY (customer_id, phone_number)
);
What is Second Normal Form (2NF)?
2NF applies to tables with composite keys. It removes partial dependencies where a non-key column depends on only part of the composite key.
-- VIOLATION: branch_name depends on account_id alone, not on (account_id, product_code)
CREATE TABLE account_products_bad (
account_id VARCHAR(10),
product_code VARCHAR(10),
branch_name VARCHAR(50), -- partial dependency!
PRIMARY KEY (account_id, product_code)
);
-- FIX: move branch_name to its own table
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
branch_name VARCHAR(50) NOT NULL
);
What is Third Normal Form (3NF)?
3NF removes transitive dependencies where a non-key column depends on another non-key column (key → A → B).
-- VIOLATION: branch_city depends on branch_code, not on account_id
CREATE TABLE accounts_bad (
account_id VARCHAR(10) PRIMARY KEY,
branch_code VARCHAR(10),
branch_city VARCHAR(50) -- transitive: account_id → branch_code → branch_city
);
-- FIX: separate branch table
CREATE TABLE branches (
branch_code VARCHAR(10) PRIMARY KEY,
branch_city VARCHAR(50) NOT NULL
);
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
branch_code VARCHAR(10) REFERENCES branches(branch_code)
);
What is BCNF?
Boyce-Codd Normal Form requires that every determinant is a candidate key. It closes an edge case where 3NF allows a non-candidate-key determinant when candidate keys overlap.
3NF: Non-prime → Non-prime is forbidden (transitive dep)
But prime → non-prime via a non-candidate-key is allowed in some edge cases
BCNF: Every determinant must be a candidate key. No exceptions.
In practice, most 3NF schemas are already in BCNF.
What anomalies does normalization prevent?
| Anomaly | Example | Cause | |---------|---------|-------| | Update | Rename a branch → update 1000 account rows | Fact stored in many places | | Insert | Cannot record a new branch with no accounts | Unrelated data missing | | Delete | Deleting last account at a branch loses the branch info | Fact tied to unrelated row |
What is denormalization?
Denormalization is the deliberate reversal: adding controlled redundancy to speed up reads at the cost of extra write work and consistency risk.
-- Denormalized: pre-joined read model for fast dashboard queries
CREATE TABLE account_dashboard (
account_id VARCHAR(10) PRIMARY KEY,
holder_name VARCHAR(100),
branch_name VARCHAR(50), -- duplicated from branches table
branch_city VARCHAR(50), -- duplicated from branches table
txn_count INT DEFAULT 0, -- precomputed counter
balance DECIMAL(15,2)
);
-- One SELECT returns everything the dashboard needs: no JOINs.
The trade-off: a branch rename now requires updating both the branches table and the dashboard table. A new transaction must increment txn_count in addition to inserting the transaction row.
When do you normalize vs denormalize?
| Concern | Normalize | Denormalize |
|---------|-----------|-------------|
| Writes | Cheap and safe | More writes, risk of inconsistency |
| Reads | JOINs needed, slower | Pre-joined, fast |
| Correctness | Each fact in one place | Duplicates must stay in sync |
| Use case | OLTP (transactions) | OLAP, dashboards, caching |
How do you manage denormalized data?
- Keep the authoritative write model normalized. Derive denormalized read models from it using:
- Same-transaction updates for strongly consistent counters.
- Materialized views or background refresh for eventually consistent reads.
- Event sourcing / CDC to propagate changes from OLTP to read models.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.*;
public class TransferService {
private final Connection connection;
public TransferService(Connection connection) {
this.connection = connection;
}
public void transfer(String from, String to, BigDecimal amount) throws SQLException {
connection.setAutoCommit(false);
try {
// Normalized write
executeUpdate("UPDATE accounts SET balance = balance - ? WHERE account_id = ?", amount, from);
executeUpdate("UPDATE accounts SET balance = balance + ? WHERE account_id = ?", amount, to);
executeUpdate("INSERT INTO transactions (from_account, to_account, amount) VALUES (?, ?, ?)",
from, to, amount);
// Denormalized counter update (same transaction for strong consistency)
executeUpdate("UPDATE account_dashboard SET txn_count = txn_count + 1, balance = balance - ? WHERE account_id = ?",
amount, from);
executeUpdate("UPDATE account_dashboard SET txn_count = txn_count + 1, balance = balance + ? WHERE account_id = ?",
amount, to);
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
connection.setAutoCommit(true);
}
}
private void executeUpdate(String sql, Object... params) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
if (params[i] instanceof BigDecimal) ps.setBigDecimal(i + 1, (BigDecimal) params[i]);
else ps.setString(i + 1, (String) params[i]);
}
ps.executeUpdate();
}
}
}
What are common mistakes?
The most common mistake is denormalizing before measuring. If the normalized schema with proper indexes serves reads fast enough, denormalization adds complexity for no benefit.
Wrong: "JOINs are slow, let us denormalize everything."
Right: "This specific dashboard query is slow after indexing. Let us denormalize that read path."
What are common interview follow-up questions?
- "Is normalization always better?" No. For OLTP writes yes. For read-heavy analytics, denormalization is often necessary.
- "What is a star schema?" A denormalized schema with a central fact table surrounded by dimension tables, used in data warehousing.
- "How do you keep denormalized data consistent?" Same-transaction updates for critical data, materialized views or CDC for non-critical data.
Visual Overview
Normal Forms Ladder: ┌──────────────────────────────────────────────────────────┐ │ 1NF: Atomic values, no repeating groups │ │ phones: '9811,9822' → separate phone rows │ ├──────────────────────────────────────────────────────────┤ │ 2NF: No partial dependencies (composite key only) │ │ branch_name depends on account_id alone → move out │ ├──────────────────────────────────────────────────────────┤ │ 3NF: No transitive dependencies │ │ account → branch_code → city → move city to branches │ ├──────────────────────────────────────────────────────────┤ │ BCNF: Every determinant is a candidate key │ └──────────────────────────────────────────────────────────┘ Normalization vs Denormalization: Normalized (write-optimized): Denormalized (read-optimized): ┌──────────┐ ┌──────────┐ ┌────────────────────────────┐ │ accounts │ │ branches │ │ account_dashboard │ │ acct_id │──│ br_code │ │ acct_id, name, branch_name │ │ br_code │ │ city │ │ city, txn_count, balance │ └──────────┘ └──────────┘ └────────────────────────────┘ JOINs needed for reads No JOINs, fast reads Each fact in one place Duplicates managed on write
Key Takeaway
The normal forms are a progressive ladder that removes redundancy and the anomalies it causes. 1NF requires atomic values with no repeating groups. 2NF applies to composite keys and removes partial dependencies. 3NF removes transitive dependencies, forbidding the chain key → A → B. BCNF tightens 3NF by requiring every determinant to be a candidate key. Each form assumes the previous, and the reward is eliminating update, insert, and delete anomalies – exactly what you want in a transactional OLTP schema where consistent, cheap writes matter most.
Denormalization is the deliberate reverse: you reintroduce controlled redundancy – duplicated columns, precomputed aggregates, or flattened dimensions – to make reads faster by avoiding JOINs, accepting that writes now touch more places and duplicates must be kept in sync. At National Bank of India, the normalised customer, account, and branch tables were correct, but the branch-manager dashboard was slow because it joined three tables on every refresh. Pankaj built a denormalized dashboard table that flattened each account with its customer name, branch name, and city, and the dashboard became fast because it did no JOINs at read time. The price was that a branch rename had to refresh the duplicated name, handled by a same-transaction update for balances and a scheduled refresh for the dashboard.
The guiding rule is: normalize for write-side integrity, denormalize for read-side speed. Keep the authoritative write model normalized for correctness, and derive denormalized read models from it, paying in extra writes so that reads are cheap. Every duplicate must be deliberately managed – via same-transaction updates, materialized views, or change data capture – never left to drift accidentally.
Remember one sentence: normalization puts each fact in one place for safe writes, denormalization duplicates facts for fast reads, and a mature system does both deliberately.
---