Understanding Database Normalization

Introduction to Database Normalization

Database normalization is the process of organizing data in a database to minimize data redundancy and improve data integrity. There are several principles of database normalization, including first normal form (1NF), second normal form (2NF), and third normal form (3NF).

First Normal Form (1NF)

A table is in 1NF if each cell in the table contains a single value. For example, consider a table with the following structure:

| Customer ID | Name | Orders |
| --- | --- | --- |
| 1 | John Doe | Order 1, Order 2 |

This table is not in 1NF because the Orders column contains multiple values. To normalize this table, you can create a separate table for orders:

| Customer ID | Name |
| --- | --- |
| 1 | John Doe |

| Order ID | Customer ID | Order Name |
| --- | --- | --- |
| 1 | 1 | Order 1 |
| 2 | 1 | Order 2 |

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and each non-key attribute in the table depends on the entire primary key. For example, consider a table with the following structure:

| Order ID | Customer ID | Order Name | Customer Name |
| --- | --- | --- | --- |
| 1 | 1 | Order 1 | John Doe |

This table is not in 2NF because the Customer Name column depends only on the Customer ID column, not the entire primary key. To normalize this table, you can create a separate table for customers:

| Order ID | Customer ID | Order Name |
| --- | --- | --- |
| 1 | 1 | Order 1 |

| Customer ID | Customer Name |
| --- | --- |
| 1 | John Doe |

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and there are no transitive dependencies. For example, consider a table with the following structure:

| Order ID | Customer ID | Order Name | Salesperson ID | Salesperson Name |
| --- | --- | --- | --- | --- |
| 1 | 1 | Order 1 | 1 | John Smith |

This table is not in 3NF because the Salesperson Name column depends on the Salesperson ID column, which in turn depends on the Order ID column. To normalize this table, you can create a separate table for salespeople:

| Order ID | Customer ID | Order Name | Salesperson ID |
| --- | --- | --- | --- |
| 1 | 1 | Order 1 | 1 |

| Salesperson ID | Salesperson Name |
| --- | --- |
| 1 | John Smith |

By following these principles of database normalization, you can improve the integrity and scalability of your database.