The three normal forms are the most fundamental design principles in database modeling. So, what exactly are the three normal forms? And in real-world development, must we strictly adhere to them? In this article, let’s discuss this topic in depth.
The Three Normal Forms
1. First Normal Form (1NF: Ensuring Atomicity in Each Column)
The First Normal Form requires that each field (column) in every table must be atomic, meaning the values in each field cannot be further divided. In other words, each field can only store a single value — it cannot contain sets, arrays, or repeating groups.
For example, consider the following student table:
| Student ID | Name | Phone Number |
|---|---|---|
| 1 | Alice | 123456789, 987654321 |
| 2 | Bob | 555555555 |
In this table, the Phone Number field contains multiple numbers, which violates 1NF's requirement for atomicity. To comply with 1NF, phone numbers should be separated into individual records or moved to a new table.
Design after conforming to 1NF:
Student Table
| Student ID | Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Phone Table
| Phone ID | Student ID | Phone Number |
|---|---|---|
| 1 | 1 | 123456789 |
| 2 | 1 | 987654321 |
| 3 | 2 | 555555555 |
2. Second Normal Form (2NF: Every Column Must Depend on the Entire Primary Key)
The Second Normal Form requires that the table already satisfy 1NF and eliminates partial dependencies — non-primary-key fields must depend on the entire primary key, not just part of it. This typically applies to tables with composite primary keys.
For example, consider the following OrderDetail table:
| Order ID | Product ID | Product Name | Quantity | Unit Price |
|---|---|---|---|---|
| 1001 | A01 | Apple | 10 | 2.5 |
| 1001 | A02 | Orange | 5 | 3.0 |
| 1002 | A01 | Apple | 7 | 2.5 |
In this table, the composite primary key is (Order ID, Product ID). The Product Name and Unit Price depend only on Product ID, not the entire primary key, resulting in partial dependency — which violates 2NF.
Design after conforming to 2NF:
OrderDetail Table
| Order ID | Product ID | Quantity |
|---|---|---|
| 1001 | A01 | 10 |
| 1001 | A02 | 5 |
| 1002 | A01 | 7 |
Product Table
| Product ID | Product Name | Unit Price |
|---|---|---|
| A01 | Apple | 2.5 |
| A02 | Orange | 3.0 |
3. Third Normal Form (3NF: Eliminate Transitive Dependencies)
The Third Normal Form requires that the table already satisfy 2NF and eliminates transitive dependencies — non-primary-key fields should not depend on other non-primary-key fields. In other words, every non-primary-key field must directly depend on the primary key, not indirectly through another non-primary-key field.
For example, consider the following Employee table:
| Employee ID | Employee Name | Department ID | Department Name |
|---|---|---|---|
| E01 | Alice | D01 | Sales |
| E02 | Bob | D02 | Engineering |
| E03 | Charlie | D01 | Sales |
In this table, Department Name depends on Department ID, which in turn depends on the primary key (Employee ID), forming a transitive dependency — violating 3NF.
Design after conforming to 3NF:
Employee Table
| Employee ID | Employee Name | Department ID |
|---|---|---|
| E01 | Alice | D01 |
| E02 | Bob | D02 |
| E03 | Charlie | D01 |
Department Table
| Department ID | Department Name |
|---|---|
| D01 | Sales |
| D02 | Engineering |
By moving the department information to a separate table, the transitive dependency is eliminated, and the database structure conforms to the Third Normal Form.
To summarize, the three normal forms are:
1NF: Ensure each field holds atomic values.
2NF: Eliminate partial dependencies — every non-key field must depend on the whole primary key.
3NF: Eliminate transitive dependencies — non-key fields should depend only on the primary key.
Violating the Three Normal Forms
In practice, while following the three normal forms (1NF, 2NF, 3NF) improves data consistency and reduces redundancy, there are cases where violating them can be beneficial — to improve performance, simplify design, or meet specific business requirements.
Below are common reasons and examples for intentionally violating normal forms:
Performance Optimization
In high-concurrency and large-scale applications, strictly following the normal forms may result in frequent join operations, which can increase query time and system load. To improve performance, designers may denormalize data to reduce joins.
For example, in an e-commerce system with Orders and Users tables, a strictly 3NF design would store only the User ID in the Orders table and require a join to retrieve user details.
To improve query performance, we might redundantly store user name and address in the Orders table to avoid joining the Users table.
Design after violating 3NF:
| Order ID | User ID | User Name | User Address | Order Date | Total Amount |
|---|---|---|---|---|---|
| 1001 | U01 | Alice | New York | 2025-01-01 | $500 |
| 1002 | U02 | Bob | Los Angeles | 2025-01-02 | $300 |
Simplifying Queries and Development
Strict normalization may result in complex database schemas, making development and maintenance more difficult. To simplify logic and reduce development effort, appropriate redundancy may be introduced.
For instance, in a content management system (CMS), the Articles and Categories tables are usually separate. If category names are frequently queried with articles, a join adds complexity. Storing the category name directly in the Articles table simplifies frontend logic.
Design after violating 3NF:
| Article ID | Title | Content | Category ID | Category Name |
|---|---|---|---|---|
| A01 | Article 1 | ... | C01 | Technology |
| A02 | Article 2 | ... | C02 | Lifestyle |
Reporting and Data Warehousing
In data warehouses and reporting systems, fast reads and aggregations are critical. To optimize performance, denormalized structures are often used, such as star or snowflake schemas — which don't comply with strict normal forms.
For example, a sales data warehouse might have a fact table that includes dimension data for fast report generation.
Design after violating 3NF:
| Sales ID | Product ID | Product Name | Category | Quantity Sold | Revenue | Sale Date |
|---|---|---|---|---|---|---|
| S01 | P01 | Phone | Electronics | 100 | $50000 | 2025/1/1 |
| S02 | P02 | Book | Education | 200 | $2000 | 2025/1/2 |
Here, storing product name and category directly avoids joins with dimension tables, boosting report generation efficiency.
Special Business Requirements
Some business scenarios require fast responses for specific queries or operations. Appropriate redundancy helps meet these demands.
For example, in a real-time trading system, to quickly calculate account balances, we might store the current balance in the Users table rather than calculate it on the fly from transaction records.
Design after violating 3NF:
| User ID | Username | Current Balance |
|---|---|---|
| U01 | Alice | $10000 |
| U02 | Bob | $5000 |
Although transaction details are stored elsewhere, maintaining a Current Balance in the user table avoids costly runtime computations.
Balancing Read and Write Performance
In systems where read operations significantly outnumber writes, redundancy is sometimes accepted to improve read performance — even if it increases write complexity.
For instance, social media platforms display a user's friend count on their profile. Calculating this every time is inefficient. Instead, the friend count is stored directly in the Users table.
Design after violating 3NF:
| User ID | Username | Friend Count |
|---|---|---|
| U01 | Alice | 150 |
| U02 | Bob | 200 |
This allows for fast display without real-time computation.
Fast Iteration and Flexibility
Startups or fast-evolving products often need flexible and quickly adjustable databases. Over-normalization may hinder speed and adaptability. Redundant design can improve development speed and agility.
For example, in an early-stage e-commerce platform, shipping addresses might be stored directly in the Orders table instead of using a separate Addresses table.
Design after violating 3NF:
| Order ID | User ID | Username | Shipping Address | Order Date | Total Amount |
|---|---|---|---|---|---|
| O1001 | U01 | Alice | New York | 2025/1/1 | $800 |
| O1002 | U02 | Bob | Los Angeles | 2025/1/2 | $1200 |
This simplifies development and supports quick product rollout. Normalization can be applied later as needed.
Reducing Complexity and Improving Comprehensibility
Excessive normalization may make a schema hard to understand and maintain. Moderate redundancy can simplify the design and improve team understanding and communication.
For instance, in a school management system, splitting class info across multiple tables can be confusing. To simplify, class name and homeroom teacher may be stored directly in the Students table.
Design after violating 3NF:
| Student ID | Name | Class ID | Class Name | Homeroom Teacher |
|---|---|---|---|---|
| S01 | Alice | C01 | Class A | Charlie |
| S02 | Bob | C02 | Class B | David |
By storing Class Name and Homeroom Teacher directly, table count is reduced and design is simplified.
Summary
In this article, we’ve analyzed the three normal forms of database design along with examples. They serve as foundational principles for designing relational databases. However, in real-world projects, due to performance needs, simplified design, rapid iteration, or specific business logic, we often do not strictly follow them.
Ultimately, system architecture is a trade-off — among business requirements, data consistency, performance, and development efficiency. We must make practical design decisions based on our application context.
We are Leapcell, your top choice for hosting backend projects.
Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:
Multi-Language Support
- Develop with Node.js, Python, Go, or Rust.
Deploy unlimited projects for free
- pay only for usage — no requests, no charges.
Unbeatable Cost Efficiency
- Pay-as-you-go with no idle charges.
- Example: $25 supports 6.94M requests at a 60ms average response time.
Streamlined Developer Experience
- Intuitive UI for effortless setup.
- Fully automated CI/CD pipelines and GitOps integration.
- Real-time metrics and logging for actionable insights.
Effortless Scalability and High Performance
- Auto-scaling to handle high concurrency with ease.
- Zero operational overhead — just focus on building.
Explore more in the Documentation!
Follow us on X: @LeapcellHQ