Why is denormalization bad

There are several denormalization techniques, each appropriate for a particular situation. If the calculation contains detail records, you should store the derived calculation in the master table. Whenever you decide to store derivable values, make sure that denormalized values are always recalculated by the system. But what if a user deletes a message from their account?

To pre-join tables, you need to add a non-key column to a table that bears no business value. This way, you can dodge joining tables and therefore speed up queries. Yet you must ensure that the denormalized column gets updated every time the master column value is altered.

This denormalization technique can be used when you have to make lots of queries against many different tables — and as long as stale data is acceptable. Imagine that users of our email messaging service want to access messages by category. However, when using hardcoded values, you should create a check constraint to validate values against reference values. This constraint must be rewritten each time a new value in the A table is required. This data denormalization technique should be used if values are static throughout the lifecycle of your system and as long as the number of these values is quite small.

Suppose we need to find out background information about users of an email messaging service, for example the kind, or type, of user. We can add a check constraint to the column or build the check constraint into the field validation for the application where users sign in to our email messaging service. There can be cases when the number of detail records per master is fixed or when detail records are queried with the master.

In these cases, you can denormalize a database by adding detail columns to the master table. This technique proves most useful when there are few records in the detail table. Imagine that we need to limit the maximum amount of storage space a user can get. Since the amount of allowed storage space for each of these restraints is different, we need to track each restraint individually.

Instead, we can go a different way and add denormalized columns to the Users table:. When you deal with historical data, many queries need a specific single record and rarely require other details. With this database denormalization technique, you can introduce a new foreign key column for storing this record with its master.

Often, users send not only messages but attachments too. The majority of messages are sent either without an attachment or with a single attachment, but in some cases users attach several files to a message.

Naturally, if a message contains more than one attachment, only the first attachment will be taken from the Messages table while other attachments will be stored in a separate Attachments table and, therefore, will require table joins. In most cases, however, this denormalization technique will be really helpful. If a database has over three levels of master detail and you need to query only records from the lowest and highest levels, you can denormalize your database by creating short-circuit keys that connect the lowest-level grandchild records to higher-level grandparent records.

This technique helps you reduce the number of table joins when queries are executed. In a normalized database, such queries would need to join the Users and Categories tables. To improve database performance and avoid such joins, we can add a primary or unique key from the Users table directly to the Messages table. This way we can provide information about users and messages without querying the Categories table, which means we can do without a redundant table join.

You overlooked how the author seemed to allow for denormalization but he immediately negated the followup dev work as "never justified" : " -- and performance is still unsatisfactory. You denormalize and, as Bolenok recognizes, introduce redundancy. Given a fully normalized db and a query which has to join say ten tables - what physical implementation would optimise this query? I find zero explanation of how to solve performance with a relational model.

As I understand the article, it seems to say There are several basic concepts you can apply to improve performance in the RDBMS and still avoid denormalization. LordHeini on Oct 19, root parent next [—]. Or you just avoid all that hassle and have some duplicates.

I have never seen a properly denormalized table. In practice you will get a "historically grown" system way to often and doing anything like that will break things. The whole article seems to be quite academic from my personal experience a textbook normalized database is slow beyond belief i did exactly that once and we had to revert it back. MustardTiger on Oct 19, root parent next [—]. Or better yet, since you don't care about your data anyways, just don't bother storing it.

Infinitely scalable and always blazingly fast. There's no such thing as "properly" denormalized, anything that is not normal is denormalized. I've seen lots of people say that, but then consistently found those same people don't actually know what the normalization rules are, and all they did was create a different denormalized database that happened to have poor performance for the queries they were using. For an existing application I personally prefer changing the index type, partitioning the table or tuning DB parameters first.

It's far less risky because you don't need to change a single query and it's transaprent to the application. Sure if you cannot get the desired perfomance by tuning the RDBMS than you need to consider changing the way how the tables are modelled.

From my experience, usually normalizing it one step furhter improves the performance, at least for OLTP use cases. If you are looking to solve performance "with a relational model" then you do not understand physical independence and the relational model.

This is exactly what the article explains you should not do. You mean DBMSs, not databases. Yes, that's the argument, but it's precisely this kind of lack of understanding that prevents better RDBMSs. The idea that you have to sacrifice the right abstraction for the sake of performance is preposterous, and can only be explained by lack of imagination. SQL DBMS are not relational, so they don't really implement relations they support bags and NULLs , not all their operations preserve closure, have weak support of relational constraints, of physical and logical independence, I could go on and on.

NULL is part of Codd's articulation of the relational model, even if later theorists have proposed cleaner variants of the model without it. Yeah, these things you mention have always annoyed me.

In particular, every relation should have a primary key, possibly consisting of zero attributes. If the primary key consists of zero attributes, the relation must have at most one element, because the primary key the empty tuple! Indeed, but it helps a great deal to give examples that show better ways, or if not working examples, even sketches of credible implementation ideas.

Very nice article, but the claims are so far from what is practiced in the industry nowadays see all companies working with tons of data and popping up big data solutions. So, while it might be perfectly true it somehow misses some big point. No, I think you missed its point. I dare you to demonstrate that the "big data solutions" guarantee logical and semantic correctness the way true RDBMSs do. Without that, garbage in garbage out. But because those "solutions" are so complex that nobody understand them, including those who designed them, their results are BELIEVED to be correct, which is not the same thing as being correct.

