What Is Database Normalization?

Database normalization is a design technique used in relational databases to organize data in a cleaner, more consistent, and manageable structure.

Its main purpose is to prevent unnecessary duplication of the same information and to create proper relationships between tables.

When starting a database project, storing all information in a single table may initially seem like the easiest solution. However, as the amount of data grows, this approach can cause serious problems.

Consider a sales system where all information is stored in a single table:

This table may work in a small system. However, problems begin to appear as the number of records increases.

Every time John Smith places an order, his name and phone number are stored again. Every time a keyboard is sold, the product name, category, and price are repeated.

Normalization reduces these repetitions by separating the data into logical tables.

For example, the system could contain the following tables:

• Customers
• Products
• Categories
• Orders
• Order Details

Each piece of information is then stored in the appropriate table only when necessary.

Why Is Database Normalization Important?

The most obvious purpose of normalization is to reduce duplicate data. However, its benefits go far beyond storage efficiency.

It Reduces Data Duplication

Instead of storing the same customer information in hundreds of order records, customer data is stored only in the customer table.

For example:

Customers
------------------------
Id
FullName
Phone
Email

The Orders table stores only the customer’s identifier:

Orders
------------------------
Id
CustomerId
OrderDate

Even when a customer places 100 orders, their name and phone number do not need to be stored 100 times.

It Prevents Update Anomalies

In a non-normalized table, the same information may exist in many different rows.

Suppose John Smith’s phone number appears in 50 order records. When the phone number changes, all 50 rows must be updated.

If only 49 rows are updated, the database becomes inconsistent.

One record may contain:

555-111-2233

while another contains:

555-999-8877

It is no longer clear which value is correct.

In a normalized structure, the phone number exists only in the Customers table:

UPDATE Customers
SET Phone = '555-999-8877'
WHERE Id = 15;

Only one record needs to be updated.

It Prevents Delete Anomalies

In a non-normalized database, deleting one record may accidentally remove other valuable information.

For example, suppose product information exists only in the order table.

If the last order containing a particular product is deleted, all information about that product may also disappear.

In a normalized database, products are stored separately:

Products
------------------------
Id
ProductName
CategoryId
Price

Deleting an order does not delete the product itself.

It Prevents Insert Anomalies

A non-normalized database may make it impossible to add certain information unless unrelated information also exists.

For example, when products are stored only in the Orders table, how can a new product be added before it has ever been ordered?

With a separate Products table, a new product can be inserted at any time:

INSERT INTO Products
(
    ProductName,
    CategoryId,
    Price
)
VALUES
(
    'Mechanical Keyboard',
    1,
    1750
);

What Are Normal Forms?

Normalization is applied through a set of rules known as normal forms.

The most commonly used normal forms are:

• First Normal Form — 1NF
• Second Normal Form — 2NF
• Third Normal Form — 3NF

More advanced forms such as BCNF, 4NF, and 5NF also exist. However, a properly designed Third Normal Form structure is sufficient for many business applications.

What Is 1NF — First Normal Form?

A table satisfies First Normal Form when each column contains atomic values.

In other words, a single cell should not contain multiple values.

Consider the following incorrect example:

The PhoneNumbers column contains multiple phone numbers.

This design makes queries more difficult.

For example, finding a customer by phone number may require:

SELECT *
FROM Customers
WHERE PhoneNumbers LIKE '%555-444-5566%';

This is not a good relational database design.

A better solution is to store phone numbers in a separate table:

Customers
------------------------
Id
FullName

CustomerPhones
------------------------
Id
CustomerId
Phone

Example data:

Now each customer can have any number of phone numbers.

The tables can be created in SQL Server as follows:

CREATE TABLE Customers
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL
);

CREATE TABLE CustomerPhones
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    Phone VARCHAR(20) NOT NULL,

    CONSTRAINT FK_CustomerPhones_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(Id)
);

This structure is much closer to First Normal Form.

What Is 2NF — Second Normal Form?

A table must first satisfy 1NF before it can satisfy Second Normal Form.

In addition, when a table has a composite primary key, every non-key column must depend on the entire key.

Consider the following order details table:

The key may consist of:

OrderId + ProductId

However, ProductName depends only on ProductId.

The order number is not required to determine the product name.

Therefore, product information should be moved into a separate table:

Products
------------------------
Id
ProductName
Price

The Order Details table can then contain:

OrderDetails
------------------------
OrderId
ProductId
Quantity
UnitPrice

The UnitPrice field is worth discussing.

At first, it may seem that price should exist only in the Products table. However, product prices can change over time.

A product that costs 750 today may cost 900 next month.

To calculate an old order correctly, the price applied at the time of sale can be stored in Order Details.

This is not a normalization mistake because the value represents the historical sale price, not the current product price.

What Is 3NF — Third Normal Form?

A table must satisfy both 1NF and 2NF before it can satisfy Third Normal Form.

In addition, a non-key column should not depend on another non-key column.

Consider the following Products table:

In this example:

• ProductId is the primary key.
• CategoryId identifies the category.
• CategoryName depends on CategoryId, not directly on the product.

The dependency is:

ProductId → CategoryId → CategoryName

This is known as a transitive dependency.

A better structure is:

Categories
------------------------
Id
CategoryName

Products
------------------------
Id
ProductName
CategoryId
Price

The SQL definition could look like this:

