Denormalized Data

1. Denormalized Data

2. Overview • Denormalization • Benefits • Types of denormalization

3. Denormalization Overview • Denormalization • Starts with a “normalized” model • Adds “redundancy” to the design • Reduces the “integrity” of the design • Application code added to compensate

4. Denormalization Techniques • Storing Derivable Values • Pre-joining Tables • Hard-Coded Values • Keeping Details with Master • Repeating Single Detail with Master • Short-Circuit Keys

5. Storing Derivable Values Before A B pk,fk pk A_id Sequence_No Quantity pk IdX * * * * * Add a column to store derivable data in the “referenced” end of the foreign key. A After pk Id XTotal_quantity * **

6. EMail Example of Storing Derivable Values Before MESSAGES (MSE) REC_MESSAGES (RME) USERS (USR) pk Id Subject Text * ** pk,fk pk,fk Usr_Id Mse_Id * * pk Id Per_name * * Store derivable column in the ‘referenced’ end of the foreign key. MESSAGES (MSE) After pk Id Subject TextNumber_of_times_received * ***

7. Pre-Joining Tables Before B A pk fk * * Id A_id pk Id Col_a * * Add the non_key column to the table with the foreign key. B After pk fk Id A_idA_col_a * **

8. EMail Example of Pre-Joining Tables Before RECEIVED_MESSAGES (RME) FOLDERS (FDR) pk,fk pk,fk Mse_id Flr_id Date_received * ** * * Id Name pk Create a table with all the frequently queried columns. RECEIVED_MESSAGES (RME) After pk,fk pk,fk Mse_id Flr_id Date_receivedFdr_Name ** **

9. Hard-Coded Values Before B A Id A_id pk fk * * pk IdType ** Remove the foreign key and hard code the allowable values and validation in the application. B After pk Id A_Type **

10. Email Example of Hard-Coded Values Before USERS (USR) BUSINESS_TYPES (BTE) pk fk Id Bte_id Per_name * ** pk Id Name * Hard code the allowable values and validation in the application. USERS (USR) After pk IdBusiness_typePer_name * **

11. Keeping Details with Master Before B A pk,fk pk A_id Type Amount * ** pk * Id Add the repeating detail columns to the master table. A pk Id Amount_1Amount_2Amount_3 Amount_4 Amount_5 Amount_6 ******* After

12. EMail Example Keeping Detail with Master Before STORAGE_QUOTAS (SQA) USERS (USR) pk,fk pk Usr_Id Storage_type AllocatedAvailable * *** pk * * Id Name Add the repeating detail columns to the master table. USERS (USR) pk Id NameMessage_Quota_Allocated Message_Quota_Available File_Quota_Allocated File_Quota_Available After ******

13. Repeating Current Detail with Master Before B A pk,fkpk A_IdStart_datePrice * * * pk * Id Add a column to the master to store the most current details. A After pk IdCurrent_price * *

14. Short-Circuit Keys Before C B A pk fk Id B_id pk fk Id A_id * * * * pk * Id Create a new foreign key from the lowest detail to the highest master. B A C After pk fk Id A_id * * pk fkfk Id B_idA_id * ** pk * Id

15. EMail Example of Short-Circuit Keys Before RECEIVED_MESSAGES (RME) USERS (USR) FOLDERS (FDR) pk fk Name Usr_id * * pk fk Id Fdr_name * * pk ** IdName Create a new foreign key from the lowest detail to the highest master. After RECEIVED_MESSAGES (RME) FOLDERS (FDR) USERS (USR) pk fk Name Usr_id * * pk fk fk Id Fdr_name Usr_name *** pkuk ** IdName

16. End Date Column Before B A A_id Start_date pk,fk pk * * pk Id * Add an end date column to speed up queries so that they can use a between operator. B After A_Id Start_date End_date pk,fk pk * * o

17. Example of End Date Column Before PRICES (PCE) PRODUCTS (PDT) pk,fk pk Pdt_id Start_date Price * ** * * Id Name pk Create an extra column derivable End_date column. PRICES (PCE) After pk,fk pk Pdt_id Start_date Price End_date * **o

18. Current Indicator Column Before B A A_id Start_date pk,fk pk * * pk * Id Add a column to represent the most current record in a long list of records. B After A_Id Start_dateCurrent_indicator pk,fk pk * *o

19. Example of Current Indicator Column Before PRICES (PCE) PRODUCT (PDT) pk,fk pk Pdt_id Start_datePrice * ** * * Id Name pk Add a column to represent the most current record, in a long list of records. PRICES (PCE) pk,fk pk After Pdt_idStart_date PriceCurrent_indicator * **o

20. Hierarchy Level Indicator Before A pk fk Id A_id * * Create a column to represent the hierarchy level of a record. A After pk fk * ** Id A_idLevel_no

21. Example of Hierarchy Level Indicator Before FOLDERS (FDR) pk fk Id Fdr_id Name * ** Create a column to represent the hierarchy level of a record. FOLDERS (FDR) After pk fk * *** Id Fdr_id NameLevel_no

22. Denormalization Summary Denormalization Techniques • Storing Derivable Information • End Date Column • Current Indicator • Hierarchy Level Indicator • Pre-Joining Tables • Hard-Coded Values • Keeping Detail with Master • Repeating Single Detail with Master • Short-Circuit Keys

23. Practices • Name that Denormalization • Triggers • Denormalize Price Lists • Global Naming