1. Problematic Data Relationships
In this video, we’re going to explore Many-to-Many Relationships, what they are, why they can be tricky, and how to resolve them with a bridge table. We’ll also cover Circular Relationships and understand why they should be avoided,and wrap up with a quick refresher on Star vs Snowflake Schema.
2. Understanding Many-to-Many Relationships
Let’s start by understanding what Many-to-Many Relationships are. This happens when multiple records in one table link to multiple records in another. For example, if we have a Customers table and a Products table, one customer might buy several products, and each product could be bought by many customers.
Without a way to handle this relationship, it can get messy, totals might become inaccurate, and it’s hard to track who bought what. This creates confusion in your data model, which is why resolving this issue is so important.
3. How to Resolve Many-to-Many Relationships
So, how do we solve this? The answer is a Bridge Table. This special table acts as a connector, turning the many-to-many relationship into a more manageable one-to-many setup.
In our example, let’s say John Doe might buy both a Laptop and a Smartphone, and those products could also be bought by other customers. Without a bridge, this creates a confusing many-to-many relationship.
By introducing a Bridge Table, we link the CustomerID from the Customers table to the ProductID in the Products table through the bridge. This transforms the many-to-many connection into two clear one-to-many relationships. Now, we can track exactly which customers bought which products without any confusion.
4. Understanding Circular Relationships
Now, let’s move on to another type of relationship that can be problematic, Circular Relationships. These occur when tables are connected in a loop, like A to B, B to C, and then C back to A, creating endless cycles.
This creates confusion because the data keeps circling back, and filters don’t know where to stop. For example, if you filter Products through Sales and Customers, but end up back at Products, the model gets stuck and gives wrong results.
To solve this, break the loop by adjusting relationships, or use DAX measures to control filtering. This clears the confusion and ensures your data flows smoothly without errors.
5. Star vs Snowflake: What is the Difference?
As we wrap things up, let’s quickly refresh our understanding of Star and Snowflake Schemas to bring everything together!
In the Star Schema, the structure is simple and denormalized. A central fact table, like Sales or Orders, is surrounded by fewer dimension tables like Products or Customer, which makes queries faster and easier. this is perfect for smaller or medium-sized datasets where performance matters most.
In contrast, the Snowflake Schema is more complex, with normalized dimensions broken into sub-tables. This helps reduce redundancy but adds complexity, slowing down queries due to more joins. It’s best suited for large, datasets needing efficient data storage.
So, for fast performance, stick with Star. For more complex scenarios, Snowflake is your solution!
6. Let's practice!
Let's now implement these concepts practically!