CREATE TABLE Categories
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CategoryName VARCHAR(100) NOT NULL
);

CREATE TABLE Products
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    ProductName VARCHAR(150) NOT NULL,
    CategoryId INT NOT NULL,
    Price DECIMAL(18,2) NOT NULL,

    CONSTRAINT FK_Products_Categories
        FOREIGN KEY (CategoryId)
        REFERENCES Categories(Id)
);

The category name is now stored only once.

When the category name changes, only one record needs to be updated:

UPDATE Categories
SET CategoryName = 'Computers and Accessories'
WHERE Id = 10;

All products connected to this category automatically use the updated value.

Example Before and After Normalization

A non-normalized order table might look like this:

Orders
---------------------------------------------------------
OrderId
OrderDate
CustomerName
CustomerPhone
CustomerAddress
ProductName
CategoryName
Quantity
Price

This design creates a large amount of duplication.

The same customer is repeated in every order.

The same product is repeated every time it is sold.

The same category may be stored in hundreds of product records.

After normalization, the system may be divided into:

Customers
------------------------
Id
FullName
Phone
Address

Categories
------------------------
Id
CategoryName

Products
------------------------
Id
ProductName
CategoryId
Price

Orders
------------------------
Id
CustomerId
OrderDate

OrderDetails
------------------------
Id
OrderId
ProductId
Quantity
UnitPrice

These tables can be connected using Primary Key and Foreign Key relationships.

For example:

SELECT
    o.Id AS OrderNumber,
    o.OrderDate,
    c.FullName AS Customer,
    p.ProductName,
   cat.CategoryName,
    od.Quantity,
    od.UnitPrice,
    od.Quantity * od.UnitPrice AS Total
FROM Orders o
INNER JOIN Customers c
    ON c.Id = o.CustomerId
INNER JOIN OrderDetails od
    ON od.OrderId = o.Id
INNER JOIN Products p
    ON p.Id = od.ProductId
INNER JOIN Categories cat
    ON cat.Id = p.CategoryId;

This query combines related data from multiple tables to produce a complete order list.

What Are the Advantages of Normalization?

A properly normalized database provides several important advantages.

Less Duplicate Data

The same information is not unnecessarily stored in multiple records.

This can provide significant storage benefits in systems containing millions of records.

More Consistent Data

Because information is stored in one place, the risk of contradictory values is reduced.

Easier Updates

Customer, category, and product information can be updated from a single location.

Better Data Integrity

Database rules such as Primary Keys, Foreign Keys, and Unique Constraints can be used more effectively.

For example:

ALTER TABLE Products
ADD CONSTRAINT FK_Products_Categories
FOREIGN KEY (CategoryId)
REFERENCES Categories(Id);

This constraint prevents a product from being assigned to a category that does not exist.

Easier Maintenance

Because each table has a clearer responsibility, the application becomes easier to develop and maintain.

Are There Any Disadvantages to Normalization?

Normalization is usually essential for good database design. However, creating as many tables as possible is not always the correct approach.

Over-normalization can result in a large number of tables and JOIN operations.

For example, when a simple report requires 15 different tables, queries may become difficult to understand and maintain.

SELECT ...
FROM Table1
INNER JOIN Table2 ON ...
INNER JOIN Table3 ON ...
INNER JOIN Table4 ON ...
INNER JOIN Table5 ON ...

Database design should therefore consider both theoretical rules and the actual needs of the application.

What Is Denormalization?

Denormalization is the intentional duplication of certain data to meet specific performance or reporting requirements.

For example, in a very large e-commerce system, calculating the order total every time a report is opened may be expensive.

Theoretically, the total can be calculated as follows:

SELECT
    OrderId,
    SUM(Quantity * UnitPrice) AS OrderTotal
FROM OrderDetails
GROUP BY OrderId;

However, in a system with millions of orders, storing an OrderTotal field directly in the Orders table may be preferred for performance reasons.

The important point is that duplication should be the result of a conscious design decision rather than an accidental database structure.

A good approach is to start with a properly normalized design and consider denormalization only after performance measurements show that it is necessary.

Does Every Database Have to Be in 3NF?

In theory, normalization is an excellent starting point for database design. In real projects, however, the purpose of the application also matters.

For example:

• Normalization is generally important in OLTP systems where daily transactions are processed.
• Reporting systems may use less normalized structures.
• Data warehouses may use different approaches such as star schemas.
• Very large systems may intentionally duplicate some data for performance.

Therefore, normalization should not simply be understood as creating more tables.

The real goal is to store data in a logical and manageable structure.

Conclusion

Normalization is one of the most important concepts in relational database design.

A well-normalized database:

• Reduces duplicate data.
• Prevents update anomalies.
• Improves data integrity.
• Makes relationships between tables clearer.
• Makes the database easier to maintain.

For many applications, understanding and correctly applying 1NF, 2NF, and 3NF provides a strong foundation for good database design.

However, the purpose of normalization is not to create the maximum possible number of tables. The purpose is to make sure that every piece of data is stored in the correct place and that the database remains consistent, manageable, and reliable over time.

In a well-designed database, each table has a clear responsibility, each piece of information is stored in as few places as possible, and relationships between tables are explicitly defined.

In summary, normalization is not merely a theoretical database topic. It is a practical design approach that directly affects data quality, maintainability, and the ability of an application to grow in the future.