Why is denormalization done

By clicking sign up, you agree to receive emails from Techopedia and agree to our Terms of Use and Privacy Policy. In many cases, denormalization involves creating separate tables or structures so that queries on one piece of information will not affect any other information tied to it. For instance, where more global data variables such as customer names are tied together with single purchases in a purchase history, a database administrator will want to make sure that work done on an item purchased will not incorrectly affect the entire customer account.

Therefore, database handlers will separate the two pieces of information, sometimes with redundant data, so that they can be worked on separately. Where denormalization comes in is that adding redundant data allows for more sophisticated search results. This is where having redundant data can allow databases to give different results based on exactly what the user is asking for. Again, having this redundant data can also improve performance based on the specific ways that a database searches for a particular item.

Challenges involved in denormalization include documenting the process carefully to avoid some kinds of anomalies that can occur as a result of data mismatch. Alex B. Tags: Tech Insights. Website speed is a priority for businesses in When to denormalize a database What is database denormalization?

Developers should use this tool only for particular purposes: 1 To enhance query performance Typically, a normalized database requires joining a lot of tables to fetch queries; but the more joins, the slower the query. Advantages Disadvantages No need to use multiple joins DML is required to update the non-denormalized column You can put off updates as long as stale data is tolerable An extra column requires additional working and disk space.

Advantages Disadvantages No need to implement a lookup table Recoding and restating are required if look-up values are altered No joins to a lookup table.

Advantages Disadvantages No need to create joins for queries that need a single record Data inconsistencies are possible as a record value must be repeated. Advantages Disadvantages Fewer tables are joined during queries Need to use more foreign keys Need extra code to ensure consistency of values. CONTENTS When to denormalize a database 1 To enhance query performance 2 To make a database more convenient to manage 3 To facilitate and accelerate reporting Database denormalization techniques Storing derivable data Using pre-joined tables Using hardcoded values Keeping details with the master Repeating a single detail with its master Adding short-circuit keys Drawbacks of database denormalization Database denormalization tips Final thoughts.

Authors: Gleb B. Rate this article! Share article with. Comments 0 Sign in. Sign in with Facebook. Sign in with Linkedin. There are no comments yet Leave comment. Subscribe on our news. Recommended Articles.

Tech Navigator views 10 min. Entrepreneurship views 9 min. Tech Navigator views 13 min. Thanks for your subscription! Technical articles. Business articles. Please fill in the search field. No need to look up source values each time a derivable value is needed.

Running data manipulation language DML statements against the source data requires recalculation of the derivable data. No need to perform a calculation for every query or report. Data inconsistencies are possible due to data duplication. DML is required to update the non-denormalized column. Writing code in comment? Please use ide. Load Comments. What's New. Most popular in DBMS. More related articles in DBMS. We use cookies to ensure you have the best browsing experience on our website.

Example: Suppose after normalization we have two tables first, Student table and second, Branch table. If we want the name of students along with the name of the branch name then we need to perform a join operation.

The problem here is that if the table is large we need a lot of time to perform the join operations. Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here.