Relationships in model building for beginners

3 min read

For a data visualization tool like Power BI, there are 3 types of relationships when building models. One to one relationship, one to many relationship and many to many relationship.

One to many relationship is commonly used in creating relationships between tables. A one to many relationship simply means that for a unique value in a table, that same unique value has different values to it in another table. Now using the dimension and facts table below

Table 1 - Sales Table

Table 2 - Product Table

The column with sales ID in the first table are unique values and those unique values now appear to have different values to it in table 2, this means that sales agent with the ID 112 sold 3 different types of product (many), sales agent with the ID 114 sold 4 types of product.

One to many relationship helps the tables communicate with each other without any obstruction. It can also be called a many to one relationship depending on the table you are looking at first. For example, if you are looking at the product table first i.e the table with different values and the sales table second then that is a many to one relationship.

One to one relationship which is not a wrong relationship but is rarely used. Overtime, I have read and understood that it's best to join tables in this case using the append query.

Many to many relationship happens when multiple records in a table are associated with multiple records in another table. However, Many-to-many relationships are usually not allowed in a relational database because of the problems they create.