Denormalized Data Overview Denormalization Benefits Types of denormalization

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

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

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

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

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

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

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

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

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

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

Repeating Current Detail with Master Before A pk * Id B pk, fk pk * A_Id * Start_date * Price Add a column to the master to store the most current details. After A pk * Id * Current_price

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

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

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

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

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

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

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

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. After FOLDERS (FDR) pk *fk * * * Id Fdr_id Name Level_no

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

Practices Name that Denormalization Triggers Denormalize Price Lists Global Naming