data-modelling
4 items tagged with "data-modelling"
Diagrams
Slowly Changing Dimension (SCD) Type 4
This diagram shows how a slowly changing dimension type 4 table is implemented. customernumber is the business key of the customer table while customerid is a surrogate key. Customer 10001 is changing first_name from Kontext to Context. SCD Type 4 uses a history table to track the historical changes. This method is similar as database change capture or auditing table implementations.
Slowly Changing Dimension (SCD) Type 3
This diagram shows how a slowly changing dimension type 3 table is implemented. customernumber is the business key of the customer table while customerid is a surrogate key. Customer 10001 is changing first_name from Kontext to Context. SCD Type 3 will add a new attribute to keep the current value. The drawback is that it can only keep previous and current values only. For SCD Type 3, surrogate ID is not necessary.
Slowly Changing Dimension (SCD) Type 2
This diagram shows how a slowly changing dimension type 2 table is implemented. customernumber is the business key of the customer table while customerid is a surrogate key. Customer 10001 is changing first_name from Kontext to Context. SCD Type 2 will track history of changes. It is usually implemented to use effectivedate to indicate the start date when the change becomes effective and iscurrent flag to indicate the current active records. It can also be implemented using effectivefromdate and effectivetodate. When effective\to\date is NULL or equals to a high date like (9999-12-31), the record is the latest version. For performance and other considerations, it can also be implemented with a combination of effectivefromdate, effectivetodate and is_current.
Slowly Changing Dimension (SCD) Type 1
This diagram shows how a slowly changing dimension type 1 table is implemented. SCD Type 1 will simply overwrite old data with new data without keeping a history. customernumber is the business key of the customer table while customerid is a surrogate key. Customer 10001 is changing first_name from Kontext to Context. For SCD Type 1, surrogate ID is not necessary.