Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Pros and Cons of Database Normalization

DZone's Guide to

Pros and Cons of Database Normalization

To normalize or not to normalize? Find out when the normalization of a database is helpful and when it is not at all helpful.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

When using a relational database, normalization can help keep the data free of errors and can also help ensure that the size of the database doesn't grow large with duplicated data. At the same time, some types of operations can be slower in a normalized environment. So, when should you normalize and when is it better to proceed without normalization?

What Is Normalization?

Database normalization is the process of organizing data within a database in the most efficient manner possible. For example, you likely do not want a username stored in several different tables within your database when you could store it in a single location and point to that user via an ID instead.

By keeping the unchanging user ID in the various tables that need the user, you can always point it back to the appropriate table to get the current username, which is stored in only a single location. Any updates to the username occur only in that place, making the data more reliable.

An example of using the first normal form. Source: ChatterBox's .NET.

What Is Good About Database Normalization?

A normalized database is advantageous when operations will be write-intensive or when ACID compliance is required. Some advantages include:

  1. Updates run quickly due to no data being duplicated in multiple locations.
  2. Inserts run quickly since there is only a single insertion point for a piece of data and no duplication is required.
  3. Tables are typically smaller than the tables found in non-normalized databases. This usually allows the tables to fit into the buffer, thus offering faster performance.
  4. Data integrity and consistency is an absolute must if the database must be ACID compliant. A normalized database helps immensely with such an undertaking.

What Are the Drawbacks of Database Normalization?

A normalized database is not as advantageous under conditions where an application is read-intensive. Here are some of the disadvantages of normalization:

  1. Since data is not duplicated, table joins are required. This makes queries more complicated, and thus read times are slower.
  2. Since joins are required, indexing does not work as efficiently. Again, this makes read times slower because the joins don't typically work well with indexing.

What If the Application Is Read-Intensive and Write-Intensive?

In some cases, it isn't as clear that one strategy should be used over the other. Obviously, some applications really need both normalized and non-normalized data to work as efficiently as possible.

In such cases, companies will often use more than one database: a relational data such as MySQL for ACID compliant and write-intensive operations and a NoSQL database such as MongoDB for read-intensive operations on data where duplication is not as big of an issue.

What NoSQL and SQL databases do well. Source: SlideShare.

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
database ,normalization ,relational databases

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}