Cardinality Issue | M:M Relationship

In my previous post The Dimensional Model Plan, I described a client in Michigan and how they were asking questions on where to begin dimensional model. As I continue to work with them identifying measures and attributes, grain, and data types, they had another modeling question regarding the entity dimProject. While discussing dimProject, we agreed it should have the following keys and attributes.

The Columns are the following:

While we talked about the practice is their organizational light, it was revealed that there could be multiple Practice Managers and Practice Leaders. An obvious, but unrealistic solution is to have a primary and secondary Practice Manager and Practice Leader. We would model this solution as follows:

The obvious issue with this model is that what happens if we have a third, or fourth Practice Manager, or Practice Lead? Do we modify dimension every time a new number of managers, or leads increases such as ThridPracticeManagerName? Since we all agreed that the business could have as many Practice Managers and Practice Leads as they decided, we quickly identified a many-to-many (M:M) relationship between the fact table and dimPractice for both the Practice Manager Name and the Practice Lead Name attributes.

It is important for us to keep in mind that a star schema always has a M:1 relationship between a fact table and a dimension table. Since our example above is actually a M:M relationship between a fact table and a dimension table, we have a cardinality issue (M:1 relationships only).

It was suggested that we could “Snowflake” dimPractice to solve the cardinality issue. However, when we model the “Snowflake,” we realize that doesn’t work because the model results in a 1-to-many (Practice Lead / Practice Manager to Practice) relationship.

I suggested we really needed a many-to-many relationship between Practice Lead /Practice Manager and Practice. To accomplish using a many to many relationship without violating the cardinality, I decided to model using a “Bridge” table.

Having a bridge table does complicate things a bit in the semantic layer (such as SSAS tabular / multi-dimensional) but it’s still probably better (certainly more flexible) than the Primary / Secondary Option above.

A final option would be to “Snowflake” the Primary / Secondary Option above. This option is just in case we have any fact tables related directly to a Practice Lead, or a Practice Manager (as opposed to having to go directly through Practice).

I hope this blog post helps visualize how using a bridge table resolves the cardinality issue created by a many to many relationship between the fact table and dimPractice.