{"id":90614,"date":"2021-04-19T17:00:01","date_gmt":"2021-04-19T17:00:01","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=90614"},"modified":"2021-04-19T14:43:53","modified_gmt":"2021-04-19T14:43:53","slug":"denormalization-in-production-common-problem-and-solution","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/denormalization-in-production-common-problem-and-solution\/","title":{"rendered":"Denormalization in Production: Common Problem and Solution"},"content":{"rendered":"

After a young developer (or database developer) learns about normalization, the quality of his databases increases a lot, there is no doubt about that.<\/p>\n

However, sometimes we need some denormalization, even in production databases. I faced some experienced developers that, although, they would agree this should be analyzed in a case-by-case scenario, they never saw an example of this situation.<\/p>\n

There is a very simple and common example of this situation and we can even go beyond the example and draw some simple rules about this.<\/p>\n

First, one small disclaimer: People with way more knowledge about modelling than me may argue denormalization in production doesn\u2019t really exist. The fields below are, in fact, two different information.\u00a0 We are used to put the same name to them and therefore, we think they are the same information. Sometimes we even drop one of them from the database.<\/p>\n

However, these are only two different ways to look to the same thing and achieve the same result. In my opinion, looking into this as denormalization is easier.<\/p>\n

What\u2019s wrong with the model below<\/p>\n

<\/p>\n

This is a classic model about products and orders. Of course, the model is simplified, but the challenge is to notice what is wrong with this model. Does it appear Ok?<\/p>\n

The problem:<\/p>\n

What happens with the record of old orders when the company decides to raise the price of the products ?<\/em><\/strong><\/p>\n

Probably you already guessed the result: Every time a raise in prices happens, this affects old orders as well, destroying the data.<\/p>\n

The Solution: Denormalization<\/h2>\n

It\u2019s easy to solve this problem: When we register an order, we need to copy the price together the order, as a static value.<\/p>\n

The new model will be like this:<\/p>\n

<\/p>\n

Why Denormalization?<\/h2>\n

According to the 3rd<\/sup> normal form, we can\u2019t have any transitive dependency between any field and the primary key. In other words, the primary key of a table needs to uniquely identify all the field values, the values should not be linked to any other field.<\/p>\n

The UnitPrice<\/strong> field in the OrderDetail<\/strong> table has a dependency with ProductId<\/strong> field, which is not part of the OrderDetail<\/strong> primary key. This establishes a transitive dependency and due to that, this table is not in the 3rd<\/sup> normal form<\/p>\n

How to Identify these scenarios<\/h2>\n

In order to identify these situations when we face them, we can look for one specific scenario in our database model. This scenario is easier to identify if we apply a BI point of view.<\/p>\n

From a BI point of view, we have facts happening in our business and these facts are defined by many linked information to them, the dimensions.<\/p>\n

The order is a fact, something that happens in our business. The product is a dimension linked to the fact. An order was made, and this order is related to a product.<\/p>\n

The fact is static along the time and that\u2019s what highlights our problem and solution. The information related to the fact can\u2019t change. So, the price from the moment of the order needs to stay static. That\u2019s when we will know we need to \u201ccopy\u201d the price from the Product<\/strong> table to the OrderDetail<\/strong> table.<\/p>\n

Everything is a misunderstanding<\/h2>\n

\nSome people could say this is not denormalization at all, it\u2019s just a misunderstanding. They consider the price of the product on the Product<\/strong> table is not the same information as the price of the product in the OrderDetail<\/strong> table. They have a different lifecycle, different meaning. The fact they will have the same value for a while is not important at all.<\/p>\n

I, in my humble opinion, prefer the first point of view, based on the idea of facts. It\u2019s easy to identify and follow.<\/p>\n

Conclusion<\/h2>\n

I\u2019m not a book worm about detailed concepts on database modelling. In case I missed something, feel free to add on the comments. However, I believe this is a practical approach to identify a scenario where denormalization is needed in production. I already found developers get confused with this.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

After a young developer (or database developer) learns about normalization, the quality of his databases increases a lot, there is no doubt about that. However, sometimes we need some denormalization, even in production databases. I faced some experienced developers that, although, they would agree this should be analyzed in a case-by-case scenario, they never saw……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[137125],"coauthors":[6810],"class_list":["post-90614","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sqlserver"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=90614"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90614\/revisions"}],"predecessor-version":[{"id":90617,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90614\/revisions\/90617"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=90614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=90614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=90614"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=90614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}