scd type 2 in bridge tables

View previous topic View next topic Go down

scd type 2 in bridge tables

Post  sgudavalli on Tue May 22, 2012 3:05 am

Hi,

I am having 2 different dimension tables in my design i.e.. policy & claims...
now i am creating a bridge (as it is many to many) to hold the associations of each claim to its corresponding policy...
i want to track all the historical changes that do happen in these 3 tables..

to do so; i added surrogates to policy and claim table.. with startdate; enddate and currentflag which helps to track down the history.
now i m stuck up with bridges... my question is do i really need to version my association if there is an update on either of these 2 dimensions..

i m thinking of following design options on my bridge..

[option1]:

table: policyclaimversionId | policynumber | claimnumber | startdate | enddate | currentflag
(Or)
table: policyclaimversionId | policyversionid | claimversionId | startdate | enddate | currentflag

PK: policyclaimversionId

in this option; i will not create a record unless the association is changed in the bridge..

[option2]:

table: policyversion | claimversionId | startdate | enddate | currentflag
PK: policyversion | claimversionId

in this option; i will reate a record in the bridge even if there is an update on the policy or the claim

i m actually creating a cube out of the above design.. plz suggest.....

Regards
Shiv





sgudavalli

Posts : 29
Join date : 2010-06-10
Age : 33
Location : Pune, India

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum