Defining many-to-many relationships
Many-to-many relationships appear in a number of modeling situations. Perhaps, the most common scenario and explanation is that of a bank account and their owners. In this situation, a bank account can have many owners who are equally responsible for the balance. However, the owners can have different accounts. The challenge faced in tabular modeling is that the relationship includes a direction that does not actively filter the fact data. The use of the bridge table breaks the standard downstream filtering that is usually applied in a star schema.
This recipe demonstrates how to create the many-to-many relationship by extending the previous recipe to include store ownership. In this example, a reseller can be owned by one or more owners. Furthermore, each owner has an ownership interest (as a percentage interest in a reseller).
The following are the standard types of questions that the model is expected to answer:
How many stores does an owner have?
What...
