Design Tip #142 - Building Bridges Dilemma with Diagnosis - additional account specific attributes

I have loaded a bridge table with the list of ICD-9 diagnoses groups for each account and have also ordered the list of diagnoses based on a source column called dx_cd_prio (“Priority”) - see Source Data below

FYI:
• The Primary Diagnoses is defined by “DF” (Diagnosis Final) present in the dx_cd_type column with the minimum dx_cd_prio value – hence the second row in the Source Data below would be the Primary Diagnoses which I have no problem “flagging”.
• The Y or N in the dx_cd_type column indicate whether or not the code was “present on admission”

My dilemma is that the business user wants to also be able to see that, for example, dx_cd “600.00” was priority #3 or that “427.31” has a “DFY”. In other words, they want to see the dx_cd_prio and dx_cd_type values. Given the Diagnosis Bridge data below – how can I add \ model these data values? Suppose that this list of diagnoses groups is shared by multiple accounts but that the dx_cd_type values may be different?
I don’t want to go the route of snow-flaking. (Oh and there is not a need for a weighting factor as dollar amounts are not a part of this).

Source diagnosis data:
pt_id schm prio type dx_cd
000100009534 9 01 DA 434.91
000100009534 9 01 DFY 434.91
000100009534 9 02 DFY 401.9
000100009534 9 03 DFY 600.00
000100009534 9 04 DFY 427.31
000100009534 9 05 DFY 424.1
000100009534 9 06 DFN 599.70
000100009534 9 07 DFY 780.09
000100009534 9 08 DFY 787.20
000100009534 9 09 DFY 429.2
000100009534 9 10 DFY 272.4


Diagnosis Group:
pt_id Diagnosis_Code_List
000100009534 434.91 , 434.91 , 401.9 , 600.00 , 427.31 , 424.1 , 599.70 , 780.09 , 787.20 , 429.2 , 272.4


Diagnosis Bridge – (The “Diagnosis_Code_List” would have the Key Value from the Diagnosis Dimension)
GroupKey Diagnosis_Code_List
1 434.91
1 434.91
1 401.9
1 600.00
1 427.31
1 424.1
1 599.70
1 780.09
1 787.20
1 429.2
1 272.4

Create a new fact table at the diagnosis grain.

Thanks for the slap on the head Boxes! I needed that.

Cheers