Database design

Database Normalization

Database normalization is a set of formalized rules that allow you to bring a database to a form in which it will be easiest to use, and the likelihood of logical errors will be minimal.

To better understand what this means, consider an example. Suppose we have a table that stores information about users and the cities they live in. In this table, the city name Kyiv is listed with an error (the correct spelling would be Kyiv). If we want to correct the city name, we would have to update information for all users, which would consume a lot of computational resources. Additionally, this update will most likely take some time, and while it is in progress, the database will be in an incorrect state, since some users will have the city name written as Kyiv and others already as Kyiv. Database normalization helps avoid such situations by eliminating duplication and undesirable characteristics.

There are three main normal forms, each of which helps solve certain problems in the database structure.

First Normal Form

The First Normal Form (1NF) requires that all values in a table be atomic, meaning indivisible. In other words, any value in any column must contain only one value. Sometimes the definition of 1NF is formulated as follows: to obtain ready-to-use data from any column of the table, no additional computations should be required.

Consider an example of a table with user information. A user may have more than one phone number. To get any usable number, you would need to read the value of the Phone column for the user, and then perform the split function on that column's value using a comma. If we bring this table to the first normal form, then the Phone column will store only one phone number per row.

Second Normal Form

The Second Normal Form (2NF) is achieved when the table is already in 1NF and one additional condition is met: all values that repeat within a row are moved to a separate table.

Let's look at our user table again. It essentially contains user phone numbers, and names are repeated in it. If we bring this database to 2NF, the names should be moved to a separate table. After this transformation, we will have a separate table with user information and a separate table with their phone numbers. In the phone table, instead of repeated user names, only their identifiers will be stored.

Third Normal Form

The Third Normal Form (3NF) requires that the table be in 2NF and that the data in the table depends only on the primary key. In other words, 3NF requires that there be no transitive dependencies in the table.

Consider an example and imagine that our database also stores information about store employees who can work in different departments. The database also stores information about the employee's office, which depends on the department. The IT department is located only in the Kyiv-1 office, and the Accounting department is located only in the Kyiv-2 office. To bring such a table to 3NF, the information about offices and their locations needs to be moved to a separate table, and the user table should only reference the department information.

Understanding normalization forms allows you to build better databases and optimize their performance.