I have never worked at true "web scale", so my experience may or may not apply to such scenarios. I tend to make a more or less fully normalized structure, and introduce aggregated data outside of the core database.

It is essentially introducting eventual consistency, for performance critical data. This data can be stored in a database or a cache layer in front of the database if it is accessed frequently enough to keep it off the disk all the time. Good luck trying to show the number of likes in twitter, instagram, face, Kardashian's tinder In general the rules are:. Normalization is almost always a good idea in medium-sized apps or larger when data on both sides of the split can be modified and the potential modifications are independent of each other.

Updating or selecting from a single table is generally simpler than working with multiple tables, however with a well-written OR, this difference can be minimized for a large part of the data model space. Working with straight SQL, this is almost trivial to work around for an individual use case, albeit it in a non-object-oriented way.

Code needs to be kept relatively small to be manage-able and one effective way to do this is to divide the data model and build a service-oriented architecture around the various pieces of the data model. The goal of an optimal state of data de normalization should be thought of within the paradigm of your overall complexity management strategy.

In complex object hierarchies there are complexities that you don't see on the database side, like the cascading of updates. If you model relational foreign keys and crosslinks with an object ownership relationship, then when updating the object, you have to decide whether to cascade the update or not. This can be more complex than it would be in sql because of the difference between doing something once and doing something correctly always, sort of like the difference between loading a data file and writing a parser for that type of file.

It remains to be proven that this can never be simplified with a bit of flexibility on the SQL side enough to make any sql complexities worthwhile. There is also a point deserving delineation with one of the normalization precepts.

A central argument for normalization in databases is the idea that data duplication is always bad. This is frequently true, but cannot be followed slavishly, especially when there are different owners for the different pieces of a solution.

I saw a situation once in which one group of developers managed a certain type of transactions, and another group of developers supported auditability of these transactions, so the second group of developers wrote a service which scraped several tables whenever a transaction occurred and created a denormalized snapshot record stating, in effect, what was the state of the system at the time the transaction.

This scenario stands as an interesting use case for the data duplication part of the question at least , but it is actually part of a larger category of issues. Data constistency desires will often put certain constraints on the structure of data in the database that can make error handling and troubleshooting simpler by making some of the incorrect cases impossible. However this can also have the impact of "freezing" portions of data because changing that subset of the data would cause past transactions to become invalid under the consistancy rules.

Obviously some sort of versioning system is required to sort this out, so the obvious question is whether to use a normalized versioning system effective and expiration times or a snapshot-based approach value as of transaction time. There are several internal structure questions for the normalized version that you don't have to worry about with the snapshot approach, like:.

I think the optimal goal here is to learn not only what is correct in theory, but why it is correct, and what are the consequences of violations, then when you are in the real world, you can decide which consequences are worth taking to gain which other benefits. That is the real challenge of design. For reporting system, use normalization unless denormaliztion is required for whatever reason, such as ease of adhoc query, performance, etc.

How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. In terms of databases, is "Normalize for correctness, denormalize for performance" a right mantra? Ask Question. Asked 12 years, 11 months ago. Active 1 year, 11 months ago. Viewed 13k times. Aydya Aydya 1, 3 3 gold badges 19 19 silver badges 22 22 bronze badges.

This topic was also covered here: stackoverflow. Add a comment. Active Oldest Votes. The two most common reasons to denormalize are: Performance Ignorance The former should be verified with profiling, while the latter should be corrected with a rolled-up newspaper ;- I would say a better mantra would be "normalize for correctness, denormalize for speed - and only when necessary". Mitch Wheat k 42 42 gold badges silver badges bronze badges. Steven A. Lowe Steven A.

Lowe No excuse for justifying denormalisation: -1 — Philippe Grondier. RamiFar: apologies, yes; "correctness" in the sense of "eliminating update anomalies" — Steven A. Is this answer still relevant in ? I am following the documentation for the firebase firestore and it literally tells me to denormalize data in order to limit relations.

I'm quite new to NoSQL databases but the concept of deliberately introducing redundancy in your database just doesn't 'click' for me.

Where can I find some good lecture on how to store domain objects with many to many relations in a NoSQL database? Titulum: good question. You want a document or aggregate model instead - Vaughn Vernon's articles on Aggregate design are a great place to start dddcommunity. Show 1 more comment. Having said that, here's a more direct answer to the original question : It is useful to think of your database as consisting of a core design that is as close as possible to a logical design -- highly normalized and constrained -- and an extended design that addresses other issues like stable application interfaces and performance.

Chris Johnson Chris Johnson Definitely worth reading Chris' answer, a LOT of good general advice. Should be higher in terms of upvotes. That last paragraph is worth remembering.

Some denormalized designs are disciplined and functional. Create a free Team What is Teams? Learn more. Ask Question. Asked 2 years, 9 months ago.

Active 2 years, 9 months ago. Viewed times. This tutorial says the following: For proponents of denormalization, the thinking is as follows: normalization creates more tables as you proceed towards higher normal forms, but more tables mean there are more joins to be made when data is retrieved, which in turn slows down your queries. Improve this question. Add a comment. Active Oldest Votes.

Improve this answer. Walter Mitty Walter Mitty 3, 17 17 silver badges 21 21 bronze badges. There are several aspects to declarative data integrity in relational systems.

Michael Green Michael Green Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Does ES6 make JavaScript frameworks obsolete?

Podcast Do polyglots have an edge when it comes to mastering programming Featured on Meta.