I want to answer this discussion in four series in the first I will focus on the when and why to use denormalization.

When and Why to Use Denormalization

As with almost anything, you must be sure why you want to apply denormalization. You need to also be sure that the profit from using it outweighs any harm. There are a few situations when you definitely should think of denormalization:

• Maintaining history: Data can change during time, and we need to store values that were valid when a record was created. What kind of changes do we mean? Well, a person’s first and last name can change; a client also can change their business name or any other data. Task details should contain values that were actually at the moment a task was generated. We wouldn’t be able to recreate past data correctly if this didn’t happen. We could solve this problem by adding a table containing the history of these changes. In that case, a select query returning the task and a valid client name would become more complicated. Maybe an extra table isn’t the best solution.
• Improving query performance: Some of the queries may use multiple tables to access data that we frequently need. Think of a situation where we’d need to join 10 tables to return the client’s name and the products that were sold to them. Some tables along the path could also contain large amounts of data. In that case, maybe it would be wise to add a client_id attribute directly to the products_sold table.
• Speeding up reporting: We need certain statistics very frequently. Creating them from live data is quite time-consuming and can affect overall system performance. Let’s say that we want to track client sales over certain years for some or all clients. Generating such reports out of living data would “dig” almost throughout the whole database and slow it down a lot. And what happens if we use that statistic often?
• Computing commonly-needed values up front: We want to have some values ready-computed so we don’t have to generate them in real time.

It’s important to point out that you don’t need to use denormalization if there are no performance issues in the application. But if you notice the system is slowing down – or if you’re aware that this could happen – then you should think about applying this technique. Before going with it, though, consider other options, like query optimization and proper indexing. You can also use denormalization if you’re already in production but it is better to solve issues in the development phase. Source: Emil (2016)

Any thought: Dennis Mazur , Nawroz Abdulrazzak Jeyris Martínez Gutiérrez Nada QASIM Mohammed Dr.Hikmat Abdulkarim Almadhkhori Ahmad Saad Ahmad Al-Dafrawi Dr R Senthilkumar Lilianna Wojtynek ?

Hello,

I believe this is an important point to note in developing a data warehouse. A fairly common mistake can be made during implementation of data warehouse development assuming that a data warehouse is just like an OLTP database. There are fundamental differences that need to be noted between the traditional database and a data warehouse, normalization versus de-normalization. According to Arshad (2003), many do not realize until it is too late that a data warehouse is a process, encompassing many components, whose successful implementation requires knowledge of both business and technology — according to Erick (2003), recommended in his study on teaching Data Warehousing to Undergraduates that the entire concept of normalization is not required for a data warehouse (DW) development. Erick added that the end users are often statisticians, not computer people. Characteristics of the star-schema are explored (de-normalization, implicit hierarchies, relational qualities that still exist (relational sentences), granularity and the correct usage of the time dimension).

Normalized schemas try to avoid data dependencies. However, even highly normalized schemas have dependencies. Based on a study to improve analysis of large complex database by Tilmann and Meikel (2011), to increase performance for analytical processing, less normalized or highly de-normalized schemas are used, such as in data warehouses or online analytical processing (OLAP) systems. These systems show many different types of dependencies. De-normalization provides the flexibility for addressing requirements that are ad hoc and unplanned. It is quite suitable for the data warehouse as a normalized database limits the queries that can be run against.

Cheers,

Mohammed.

References:

Arshad, K. (2003) Data Warehousing 101 Concept and Implementation [Online] Available at: https://books.google.co.uk/books?hl=en&lr=&id=qbhQN3WBJ2IC&oi=fnd&pg=PR5&dq=de-normalization+or+normalization+for+data+value+conflict+in+DW+development&ots=-H0y4tWoMU&sig=-lupx0XxdT3OkJVu3tbZCKgHdao#v=onepage&q=de-normalization&f=false [Accessed 24 March 2019]

Erick, S. (2003) Teaching Data Warehousing to Undergraduates – Tales from the Warehouse Floor[Online] Available at: https://dl-acm-org.liverpool.idm.oclc.org/citation.cfm?id=947176 [Accessed 24 March 2019]

Tilmann R., and Meikel, P. (2011) Parallel Data Generation for Performance Analysis of Large, Complex RDBMS [Online] Available at: http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.454.8938&rep=rep1&type=pdf [Accessed 24 March 2019]

I want to answer this discussion in four series in the first I will focus on the when and why to use denormalization.

When and Why to Use Denormalization

As with almost anything, you must be sure why you want to apply denormalization. You need to also be sure that the profit from using it outweighs any harm. There are a few situations when you definitely should think of denormalization:

• Maintaining history: Data can change during time, and we need to store values that were valid when a record was created. What kind of changes do we mean? Well, a person’s first and last name can change; a client also can change their business name or any other data. Task details should contain values that were actually at the moment a task was generated. We wouldn’t be able to recreate past data correctly if this didn’t happen. We could solve this problem by adding a table containing the history of these changes. In that case, a select query returning the task and a valid client name would become more complicated. Maybe an extra table isn’t the best solution.
• Improving query performance: Some of the queries may use multiple tables to access data that we frequently need. Think of a situation where we’d need to join 10 tables to return the client’s name and the products that were sold to them. Some tables along the path could also contain large amounts of data. In that case, maybe it would be wise to add a client_id attribute directly to the products_sold table.
• Speeding up reporting: We need certain statistics very frequently. Creating them from live data is quite time-consuming and can affect overall system performance. Let’s say that we want to track client sales over certain years for some or all clients. Generating such reports out of living data would “dig” almost throughout the whole database and slow it down a lot. And what happens if we use that statistic often?
• Computing commonly-needed values up front: We want to have some values ready-computed so we don’t have to generate them in real time.

It’s important to point out that you don’t need to use denormalization if there are no performance issues in the application. But if you notice the system is slowing down – or if you’re aware that this could happen – then you should think about applying this technique. Before going with it, though, consider other options, like query optimization and proper indexing. You can also use denormalization if you’re already in production but it is better to solve issues in the development phase. Source: Emil (2016)

Any thought: Dennis Mazur , Nawroz Abdulrazzak Jeyris Martínez Gutiérrez Nada QASIM Mohammed Dr.Hikmat Abdulkarim Almadhkhori Ahmad Saad Ahmad Al-Dafrawi Dr R Senthilkumar Lilianna Wojtynek ?