SCD2 Type Question - Architecture

[vermarajiv]

Guys,
Need your help related to SCD2 type change. I have a table MotorCarrier and we need to keep historical

records. The primary key is a column with Identity values (Primary key col name is MCId). There are multiple

members belonging to a motorcarrier and are maintained in a separate table called Members. Members dont care

about the history of Motorcarrier. For eg MotorCarrier has following records :

Motocarrier

MCId MCName Status StatusChangeDate RecordStatus
1 ABC Active 1/1/2009 Current
2 MM Active 5/1/2009 Current



Member
MemberId MCId MemberName DateJoined
1 2 Mem1 6/1/2009


Now, a new record is created for MotorCarrier MM because its status changed. So we have following records

MCId, MCName, Status, StatusChangeDate RecordStatus
1 ABC Active 1/1/2009 Current
2 MM InActive 5/1/2009 Expired
3 MM Active 12/1/2009 Current


Now the problem is that the Member table still has older Expired record's MCId which is incorrect and should

have value 3. One way to deal with this is that you go to all the tables that points to this MC record and

update it to current MCId. But there are lots of tables with millions of records that refer to MotorCarrier

table. Is there a better way to deal with this situation or a different archi

 

[RiverGuy]

Keep two keys in MotorCarrier:

MCId -- related 1 to 1 with the business key
MCKey -- increments for each row in the dimension table

Then relate your Member table to MCId.