Database Normalization: Simplifying your tables
Tags: database, mysql, normalization, programming
Published: 2023-08-04 06:48:12

#### Database normalization

Is a process in database design that helps eliminate data redundancy and improve data integrity by organizing data into logical and efficient structures. It involves breaking down a database into multiple related tables and defining relationships between them.

**The main goals of database normalization are:**

- **Minimized data redundancy:** Normalization eliminates data redundancy by ensuring that each piece of data is stored in only one place. This reduces storage space requirements and avoids inconsistencies that can arise from storing redundant data. It also simplifies data maintenance since updates and modifications need to be made in only one location.

- **Improved data integrity:** By organizing data into logical and normalized structures, data integrity is enhanced. Relationships between tables are defined and enforced through constraints, such as primary keys and foreign keys, ensuring the accuracy, consistency, and reliability of data. This reduces the risk of data anomalies and inconsistencies.

- **Simplified data maintenance:** Normalization simplifies the process of modifying and updating data. Since data is stored in smaller, more manageable units, changes made to a particular piece of data only need to be applied in one location. This reduces the chances of introducing errors during data maintenance and ensures consistent updates throughout the database.

- **Enhanced query efficiency:** Normalized databases typically require less complex and faster queries compared to denormalized databases. With well-defined relationships between tables, queries can be written to retrieve and manipulate data more efficiently. This can improve overall performance and response times in database operations.

- **Facilitated database design:** Database normalization provides a systematic approach to database design. It helps designers break down complex data structures into smaller, more manageable tables and define relationships between them. This promotes clarity and organization in the database schema, making it easier to understand, maintain, and extend as the system evolves.

- **Scalability and flexibility:** Normalized databases are generally more scalable and flexible. As new requirements arise or the system grows, it is easier to extend and modify a normalized database schema without impacting the existing data or structure. This adaptability is particularly useful in dynamic and evolving systems.

**Forms of Database Normalization**

The normalization process involves decomposing a database schema into multiple normalization levels, known as normal forms. The most commonly used normal forms are:

**First Normal Form (1NF):** Ensures atomicity by eliminating repeating groups and ensuring that each column in a table contains only atomic values.

**Second Normal Form (2NF):** Builds upon 1NF by eliminating partial dependencies. It involves removing columns that depend on only a portion of the primary key and placing them in separate tables.

**Third Normal Form (3NF):** Builds upon 2NF by eliminating transitive dependencies. It involves removing columns that depend on non-key attributes and placing them in separate tables.

There are higher normal forms beyond 3NF, such as **Boyce-Codd Normal Form** (BCNF) and **Fourth Normal Form** (4NF), which address more complex dependencies. The choice of the appropriate normal form depends on the specific requirements and complexity of the database schema. However, in this article, we will be tackling only the first, second, and third normal form.

_Examples_

**Non-normalized Form**

![Non normalized](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tf77x3gmy2mhg80oi1h5.png)

In this **non-normalized form**, the `Books` table contains redundancy. The book _The Great Gatsby_ by _F. Scott Fitzgerald_ appears twice, leading to data redundancy and potential inconsistencies. If, for example, the author's name was to be updated, it would need to be modified in multiple places.

**1st Normal Form**

![First normal form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/03o1p7tpjc1hx29wz6im.png)

In this **1NF form**, the `Books` table has been divided into two separate tables: `Books` and `Authors`. The `Books` table now contains a foreign key, `author_id`, which references the primary key in the `Authors` table. This eliminates the data redundancy issue, as the author's information is stored in the `Authors` table only once.

**2nd Normal Form**

![Second normal form 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8vzmodhrejfj71cer04o.png)

![Second normal form 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gi8hjk2ktyc5hxo5sxkg.png)

In this 2NF form, the Category column has been moved to a separate Categories table. This eliminates the partial dependency, where the Category column was dependent on the Book ID rather than the primary key of the table.

**3rd Normal Form**

![Third normal form 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/18cs1qmnqoi4tk2x82yn.png)

![Third normal form 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k6jlw67a8jjl1lu14hoy.png)

In this **3NF form**, a new table called `Book_Author` has been introduced to represent the _many-to-many_ relationship between `books` and `authors`. This eliminates the transitive dependency, where the relationship between `books` and `authors` was indirectly dependent on the primary key of the `Books` table.

By following **1NF**, **2NF**, and **3NF**, the database is structured with reduced redundancy, improved data integrity, and more flexible and maintainable data management.

Normalization is an essential concept in relational database design and is widely used to improve data organization, efficiency, and integrity. However, it's important to strike a balance between normalization and performance, as excessive normalization can lead to increased complexity in querying and joining tables.