Many to many relationship in a dimensional model

Hello everyone,

This is my first post here. I'm looking for an answer to the following question:

I am aware of the bridge table being the technique used to model many to many relationships between a fact and a dimensional table.

I have a fact table called Claims. This is linked go several dimensional tables, one of them being the drug dimension. I, now have to model the drug ingredients. One drug can have many ingredients and many drugs can have the same ingredient.
Will the bridge table be the only way i can model this.

The reporting requirements are:
1) list of ingredients for a drug (this one is fairly simple)
2) list of all drugs which a particular kind of ingredient
3) members who are taking a particular kind of infredient in their medications and in what quantity?

The third requirement is the most imp one. Member is a dimension which is linked to the claims fact table.
So, we can take a particular member, and then get all the drugs for that member. We then need to see, of all the drugs the member is prescribed, how much quantity of a particular drug (say for eg. Acetaminophen) does the member take??

I needed help in knowing the best possible way this can be modeled which is effeicient even in queries for reporting.

Thanks,

A bridge table between drug and ingredient will handle all 3. It is the most efficient way of handling it.

Thanks for the reply @ngalemmo.

Hi @ngalemmo, thanks for the help.

I have a similar problem and for what i have read in the forum, the answer i need is also a bridge table.

I have a fact table with services, a dim table with companies and another dim table with coverages that the companies have. One company may have many coverages and, on the other hand, a coverage can be held by many companies.

The additional problem is that my fact table also has other information and i cannot base the numbers on the info contained in the bridge. That is, i can have services that may not need a coverage. If the connection is made solely to the bridge table (without connecting the company to the fact table), i will only have info on my fact table about services that required coverages.

So, the bridge table is naturally the answer.

But, How should i connect the tables to the fact table?


Thanks a lot in advance for all the help,
Marta

Hi - it depends on the grain of your fact table and the relationship between a fact record and a dimension record.

If a service fact record can be related to only 0 or 1 Companies then you join the fact record and the Company Dim directly (your Company Dim will contain 'dummy' records to allow for the unknown/not applicable situation where the service fact is not related to a company)
The same logic applies to service fact/coverage Dim relationship.

Bridge tables are normally used to resolve many-to-many relationships between Dimensions. So you would have your bridge table between your company and coverage tables; the bridge table does not join directly to your fact table. But you only need to do this if one of your dimensions does not have a direct relationship to a fact; if your service fact record can only have 0/1 company and only 0/1 Coverage then both join directly to your fact table and you don't need a bridge table.

Hope this helps?

What is it you are trying to model? Services or coverages?

If you have a service event where a coverage applies, then coverage is a dimension of the service event. If multiple coverages apply to the event then you either have multiple rows or one row and a bridge to the coverage… depending on how you can treat the measures.

If you are modeling coverages a customer has, this can be done with a factless fact table, or it may be a byproduct of an invoicing fact...