Can someone help me in knowing how can we store history in supertype , subtypes .

Can someone help me in knowing how can we store history in supertype , subtypes .


I have supertype with product num as unique identifier and subtype with product num as unique identifier ofcourse with other attributes that are different from supertype . I want to implement scd2 if any column changes in supertype or subtype .Can someone guide me on this ?

By definition, the primary key for a supertype is also the primary key for it's subtypes. It would seem to me, the only way you can ensure sanity amongst users is to keep the keys in sync and create rows in the supertype and applicable subtypes when a change occurs anywhere for that member. Think of it as one row broken up into pieces.

Lets say i make product ID and row eff date as primary in supertype and subtype as well i think i will be able to correlate split up records .

That is not how type 2 works.

What you are creating is a table with a compound primary key... a type 1 key and a timestamp.

Using compound keys between facts and dimensions is not considered best practice.

Thanks for replying , the only way to solve this is than make product id and row eff as unique keys and generate a surrogate key in supertype and propagate this in subtype as foriegn key . Similarly subtype will have system generated surrogate key as primary key whereas product id and row eff date as unique keys.

Can u please give me an example of why composite t key in fact table will not be a good idea .

I may be wrong in what i came up with ,please advise if there is better way to implement this .

Combine your super and sub type tables into one table. There is no reason to model this way in a dimensional model.

Super/Sub types are a physical implementation of what is logically a single row. In a dimensional model, you either implement it as a single table, as B&L suggests, or as separate tables as you suggest.

If you implement separate tables in a dimensional model, you implement each table as separate dimensions with coordinated primary keys. For type 2, that means a new row in one table results in new rows in the other related tables with the same key.

A third option is to carry FK's to the super type and each subtype in the fact (which basically means you don't implement super/sub types).

There are a lot of other ways to do it, but if you wish to implement a dimensional model (as opposed to a relational model) those are your choices.