Confused about many to many

Hello folks,

I am working on a modeling problem involving a many to many relationship for which I would be grateful to receive insights.

The business process i am modeling is the administration of funding applications, each of which has up to five science branches as attributes. The analytical interests are in finding out the average count of science branches listed for the applications, on which applications certain branches co-exist etc.  The data warehouse feeds SSAS cubes as user interface.

So, I guess the standard solution offered for this situation is to create two "helper tables", science branch group and science branch bridge and model the relation as application -> science branch group <- science branch bridge -> science branch dimension

However, someone suggested that you could drop the science branch group -table from the model altogether, instead modeling the data like this:

application <- science branch bridge -> science branch dimension

Are both of these models plausible and are there some pros and cons involved with either of these designs?

Best Regards,

Antti

Both will work.

However, some clarification… Science Branch Group is a junk dimension, not a helper table. It is not actually used in queries, but serves as a means to identify unique combinations of branches. Doing so reduces the size of the bridge table when it can be expected that specific clusters of branches re-occur frequently.

The alternate method is you assign a unique key to each application row and have a bridge entry between the application and the branches. This is easier to load.

The difference between the two approaches is the size of the bridge table and load complexity. Wither that is a problem depends on how many applications you deal with and how often specific clusters of branches repeat.

Thanks for your help ngalemmo, it clarified my thoughts on the subject As the data volumes in our DW are very manageable, I think we will just link the bridge table with the surrogate key of the application, and drop the group table...