Database Denormalization in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Database Denormalization in MySQL with Examples

In this article, I am going to discuss Database Denormalization in MySQL with Examples. Please read our previous article, where we discussed Database Normalization in MySQL. Understanding Database Denormalization is a must for a database developer. I am going to discuss Database Denormalization with examples so that it will be easier for you to understand the concepts.

Database Denormalization in MySQL

It is a database optimization technique where we can add redundant data to one or more tables and optimize the efficiency of the database. It is applied after doing normalization. It also avoids costly joins in a relational database. It is used on the already normalized database to increase performance. In denormalization, we are including data from one table to another table to reduce the number of joins in the query which helps in speeding up the performance.

Let’s take an example to implement a normalized database. We will create a school table, a student table, and a school_has_student table which contains school_id and student_id column.

Now I want to denormalize my database which means I want that School_has_Student table to also contain School table columns and all the student table columns. For which we would do a join between these tables.

Now the Student_has_School tables contain all of the student’s and school’s data.

Advantages of Denormalization

1. It minimizes the number of joins needed for a query by adding redundancy to a database.
2. Fetching queries can be simpler in denormalization because we need to look at fewer tables.

Disadvantages of Denormalization

1. Due to data redundancy, it takes large storage.
2. It is expensive to insert and update data in a table.
3. It makes data inconsistency as data can be modified in several ways.

Difference Between Normalization and Denormalization in MySQL

Normalization	Denormalization
It is used to delete redundant data from a database and replace it with non-redundant and reliable data.	It is used when a large number of insert/update/delete operations are performed and joins between tables are not expensive.
It is a technique used to merge data from multiple tables into a single table that can be queried quickly.	It is used when joins are costly and queries are run regularly on the tables.

Here, in this article, I try to explain Database Denormalization in MySQL with Examples. I hope you enjoy this Database Denormalization in the MySQL article. If you have any questions or queries about this Database Normalization in MySQL with Examples article, then please feel free to ask me in the comment section.