April 7, 2010 at 5:14 am
I'm developing a healthcare claims data mart (Kimball dimensional modeling). The diagnoses in the claims data are being implemented as a multivalued dimension, which consequently requires a bridge table between FactClaims and DimDiagnosis.
The bridge table contains two essential fields: DiagBridgeKey and DimDiagKey. The field DiagBridgeKey joins FactClaims and the bridge table, and DimDiagKey joins the bridge table to DimDiagnosis.
The difficulty I'm having is implementing constraints. SQL Server 2005 is allowing me to define a FK constraint between the bridge table and DimDiagnosis since DimDiagKey is the primary key in DimDiagnosis. But I can't get a FK constraint between FactClaims and the bridge table.
Ultimately, I want to make sure that ClaimsFact has key integrity between ClaimsFact and the bridge table, and that the bridge table has key integrity between it and DimDiagnosis. Can this be achieved?
Thanks in advance,
Pete
April 7, 2010 at 3:13 pm
Is it correct to say that the DiagBridgeKey is also in the FactClaims table?
My assumption is that in order to maintain the correct cardinality DiagBridgeKey is the PK on the bridge and therefore is also in the fact. But, if you say you can't create a foreign key from the fact to the bridge, then I'm missing something.
If your tables are set up differently, can you describe tables and their relationships in more detail?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply