What is denormalization and the benefits of doing so?
When would you denormalize a data model, and when would denormalization be a bad move?
asked
What is denormalization
Um, isn't that when a computer geek's attempt to appear normal start to wear off? ;-)
The obvious answer is that denormalization is the process of undoing the normalization done in a typical database design. So what does that mean? Well, it means allowing for redundant data to reduce the number of multi-table (or view) joins necessary.
Picture a normalized database as one with 4 small tables (customers, locations, products, customer_orders) joined together on appropriate keys. A denormalized version might have just 1 table with all of the necessary columns.
Normalization Pros:
- no (or at least reduced) redundancy
- faster writes if just 1 smaller table needs updating/inserting/deleting
- ideal for transaction (OLTP) systems
Denormalization Pros:
- easier for nontechnical users to understand
- faster reads if all or most of the data is needed anyway
- ideal for reporting (OLAP) systems
This is just a high-level overview. If you would like more information, I suggest searching the internet using some of the keywords mentioned here.
You may increase performance and reduce complexity in some cases with denormalization in a datawarehouse because you can reduce amount of joined tables, but as you are storing redundant data you will increase the size of the table and indexes you will add more IO, and that will in the end slow you down. Sometimes it is more clever to break the code into smaller pices with temp tables etc.
In a OLTP database it would normaly be a bad idea to denormalize due to the cost of data maintenance, and you will loose more than you will benefit from denormalization.
In a normalized database you try to break redundant data into separate table, for example you would probably have a country table with a countrycode and every table that need a country will have the country code instead of the full name. If you don't, what will happen if the country name will change? You will need to update every row in every table, and that is not an efficient solution. Yes, country names doesn't change that often, but it doest.
read more about denormalization in :
Denormalization means the deliberate creation of extra join dependencies that aren't implied by the superkeys of a relation. This is generally undesirable because it introduces redundancy to the database which could cause incorrect results and ambiguity.
Normalization/denormalization has nothing to do with performance because it specifies only the logical characteristics of the database, not their physical implementation. However, given the poor degree of Physical Data Independence in many DBMSs it is sometimes necessary to compromise your logical model in order to make corresponding changes to the underlying physical implementation. Another reason to make such compromises is if you need to implement some constraint or other logic that the DBMS cannot otherwise support.
