SCD Types.. Part II

Type III

This method tracks changes using separate columns and preserves limited history. The Type II preserves unlimited history as it's limited to the number of columns designated for storing historical data. The original table structure in Type I and Type II is the same but Type III adds additional columns. In the following example, an additional column has been added to the to record the Customer's original state - only the previous history is stored.

Customer_Key	Customer_Code	Customer_Name	Original_Customer_State	Effective_Date	Current_Customer_State
1	ABC	Oracle	CA	22-Dec-2009	NJ

Cons: This record contains a column for the original state and current state—cannot track the changes if the Customer relocates a second time. One variation of this is to create the field Previous_Customer_State instead of Original_Customer_State which would track only the most recent historical change.

Type IV

The Type 4 method is usually referred to as using "history tables", where one table keeps the current data, and an additional table is used to keep a record of some or all changes.

For the above example the original table name is Customer and the history table is Customer_History.

Customer
Customer_key	Customer_Code	Customer_Name	Customer_State
1	ABC	Oracle	NJ

Customer_History
Customer_key	Customer_Code	Customer_Name	Customer_State	Create_Date
1	ABC	Oracle	CA	22-Dec-2009

Part III here ...

Part I here...