1 / 23

Denormalized Data

Denormalized Data. Overview. Denormalization Benefits Types of denormalization. Denormalization Overview. Denormalization Starts with a “normalized” model Adds “redundancy” to the design Reduces the “integrity” of the design Application code added to compensate.

lala
Download Presentation

Denormalized Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related