Dimensions with varying roles

[Kamini]

I am working on a health care claims data warehouse.
We get institutional and professional claims from various sources. Each of these claims will have multiple practitioners associated to them. The roles of practitioners in the claim can vary with the type of claim ( Institutional / Professional ) . In addition to the difference in the roles ,in case of institutional claims there is an option to add 2 “Other Practitioners” where the role is defined by a qualifier code associated to the "other" practitioner’s ID.
I was planning combining both the types of claims in to a single fact table and have role playing practitioner dimension (for all distinct roles from Institutional and Professional claims ). My concern is with the “other” role.
My thoughts are
1. Model all of them as role playing dimensions,but what if new qualifier codes are introduced, my model will not be scalable.
2. Create up to 10 Practitioners Practitioner_1 .. to Practitioner_10 and Practitioner_role1 to Practitioner_role10
Any suggestions .
Thanks
Kamini

[Kamini]

Another option that comes to my mind is to introduce a bridge table between practitioners and the claims fact and have a role attribute in the bridge ? But then I could end up with a huge bridge table ( which will have number of roles times number of facts rows in it) and there will be very little reusability for the Practitioner group bridge records.

[ngalemmo]

Your better option is the bridge, it is much more flexible. As for size, you may very well run into a lot of cases where the same physicians are involved... particularly in these days of medical groups. Doctors usually have other doctors that they prefer to work with so you will most likely see the same team more often than you may expect.

[Kamini]

Thanks for the response ; I was leaning towards that approach too.

[burt84paxton]

Do we have some more optional approaches for it?

[Sponsored content]