DEV Community

Muhammad Salem
Muhammad Salem

Posted on

Database Normalization simplified

Applying the normalization rules
You can apply the data normalization rules (sometimes just called normalization rules) as the next step in your design. You use these rules to see if your tables are structured correctly. The process of applying the rules to your database design is called normalizing the database, or just normalization.

Normalization is most useful after you have represented all of the information items and have arrived at a preliminary design. The idea is to help you ensure that you have divided your information items into the appropriate tables. What normalization cannot do is ensure that you have all the correct data items to begin with.

You apply the rules in succession, at each step ensuring that your design arrives at one of what is known as the "normal forms." Five normal forms are widely accepted — the first normal form through the fifth normal form. This article expands on the first three, because they are all that is required for the majority of database designs.

Sure! Let's walk through the normalization process step by step, up to the third normal form (3NF), using clear examples to illustrate each step.

First Normal Form (1NF)

Definition: A table is in the first normal form if:

  1. All the columns contain atomic (indivisible) values.
  2. Each column contains values of a single type.
  3. Each column contains a unique name.
  4. The order in which data is stored does not matter.

Example:

Consider a table for storing customer orders:

OrderID CustomerName CustomerPhone Product1 Product2 Product3 ...
1 John Doe 123-456-7890 Widget A Widget B NULL ...
2 Jane Smith 987-654-3210 Widget C NULL NULL ...

This table is not in 1NF because it has repeating groups (Product1, Product2, Product3, ...).

To convert this to 1NF, we should remove the repeating groups and ensure each column contains only atomic values:

OrderID CustomerName CustomerPhone Product
1 John Doe 123-456-7890 Widget A
1 John Doe 123-456-7890 Widget B
2 Jane Smith 987-654-3210 Widget C

Now, each column contains atomic values and there are no repeating groups.

Second Normal Form (2NF)

Definition: A table is in the second normal form if:

  1. It is in 1NF.
  2. All non-key attributes are fully functional dependent on the primary key (i.e., there are no partial dependencies).

Example:

Consider the 1NF table from above:

OrderID CustomerName CustomerPhone Product
1 John Doe 123-456-7890 Widget A
1 John Doe 123-456-7890 Widget B
2 Jane Smith 987-654-3210 Widget C

Here, CustomerName and CustomerPhone are partially dependent on OrderID since they depend on the customer, not the specific product in the order.

To achieve 2NF, we separate the table into two tables: one for orders and another for customers:

Orders Table:

OrderID CustomerID Product
1 1 Widget A
1 1 Widget B
2 2 Widget C

Customers Table:

CustomerID CustomerName CustomerPhone
1 John Doe 123-456-7890
2 Jane Smith 987-654-3210

Now, all non-key attributes are fully dependent on the primary key of each table.

Third Normal Form (3NF)

Definition: A table is in the third normal form if:

  1. It is in 2NF.
  2. There are no transitive dependencies (i.e., non-key attributes do not depend on other non-key attributes).

Example:

Consider our 2NF tables:

Orders Table:

OrderID CustomerID Product
1 1 Widget A
1 1 Widget B
2 2 Widget C

Customers Table:

CustomerID CustomerName CustomerPhone
1 John Doe 123-456-7890
2 Jane Smith 987-654-3210

Assume we have another table for products with product details:

Products Table:

ProductID ProductName Price
A Widget A 10.00
B Widget B 15.00
C Widget C 20.00

To achieve 3NF, ensure there are no transitive dependencies. In our case, there are none since ProductName and Price are attributes of the Products table and are not dependent on any other non-key attribute.

The final structure is:

Orders Table:

OrderID CustomerID ProductID
1 1 A
1 1 B
2 2 C

Customers Table:

CustomerID CustomerName CustomerPhone
1 John Doe 123-456-7890
2 Jane Smith 987-654-3210

Products Table:

ProductID ProductName Price
A Widget A 10.00
B Widget B 15.00
C Widget C 20.00

Summary

  1. 1NF: Ensure each table column contains atomic values and there are no repeating groups.
  2. 2NF: Ensure the table is in 1NF and all non-key attributes are fully dependent on the primary key.
  3. 3NF: Ensure the table is in 2NF and there are no transitive dependencies.

By following these steps, you can normalize your database to ensure it is well-structured and efficient, reducing redundancy and ensuring data integrity.

Top comments (0)