Loading Bridge Table

I started my work in the DW world about 9 years ago, but haven't done much for the past 3 years. I am starting to work on DW projects again and have a multivalued dimension situation where I require the use of a bridge table. I am new to bridge tables, but understand the concept. I have the tables modeled, but am struggling with a process to load the bridge table. I have taken a look at previous posts on here, but am still a little lost.

Source Data
Patient_Id Diag_Codes
1 '1 2 3'
2 '9 11'

Bridge Table
SKey DiagGroupKey DiagKey
1 1 1
2 1 2
3 1 3
4 2 4
4 2 5

Diag_Dim
SKey DiagKey Description
1 1 Cold
2 2 Flu
3 3 Fever
4 4 Broken hip
5 5 Broken ankle

So my question is when I get a new record coming in from the source, how do I manage the bridge table? What is the recommended practice to check to see if the group already exists or if I need to add it to the bridge table? I was thinking of adding another table in between the source data and the bridge which would store the diagnosis codes concatenated together. To me that seems to be the easiest route, otherwise you have to go through some complex logic to manage the table. So my intermediate table would be something like this.

Groups
DiagGroupKey DiagCodes
1 '1 2 3'
2 '9 11'

Then when I load a new record from source I can check this table to see if my grouping exists. If it does, then I grab the key from this table, otherwise I add to it and then add to the bridge table in the next step.

Does this method work? Is there an easier way?

Any help is appreciated.

Hi,

I think I have the same problem, expressed as clearly as I can in this post:

"How to lookup a small group of records as one"

I'm not calling it a bridging table though, as it's all on the loading/staging side, and hopefully it will be maintained by a data steward.

I wonder if creating scalar functions in SQL server to concatenate, sort and de-duplicate the string of id's will enable a join to occur in an SSIS lookup step?

Al Wood

The group table you describe is the way to go. Particularly with diagnosis as, you will find, the group table and bridge will eventually stabilize over time.

Not sure why you have a surrogate PK in the bridge, as it has no purpose. The group key and diagnosis key is all you need.