scd type 2 in bridge tables
Page 1 of 1
scd type 2 in bridge tables
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
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 : 40
Location : Pune, India
Similar topics
» ICD-10 and Bridge Tables
» BRIDGE TABLES
» Bridge Tables
» Oh no, not Bridge tables again!!!
» Too many Bridge Tables...?
» BRIDGE TABLES
» Bridge Tables
» Oh no, not Bridge tables again!!!
» Too many Bridge Tables...?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum