A Data Science Central Community
While having a many-to-many relationship may sound steamy to some of you, when it comes to the data in your tables it can get complicated. That is, unless you understand the different scenarios, their resolutions, and how to build a good relationship with your data. Just as a general overview, a relationship specifies the logic used to combine data from one or more tables. You create relationships by connecting fields between two or more tables, and this determine what data is reflected in a dashboard widget (such as a pivot table). There can be 3 types of relationships:
Definition: You can consider the general rule that when a field from two or more tables contains the same value, and these values are duplicated in both tables, a connection has been made based on this field a many-to-many relationship is created.
We know, it’s confusing, so here’s a business case example: a hotel can have a data table with reservation data and a table with payment data. In both tables the name of the guest is stored. A guest can have multiple reservations under their name, as well as multiple payments for their stay recorded in their name. If a relationship between the reservation and payment table was created based on the guest’s name, a many-to-many relationship would be created (as the guest’s name appears multiple times in each table).
Negative Consequences: The problem with this kind of relationship is that it can create complex data sets which either: do not return the correct results, or use excessive computing resources and don’t return any results. Both scenarios lead to data havoc such as creating duplicates, incorrect results, and performance lags, which is why many people try to avoid many-to-many relationships all together.
Resolutions: There are several methods to resolve or bypass a many-to-many relationship that should be chosen based on the business model and the logic of the business questions at hand. But, the first step is to test to see if you are dealing with a many-to-many relationship (see next section), and then according to the schema logic, apply the best resolution.
You can easily test if a relationship is Many-to-Many by checking the data modeling of the relationship and determining the exact number of unique and duplicate values on each side of the relationship.