Database Normalization : The Simplest Explanation You’ll Find

So, what is database normalization, really?

People often make database normalization in SQL seem extremely complex, throwing around every technical term available in the dictionary. So, let me explain it using the most basic technical terms and the simplest examples possible.

What is Normalization?

Normalization is a method of organizing data in a database to achieve two primary goals: eliminating data redundancy (avoiding repeated data) and improving data integrity (preventing common errors).

When your database is properly normalized, it becomes well-structured , think of it like a perfectly organized Excel sheet with clear columns and rows. This structure makes it much easier to query data, from simple lookups to extremely complex queries involving multiple joins.

End of the day, we can say: Design your database following normalization so that later, you won’t regret it.

Let’s dive into Normal Forms with a practical example!

Learn it with a problem: The Unnormalized Table

Imagine a small business that records customer orders in a single, messy spreadsheet. This table violates pretty much every normalization rule:

Customer_Orders_Unnormalized

Here we face our first obvious problem:

Multi-valued fields in a single column: ProductDetails contains multiple products and quantities (e.g., “TV:2, Mobile:1”).
SupplierDetails also has multiple suppliers.

Redundancy: Information like CustomerName for OrderID 1 and 3 is repeated.

This structure makes it impossible to query individual products,
update quantities easily, or ensure data consistency.

1. First Normal Form (1NF):

Rule:

• Each column must contain atomic values (each cell in a database table should contain only one value).
• No repeating groups within rows.
• Each row must be unique, meaning there will be a primary key as a identity for the row.

How we fix it: We break down each repeating field into separate rows, ensuring each cell holds only one piece of information.

Customer_Orders_1NF

Primary Key (PK) for 1NF Table: (OrderID, Product) (This is a composite key because it takes both columns together to uniquely identify a row).

Now we face our second problem:

Even though (OrderID, Product) is our primary key, we still have a
lot of repeated data. Notice how “Alice” and “Bob” (customer names)
are repeated for every item in their respective orders. This is a
partial dependency.

2. Second Normal Form (2NF)

Rule:

• Must be in 1NF.
• All non-key attributes must be fully functionally dependent on the entire primary key. (No partial dependencies).
• Simply means Make sure every detail truly needs all the parts of that row’s unique identifier to belong there. If it doesn’t, move it.

The Problem (Partial Dependency):
In our Customer_Orders_1NF table, CustomerName depends only on OrderID, not on the full composite primary key (OrderID, Product). This means we’re repeating customer names unnecessarily.

Get Farhan Mahmood’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

How we fix it: We separate the attributes that depend only on part of the primary key into new tables.

Orders Table

• PK: OrderID

Order_Items Table

• PK: (OrderID, Product) (Composite Key)
• FK: OrderID references Orders(OrderID)

Now we face a third problem:

In our Order_Items table, notice that Supplier seems to depend on
Product (e.g., “TV” is always from “Sony,” “Mobile” from “Samsung”).
This means Supplier isn’t directly dependent on the entire
(OrderID, Product) key, but rather transitively through Product.
This is a transitive dependency.

3. Third Normal Form (3NF)

Rule:

• Must be in 2NF.
• No transitive dependencies (where a non-key attribute depends on another non-key attribute).
• Simply means “ No detail should be relying on another non-ID detail in the same record. Every detail should point directly back to the main unique ID of that record “

The Problem (Transitive Dependency):
In the Order_Items table, Supplier (a non-key attribute) depends on Product (another non-key attribute within the context of the Order_Items table’s PK). This means if we change the supplier for a product, we’d have to update multiple rows.

How we fix it: We move the Product-Supplier relationship into its own table.

Products Table

• PK: Product

Order_Items Table (Updated)

• PK: (OrderID, Product)
• FK: OrderID references Orders(OrderID)
• FK: Product references Products(Product)

At this point, our database is largely normalized and looks much cleaner! We have:

• Orders: Stores order-specific information (OrderID, CustomerName).
• Order_Items: Stores the details of what products are in each order (OrderID, Product, Quantity).
• Products: Stores product-specific information (Product, Supplier).

This structure eliminates most redundancy and makes our database much more robust and easier to manage.

While 3NF is often sufficient for most business applications, there are higher normal forms (BCNF, 4NF, 5NF) that address even more subtle types of data anomalies. For most practical purposes, the tables we’ve achieved through 3NF usually already satisfy these higher forms. I’ll explain those in the next part of this series.

You might have noticed that in our normalized tables, we still used names (like `Product` or `CustomerName`) as part of or even as the full primary key. While the rules of normalization focus on how attributes depend on keys (regardless of whether those keys are ‘IDs’ or ‘meaningful data’), in practice, we almost always introduce simple, auto-incrementing ‘ID’ columns (called surrogate keys) for our primary keys. It’s easier and often faster to query by a single, small integer ID. Foreign keys referencing a single-column ID are much simpler to define and maintain than those referencing multi-column composite keys. If you use a real-world piece of data like `CustomerName` as a primary key, and that customer’s name changes, you would need to update it not only in the `Customers` table but potentially in every other table that refers to that customer. Using an immutable `CustomerID` avoids this problem entirely.