Live to Plant

Practical Examples of Normalization in SQL Databases

Scroll down to article
Updated: July 19, 2025

Normalization is a fundamental concept in relational database design that helps organize data to reduce redundancy and improve data integrity. By structuring a database according to normalization principles, developers can ensure efficient storage, easier maintenance, and better consistency of data. This article explores practical examples of normalization in SQL databases, illustrating how normalization works in real-world scenarios.

What is Normalization?

Normalization is the process of organizing data within a database to minimize duplication and dependency. It involves decomposing large tables into smaller, more manageable pieces without losing data integrity. The goal is to separate data logically while maintaining relationships through foreign keys.

Normalization typically involves several normal forms (NFs), each representing a set of rules:
– First Normal Form (1NF): Eliminate repeating groups; ensure atomicity.
– Second Normal Form (2NF): Remove partial dependencies on a composite primary key.
– Third Normal Form (3NF): Remove transitive dependencies.
– Boyce-Codd Normal Form (BCNF): A stronger version of 3NF.
– Higher normal forms exist but are less common in typical business applications.

Let’s dive into practical examples to understand these concepts better.

Example Scenario: An Online Bookstore

Consider an online bookstore that needs to store information about books, authors, publishers, and customer orders. Initially, the data might be stored in a single table like this:

OrderID CustomerName BookTitle AuthorName Publisher OrderDate Quantity PricePerUnit
1001 Alice Smith Introduction to SQL John Doe TechBooks Inc. 2024-05-01 2 20
1002 Bob Johnson Advanced Python Jane Roe CodePress 2024-05-03 1 35
1001 Alice Smith Advanced Python Jane Roe CodePress 2024-05-01 1 35

This table is unnormalized and contains redundancies such as repeating customer names and book details across multiple rows.

First Normal Form (1NF)

Problem: Repeating Groups

The above table violates 1NF because it contains multiple books per order, leading to repeating groups and redundant data entries for customers and books.

Solution: Atomic Values

To satisfy 1NF, each column should contain atomic values , no multi-valued attributes or arrays.

Applying 1NF

We separate orders into two tables: one for orders and one for order items.

Orders Table

OrderID CustomerName OrderDate
1001 Alice Smith 2024-05-01
1002 Bob Johnson 2024-05-03

OrderItems Table

OrderItemID OrderID BookTitle Quantity PricePerUnit
1 1001 Introduction to SQL 2 20
2 1001 Advanced Python 1 35
3 1002 Advanced Python 1 35

Now, each cell contains atomic values, and the repeated group of books per order is split into rows in OrderItems.

Second Normal Form (2NF)

Problem: Partial Dependencies

Although the tables comply with 1NF, the OrderItems table still violates the second normal form if it has a composite primary key such as (OrderID, BookTitle). The PricePerUnit depends only on the book and not on the order.

Solution: Remove Partial Dependencies

To reach 2NF, remove columns that depend only on part of the composite primary key and place them in separate tables.

Applying 2NF

Create separate tables for Books and keep the prices there:

Books Table

BookID BookTitle AuthorID PublisherID PricePerUnit
B001 Introduction to SQL A001 P001 20
B002 Advanced Python A002 P002 35

Authors Table

AuthorID AuthorName
A001 John Doe
A002 Jane Roe

Publishers Table

PublisherID PublisherName
P001 TechBooks Inc.
P002 CodePress

OrderItems Table

OrderItemID OrderID BookID Quantity
OI001 1001 B001 2
OI002 1001 B002 1
OI003 1002 B002 1

Orders Table remains unchanged:

This design removes partial dependencies because PricePerUnit now depends on BookID alone, not on (OrderID, BookID).

Third Normal Form (3NF)

Problem: Transitive Dependencies

In the Books table above, suppose we added more publisher-related columns such as PublisherAddress. This creates transitive dependency because PublisherAddress depends on PublisherName, which depends on Book.

Solution: Remove Transitive Dependencies

