Too many Bridge Tables...?

I'm working on a health care warehouse, and we have a Provider dimension. A provider is basically a doctor or a clinic or a hospital or such.

I'm running into a lot of attributes that are a) Many to One with provider b) time sensitive (i.e. most have an effective start and end date) and c) feel like provider attributes (rather than dimensions on their own). Some of them may be degenerate dimensions, but the many to one still applies. Examples include:

• Certification (Pediatrics, OBGYN, Neurology)
  
• Language (English, Spanish, ...)
  
• Alternate IDs (Medicare Provider ID, Medicaid Provider ID, Tax ID, State specific practitioner IDs)
  

The way I'm modeling this now is by building a separate bridge table for each attribute with four fields per table: the provider surrogate key, the degenerate value, and the effective start and end dates.

This feels kind of over-blown, but I can't think of a better way. I'm open to suggestions.

You could probably just enumerate the alternate ID's as attributes in the provider dimension. But, the others are what they are. Although I would probably make certification a full dimension to house attributes to cover different levels of certification and certification bodies.

Can you give an example of your fact table? I suppose if relevant you could come up with a primary language concept if that "attribute" is not all that important in your fact. For example, does it matter what languages the doctor speaks for a surgery fact?

Tim Webber wrote:Can you give an example of your fact table? I suppose if relevant you could come up with a primary language concept if that "attribute" is not all that important in your fact. For example, does it matter what languages the doctor speaks for a surgery fact?

It doesn't matter. The fact will have a FK to a physician. If a bridge is implemented, it would carry the same physician FK. It is independent of the fact. The bridge can be created at any time, and any fact that references a physician can use it. It may make sense to include primary language as an attribute of the physician, but again, it has nothing to do with the fact table itself.

I agree that any fact where provider is relevant will have a FK to provider. However, I also believe that whether or not you can simplify (...find a better way...) the relationship of provider to language or certification depends on how you need to use those attributes/dimensions to describe your facts. If primary language is enough, it can be an attribute of provider. If there are only 3 relevant languages, you could flatten language out and put three flags (one for each language) in provider.

This approach may not be a "better" way but its an option and i believe adopting it will depend on a further analysis on the requirements for reporting related facts.

Flattening is always an option, as I mentioned with the alternate identifiers, but it only works well when the role is clearly identified. When you are dealing with multiple choices that are peers, flattening becomes a problem. For example, if you had 3 language columns and I wanted physicians who speak French, which column do I examine? (Answer: All 3) Queries become very tedious.

Consider the provider table with a primary key, a bunch of columns and three language columns. Remember, 3 is absolutely arbitrary and only serves as an example of "options". The point is that flattening is an option. What I dont understand is why you think you need to examine three columns? If the business decides there are only three relevant languages, then there will be three flags -> one for each of the languages.

provider_pk, col1....coln-3, english_yn, french_yn, spanish_yn

Tim Webber wrote:If the business decides there are only three relevant languages

That is a HUGE assumption. An yes, you can put all kinds of restrictions to the amount of information you are going to keep to make it fit a 'flattening makes sense' scenario.

But, getting back to reality, in a health care environment, EVERY language is important. And there are a lot of them. Not 3, not 5, not 20. Flattening language in this particular application in this particular industry makes no sense. A bridge table is the correct approach.

ngalemmo wrote:That is a HUGE assumption

Lol, remember we are investigating options for the original poster. Not my assumption. Just a necessary artefact of simplification in order to explore these options. If you have lots of experience with health care business processes and understand the OP's requirements you could have stated "A bridge table is the correct approach." from get go. Remember, you stated that this decision has nothing to do with the Facts. But in your post you are falling back on your experience in health care (this is an assumption!) to inform us that all languages are necessary to contextualize these facts. That may be the case.