Summary Chapter 7: Modeling tips and tricks 7. Introduction 7.10. Converting many-to-many relationships

7.10. Converting many-to-many relationships

Relationships provide a good way to accelerate the modeling through the column propagation mechanism as detailed on section Relationships on chapter 4. But despite being of great utility, relationships have some limitations that can cause delay on the design in very specific cases, one of them is related to the many-to-many relationships. Since this kind of link implicitly creates an intermediate table between the two entities that compose it there is no possibility to do complex operations over the generated table like add triggers, indexes, rules or even create other relationships involving it.

Fortunately, in order to workaround this situation pgModeler implements a feature that converts the many-to-many relationship into two new one-to-many relationships that connect the intermediate table to the others that composed the converted relationship. To give a detailed explanation, observe the following image.

The sample image above shows a many-to-many relationship in its original form connecting the table_a and table_b, also the relationship contains two attributes called attrib1 and attrib2 which are part of the intermediate table named table_c. In order to proceed with the conversion the user must right click the relationship and select the Convert action. Once done that the result will be similar to the next image.

As you can see two new relationships were created: one that connects the table_a to table_c and another that connects table_b to table_c. This approach keeps untouched the semantics of the many-to-many relationship but in the other hand gives the user the chance to handle the table_c in all possible ways. Also, note that the relationship attributes attrib1 and attrib2 are now explicitly part of the table_c, in the same way you are free to manipulate these objects.

Finally, check on the above image that both relationships were created in a form of identifier relationships this because in the many-to-many the primary key of the intermediate table is composed by the foreign key columns which, in the new arrange, can be achieved by the use of identifier relationships. Again, you have the freedom to change even the new relationships configurations as you wish.


Sep 12, 2018 at 11:24