Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SCD2 Type Question - Architecture

SCD2 Type Question - Architecture

SCD2 Type Question - Architecture

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 :


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

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

RE: SCD2 Type Question - Architecture

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close