question

Kev Riley avatar image
Kev Riley asked

What is denormalization

What is denormalization and the benefits of doing so?

When would you denormalize a data model, and when would denormalization be a bad move?

database-designdata-modelling
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Um, isn't that when a computer geek's attempt to appear normal start to wear off? ;-)
1 Like 1 ·
Tom Staab avatar image
Tom Staab answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

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 :

http://en.wikipedia.org/wiki/Denormalization

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.