Overview of Many-to-Many Relationship Guidance

To understand Many-to Many Relationship you should consider the classic scenario that relates two entities namely, bank customers and bank accounts. Assuming the customers can have multiple accounts, also accounts can have multiple customers. Now, when an account has multiple customers, they are referred as joint account holders.

Moreover, modeling these entities is very simple. The one dimension-type table stores accounts, and another dimension-type table stores customers. Further, the characteristic of dimension-type tables includes an ID column in each table. So, to model the relationship between the two tables, a third table is required. This table is known as a bridging table. 

How to relate many-to-many dimensions guidance?

When you hold a many-to-many relationship between dimension-type tables, then following guidance is applicable –

• Firstly, add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
• Secondly, add a bridging table to store associated entities
• Thirdly, create one-to-many relationships between the three tables
• Then, configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
• Now, since it is inappropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
• Next, hide the bridging table (unless it contains additional columns or measures required for reporting)
• Also, hide any ID columns that aren’t suitable for reporting (for example, when IDs are surrogate keys)
• But, if it required to leave an ID column visible, ensure that it’s on the “one” slide of the relationship. Therefore, always hide the “many” side column. It results in the best filter performance.
• Lastly, to bypass any misinterpretation, communicate explanations to your report users—you can add descriptions with text boxes or visual header tooltips

However, you are not suggested to relate many-to-many dimension-type tables directly. Since this approach to design needs you to configure a relationship with a many-to-many cardinality. Remember, it can be accomplished conceptually yet the related columns will contain duplicate values. It’s a well-accepted design practice, however, that dimension-type tables have an ID column. Dimension-type tables should always use the ID column as the “one” side of a relationship.

Relating Many-to-Many Facts

In this many-to-many scenario type, you will be required to relate two fact-type tables that can be related directly. This Many-to-Many Facts design technique is beneficial for quick and simple data exploration. However, it is not a well-recommended design approach. Since the usefulness of the model is limited as you can only filter or group by the table column.

In general, you are not recommended to relate two fact-type tables directly using many-to-many cardinality. The primary reason is since the model won’t provide flexibility in the ways you report visuals filter or group.  Additionally, this relates to the quality of your data. So, if your data has integrity issues, it’s possible some rows may be omitted during querying due to the nature of the limited relationship. Also, relating fact-type tables directly, we recommend you to adopt Star Schema design principles.

For More Visit: Many-to-many relationship guidance