09 - Redundancy & Denormalization

Whoa. The actual title is far too long: "Methodology - Monitoring and Tuning the Operational System Transperancies."

Denormalization: Putting the Anomalies Back

---

Objectives

• Meaning of denormalization
  
  
• When to denormalize to improve performance
  
  
• Importance of monitoring and tuning the operational system. (note: look@ index first for performance issues)

---

Step 8 - Consider the introduction of Controlled Redundancy

To determine whether introducing redundancy in a controlled manner by relaxing the normalization rules will improve the performance of the system.

• Result of normalization is a logical database design that is structurally consistant and has minimal redundancy.
  
  
• However, sometimes a normalized database design does not provide maximum processing efficiently.
  
  
• It may be necessary to accept the loss of some of the benefits of a fully normalized design in favor of performance.
  
  
• Also consider that denormalization:
  
  
• Makes implementation more comlplex
  
  
• Often sacrifices flexibility
  
  
• May speed up retrievals, but it slows down updates
  
  
• Consider denormalization in the following situations, specifically to speed up frequent or critical transactions:
  

1. Combining 1:1 relationships
   
   
2. Duplicating non key attributes in 1:* relationships to reduce joins
   
   
3. Duplicating foreign key attributes in 1:* relationships to reduce joins
   
   
4. Duplicating attributes in *:* relationships to reduce joins
   
   
5. Introducing repeating groups
   
   
6. Merging lookup tables with base relations
   
   
7. Creating extract tables

• Reports can access derived data and perform multi-relation joins on the same set of base relations. However, data the report is based on may be relatively static or may not have to be current.
  
  
• Possible to create single, highly denormalized extract table based on the relations required by reports, and allow the users to access extract table directly insted of using base relations.
  

---

Monitor and Tune the Operational System

To monitor operational system and improve performance of the system to correct inappropriate design decisions or reflect changing requirements.