v1.1.0 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 in section Relationships in chapter 4. But despite being of great utility, relationships have some limitations that can cause a delay in the design in very specific cases, one of them being 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, to work around 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 compose 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. To proceed with the conversion, the user must right-click the relationship and select the Convert action. Once done 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 on the other hand, it 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, note that in the above image, both relationships were created in the form of identifier relationships this is because in the many-to-many the primary key of the intermediate table is composed of the foreign key columns which, in the new arrangement, can be achieved by the use of identifier relationships. Again, you have the freedom to change even the new relationship configurations as you wish.

Mar 25, 2024 at 09:14