What is Normalization?

Hello,

Today, I will talk to you about the concept of normalization, which is a part of successful database design. What is normalization? What kind of problems do we encounter without normalization? How does normalization solve these problems? We will seek answers to such questions.

Let's get started :)

What is Normalization?

Normalization is a technique for organizing the contents of tables in databases. It is part of successful database design. Without normalization, database systems can be incorrect, slow, and inefficient, and may not produce the data you expect.

It is important to normalize a database to minimize data redundancy and to ensure that only relevant data is stored in each table. A table should be about a specific subject and should only contain supporting topics. For example, a table containing information about sales representatives and customers serves various purposes:

· Identify the sales representatives in your organization

· List all the customers your company contacts to sell products

· Determine which sales staff should be called for specific customers

By limiting a table to a single purpose, you reduce the amount of duplicate data in your database. This eliminates some problems caused by database modifications. Some established rules are used to achieve these goals. New tables are created when applying these rules.

There are three normal forms that most databases rely on. As tables meet each successive database normalization form, they become less prone to database modification problems and focus more on a single purpose or subject.

Reasons for Database Normalization

There are three main reasons to normalize a database. The first is to minimize duplicate data, the second is to minimize or prevent data modification issues, and the third is to simplify queries.

Now, let's look at some unnormalized data and discuss some potential problems.

Consider the following table;

The first thing to note is that this table serves many purposes, including the following:

1. Identifying the organization's sales staff

2. Listing sales offices and phone numbers

3. Associating a sales representative with a sales office

4. Showing each sales staff's customers

Let's pause and think here. In general, I want to see tables with a single purpose. A table serving many purposes brings many challenges; that is, data redundancy, data modification issues, and increased effort to query the data…

Remember; one of the most important principles of database logic says:

Create a separate table for each object type!

Data Redundancy and Modification Issues

Note that for each SalesPerson, we list both the SalesOffice and OfficeNumber. There is redundant sales person data. Duplicate information creates two problems:

1. It increases storage and reduces performance.

2. It becomes harder to maintain data changes.

For example:

Suppose we move the Istanbul office to Antalya. To reflect this properly in our table, we need to update all SalesPerson entries currently in Istanbul. Our table is a small example, but you can see that this could involve hundreds of updates.

Insert Problem

In our example, we cannot record a new SalesOffice without adding a SalesPerson. Why? Because we need to provide a primary key to create the record. So, we cannot add a SalesOffice without adding an EmployeeID.

Update Problem

In this case, we have the same information in several rows. For example, if the OfficeNumber changes, multiple updates are required. If we do not update all rows, inconsistencies will occur and data integrity will not be maintained.

Delete Problem

Deleting a row causes the removal of multiple sets of facts. For example, if Boyce Evenue retires, deleting that row causes us to lose information about the Izmir office.

Search and Sort Problems

In the SalesStaff table, if you want to search for a specific customer like Ford, you need to write a query like the following:

Obviously, if the customer were in a column in some way, our query would be simpler. Also, consider if you wanted to sort by customer…

You can eliminate or reduce these problems by separating the data into different tables. This places the data into tables that serve a single purpose.

The process of redesigning the table is database normalization. I will not discuss the normalization rules. Once you understand the logic, you will be able to design your database intuitively without worrying about the rules.

Now…

We have seen the importance of the concept of normalization for database design. I hope it has been useful.