To satisfy third normal form, non-key attributes must depend only on candidate keys , no transitive dependencies allowed.

Applying 3NF

Keep publisher details in their own table:

Publishers Table

PublisherID PublisherName PublisherAddress
P001 TechBooks Inc. 123 Tech Ave, Cityville
P002 CodePress 456 Code St, Devtown

This way:

  • The Books table has no attributes dependent on PublisherAddress.
  • All publisher-related information is stored only once in the Publishers table.

Fourth Normal Form (4NF) – Brief Overview

While most practical applications stop at third normal form or BCNF, sometimes multivalued dependencies need handling.

For instance, if an author can have multiple phone numbers and email addresses independent of each other, storing both in a single table would violate fourth normal form.

Separate author contacts into two tables:

  • AuthorPhoneNumbers
  • AuthorEmails

Each relates via AuthorID without creating redundancies or anomalies.

Practical Benefits of Normalization

Data Integrity

Normalized databases reduce inconsistencies. For example, updating an author’s name requires just one update operation rather than several scattered across rows.

Efficient Queries

Properly normalized tables make joins straightforward and avoid retrieving redundant data.

Easier Maintenance

Modifying schema or business logic becomes simpler due to well-organized data structures.

Sample SQL Code Illustrating Normalization Steps

Here is some SQL code illustrating these concepts based on our bookstore example:

,  Create Authors Table
CREATE TABLE Authors (
    AuthorID VARCHAR(10) PRIMARY KEY,
    AuthorName VARCHAR(100) NOT NULL
);

,  Create Publishers Table
CREATE TABLE Publishers (
    PublisherID VARCHAR(10) PRIMARY KEY,
    PublisherName VARCHAR(100) NOT NULL,
    PublisherAddress VARCHAR(255)
);

,  Create Books Table
CREATE TABLE Books (
    BookID VARCHAR(10) PRIMARY KEY,
    BookTitle VARCHAR(200) NOT NULL,
    AuthorID VARCHAR(10),
    PublisherID VARCHAR(10),
    PricePerUnit DECIMAL(8,2),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
    FOREIGN KEY (PublisherID) REFERENCES Publishers(PublisherID)
);

,  Create Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE
);

,  Create OrderItems Table
CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    BookID VARCHAR(10),
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

Data insertion becomes modular:

INSERT INTO Authors VALUES ('A001', 'John Doe');
INSERT INTO Publishers VALUES ('P001', 'TechBooks Inc.', '123 Tech Ave');
INSERT INTO Books VALUES ('B001', 'Introduction to SQL', 'A001', 'P001', 20);
INSERT INTO Orders VALUES (1001, 'Alice Smith', '2024-05-01');
INSERT INTO OrderItems VALUES (1,1001,'B001',2);

When Not to Over-Normalize?

While normalization brings many advantages, sometimes over-normalization can lead to complex queries with many joins that degrade performance. Denormalization strategically adds some redundancy back for faster reads in certain scenarios such as reporting or analytical processing.

For example:
– Adding summary columns or caching computed values.
– Storing denormalized product information for faster retrieval.

The key is understanding when normalization benefits outweigh its costs depending on your use case.

Conclusion

Normalization plays a crucial role in designing robust SQL databases by eliminating redundancy and ensuring data consistency. Through practical examples from an online bookstore scenario, we illustrated how to apply first, second, and third normal forms effectively:

  • 1NF: Ensures atomicity by removing repeating groups.
  • 2NF: Removes partial dependency by isolating fields related only to part of composite keys.
  • 3NF: Eliminates transitive dependency by separating fields with indirect relationships into their own tables.

Well-normalized databases improve maintainability, optimize storage space, and enhance data integrity. However, pragmatic decisions around performance may sometimes favor denormalization for specific workloads. Understanding normalization principles equips database designers and developers with tools necessary for balanced schema design suitable for any application domain.

By applying these principles thoughtfully in your next project, you can build scalable, cleanly structured relational databases that stand the test of time.

Related Posts:

Normalization