Conceptual Many-to-Many Relationships

It bothered me for a long time there was no data modeling tool to represent, in the conceptual view of a relational database model, many-to-many relationships with just a straight connector between the two tables and nothing more. With no required intersection table, as long as this was a physical level implementation detail.

Traditional Model, with All Shapes Expanded

Take the Indian Reserves database model (which I built on purpose, with lots of many-to-many relationships, to illustrate the concept). The diagrams below are equivalent representations of the same tables and relationships between them. I chose on purpose just many-to-many relationships, to show why Model Xtractor simplifies their representation and helps understanding.

I should start with an initial view that most data modeling tools offer as a unique view: with all these tables expanded. But I would spare you the details of a huge diagram with lots of details, hard to understand.

Traditional Model, with All Shapes Collapsed

Let’s rather start with the diagram below, in which all our selected table shapes are collapsed. This is also a classical ER diagram you may see in many data modeling tools, using the crow’s foot notation: plenty of intermediate intersection tables that make the actual conceptual relationships hard to understand. Multiple the number of these tables by 10 or (much) more, because this is what we usually get in real life enterprise systems. If those intersection tables contain, as both foreign and primary keys, just propagations of the PKs in the related tables, why should this matter to us? This is an implementation detail, a required constraint of the relational model to allow such relationships work.

Simplified Model

Show Simplified is a nice feature of Model Xtractor, when you do not need glitz and blitz and just want to focus on content:

Minified Shapes

Having collapsed shapes is not enough. There are still too many “utility” tables to understand the main conceptual connections. Model Xtractor introduces the minified shapes. One minified shape appears as a big bullet or a connection point, with no shape names, suggesting they are irrelevant in this representation:

Many-to-Many Generated Chains

Finally, take another even more optimized alternative representation from Model Xtractor: for each many-to-many relationship, we also transparently generated, as artifact, a “virtual” many-to-many chain relationship between the two main tables. And we simply removed all intersection tables from this diagram, we do not need them anymore:

Graph Model

Or simplified, in a Graph model type, when we get rid of most glitz and blitz and focus on connectivity through relationships only:

Conclusion

Hard to believe all these representations contain the same main tables and are meant to illustrate many-to-many relationships between tables. This last conceptual diagram tells us better only what we need to know, from the business perspective:

1. One province can have zero or more bands and zero or more reserves.
2. A band or a reserve can spread in one or more provinces.
3. Reserves and bands can have one or more offices.
4. One office can serve one or more bands and reserves.
5. One reserve can hold one/more bands, and one band can spread in one/more reserves.
6. One user role can have one/more privileges, and one privilege can be in zero/more roles.