Disclaimer
All names, phone numbers, account numbers, branch names, and transaction details used in this article are purely fictional and created only for educational and demonstration purposes. Any resemblance to real persons, accounts, or data is purely coincidental.
When databases grow, poor design decisions can silently create serious problems—duplicate data, incorrect reports, and difficult maintenance. This is where database normalization becomes critical.
Normalization is not just a theoretical concept taught in textbooks. It is a practical database design approach used daily in real-world systems such as banking applications, ERP platforms, insurance systems, and enterprise CRMs.
In this article, we will understand normalization step-by-step using one realistic banking use case, and we will apply all important normal forms in a clear, interview-friendly manner.
What Is Database Normalization?
Database normalization is the process of organizing data into well-structured tables to:
- Eliminate data redundancy
- Prevent update, insert, and delete anomalies
- Maintain data consistency
- Improve long-term scalability and maintenance
In simple terms:
Normalization ensures that each piece of data is stored once, stored correctly, and stored in the right place.
Real-Time Use Case: Banking System
Consider a simple banking system where we need to manage:
- Customer information
- Bank accounts
- Branch details
- Financial transactions
Initial Design (Before Normalization)
A very common beginner-level design is to store all information in a single table.
BANK_DATA (Unnormalized Table)
| CustomerID | CustomerName | MobileNumber | AccountNo | AccountType | BranchCode | BranchCity | TransactionID | Amount |
|---|---|---|---|---|---|---|---|---|
| CUST001 | Customer Alpha | 9000000001 | ACC10001 | Savings | BR001 | Mumbai | TXN9001 | 5000 |
| CUST001 | Customer Alpha | 9000000001 | ACC10001 | Savings | BR001 | Mumbai | TXN9002 | 2000 |
| CUST002 | Customer Beta | 9000000002 | ACC10002 | Current | BR002 | Delhi | TXN9003 | 7000 |
Problems with This Design
- Customer data is repeated for every transaction
- Branch information is duplicated unnecessarily
- Updating a mobile number requires multiple updates
- High risk of inconsistent data
- Poor scalability for enterprise systems
This table clearly needs normalization.
First Normal Form (1NF)
Rule of 1NF
- Each column must contain atomic (single) values
- No repeating groups or multi-valued attributes
- Each record must be uniquely identifiable
Example of Violation
If a column stores multiple values such as:
9000000001, 9000000005
it violates 1NF.
Status
Our table already stores atomic values, so it satisfies First Normal Form, but redundancy still exists.
Second Normal Form (2NF)
Rule of 2NF
- Table must be in 1NF
- No partial dependency
- Every non-key attribute must depend on the entire primary key
Identifying the Issue
The effective composite key here is:
(CustomerID, AccountNo, TransactionID)
However:
- CustomerName and MobileNumber depend only on CustomerID
- Branch details depend only on AccountNo
This is a classic partial dependency problem.
Applying 2NF (Table Decomposition)
CUSTOMER Table
| CustomerID | CustomerName | MobileNumber |
| CUST001 | Customer Alpha | 9000000001 |
| CUST002 | Customer Beta | 9000000002 |
ACCOUNT Table
| AccountNo | AccountType | BranchCode |
| ACC10001 | Savings | BR001 |
| ACC10002 | Current | BR002 |
TRANSACTION Table
| TransactionID | AccountNo | Amount |
| TXN9001 | ACC10001 | 5000 |
| TXN9002 | ACC10001 | 2000 |
| TXN9003 | ACC10002 | 7000 |
Partial dependency is now removed.
Third Normal Form (3NF)
Rule of 3NF
- Table must be in 2NF
- No transitive dependency
- Non-key attributes must not depend on other non-key attributes
Identifying Transitive Dependency
In the ACCOUNT table:
AccountNo → BranchCode → BranchCity
BranchCity depends on BranchCode, not directly on AccountNo.
Applying 3NF
BRANCH Table
| BranchCode | BranchCity |
| BR001 | Mumbai |
| BR002 | Delhi |
Updated ACCOUNT Table
| AccountNo | AccountType | BranchCode |
| ACC10001 | Savings | BR001 |
| ACC10002 | Current | BR002 |
This ensures better data consistency and easier maintenance.
Boyce–Codd Normal Form (BCNF)
Rule of BCNF
Every determinant must be a candidate key.
Example Scenario
If business rules state:
AccountType → InterestRate
AccountType is not a key, so the table violates BCNF.
BCNF Solution
ACCOUNT_TYPE Table
| AccountType | InterestRate |
| Savings | 4.0 |
| Current | 0.0 |
Fourth Normal Form (4NF)
Rule of 4NF
- No multi-valued dependencies
Realistic Scenario
A customer can have:
- Multiple mobile numbers
- Multiple email addresses
Storing both in one table causes duplication.
Solution
CUSTOMER_PHONE
| CustomerID | MobileNumber |
CUSTOMER_EMAIL
| CustomerID | Email |
Fifth Normal Form (5NF)
Rule of 5NF
- No join dependency
- Tables cannot be further decomposed without losing information
This level is mainly relevant in large-scale enterprise systems and data warehouses.
For most transactional systems:
Third Normal Form or BCNF is more than sufficient.
Final Normalized Banking Schema
- CUSTOMER
- ACCOUNT
- ACCOUNT_TYPE
- BRANCH
- TRANSACTION
- CUSTOMER_PHONE
- CUSTOMER_EMAIL
Each table has a single responsibility, minimal redundancy, and strong data integrity.
Why Normalization Matters in Real Projects
- Prevents duplicate data
- Avoids update and deletion anomalies
- Improves reporting accuracy
- Enhances system scalability
- Essential for banking, ERP, and financial systems
Interview-Ready Summary
Normalization is the process of structuring database tables to reduce redundancy and maintain data integrity by applying normal forms such as 1NF, 2NF, 3NF, and BCNF.
Final Thoughts
Normalization is not about memorizing rules—it is about designing databases that can grow safely and reliably. A well-normalized schema saves countless hours of debugging, data correction, and performance tuning in real-world systems.
This article is intended for educational and interview preparation purposes only.