Database normalization is the process of organizing data into a linked table. This significantly increases the integrity and productivity of the query. To normalize a table, you must divide it into sub-tables and establish relationships between them.
Thus, database normalization is a practical optimization of table structures. Much attention here is paid to setting up relationships between tables.
There are several types of database normalization process:
Data normalization avoids data redundancy by distributing information to different addresses of one database.
In addition to cumbersome maintenance and updating of the database, a large amount of information can also be inconsistent. Anomalies of inserting, updating and deleting data are becoming frequent. In addition, redundant data significantly increases the size of the database and takes up too much disk space.
This optimization method is applied after normalization. Denormalization is the deliberate bringing of a database structure to a state that doesn't meet the requirements of normalization. Denormalization is used to add redundancy to fulfill a query quickly. In this case, the number of tables decreases, in contrast to normalization.
Fewer connections reduce errors. But there are drawbacks to this method: denormalization can make it difficult to update and refine the code.
We've already found that normalization and denormalization are the two pillars of one process — changing the structure of databases. Now you might be interested in seeing the border line between the two more clearly. Satisfy your wish!
Let's also look at examples of normalization and denormalization in action. Here's the table we can use as the data template:
If we normalize this data according to the company's parameters, we'll get such a result:
In the same way, we can create a table for the product, which will include the ID, name, and quantity of the product.
Let's move on! Now we've got 2 tables:
And what denormalization brings here? Thanks to it, we'll get the following table:
As you may see, the use of normalization and denormalization is an integral part of working with databases. The benefit of using this or that method should be considered in each case. With the right choice, you will never face data loss problems or scaling difficulties.