Polymorphic connections for the smallest

Recently, while making another functional on one of the projects, I came across a little unusual connections in relational DBMSs, which, as it turned out later, have an intricate name - Polymorphic Links. What is it, how and where to apply them, I will try to explain in this article.

The topic of polymorphic links has been raised more than once on Habré ( "Rails and polymorphic links" , "Polymorphic end-to-end associations in Ruby on Rails" , "Polymorphic links" ), but it was raised in the context of Ruby, and for those who already have some kind of experience in database design. For beginners (I had), little is clear from those articles, so in this article I will try to tell everything on my fingers, abstracting from the language, unless I’m a bit offended by the ORM of popular web frameworks.

Everyone understands the usual “relationship” of labels in relational databases: one-to-one, one-to-many, many-to-many. And if you do not understand, then here are simple examples of their implementation.

One-to-one . One record from the first table corresponds to only one record from the second table. Everything is simple here. The most common example is the user and user_profile table (Each user has one profile).

One-to-many . The relationship is constructed in such a way that each record in one table can correspond to several records from another table. An example is the articles table, the comments table. A lot of comments can be left on one article.

Many-to-many. A relationship is realized when one row from one table can correspond to several records from another and vice versa. A good example - there is a table of articles (articles), there is a table of tags (tags), they are linked through an intermediate table (pivot table or junction table) tags_articles, in which there is article_id, tag_id.
It seems that everything is simple and clear.

Where did some polymorphic bonds come from, if the previous ones are quite logical and seem to require no additions?

Previous connections (one-to-one, one-to-many, many-to-many) are created for static entities from tables on which you can place constraints provided by the DBMS.

Back to the one-to-many relationship example.

+ -------------- +
| articles |
| comments |
+ -------------- +


articles:
+ ---- + -------------------------------------------- ------------ + ------------ +
| id | text | date |
+ ---- + -------------------------------------------- ------------ + ------------ +
| 1 | Cool article text | 2015-07-05 |
| 2 | The text of another cool article | 2015-07-05 |
+ ---- + -------------------------------------------- ------------ + ------------ +


comments:
+ ---- + -------------------------------------------- -------------------- + ------------ + ------------ +
| id | text | article_id | created_at |
+ ---- + -------------------------------------------- --------------------------------- + ------------ +
| 1 | Nice comment | 1 | 2015-07-05 |
| 2 | Nice comment | 1 | 2015-07-05 |
| 3 | Nice comment | 2 | 2015-07-05 |
+ ---- + -------------------------------------------- -------------------- + ------------ + ------------ +


In the comments table, article_id is the id of the article from the articles table. Everything is obvious. But! What if tomorrow we have a need to create a table of news (news) and for it, too, you need to add comment functionality ?!

With the types of relationships between tables known to us, there are two options:
1) Create a new comments table (eg comments_news) with the same structure as the comments table, but instead of article_id, put news_id.
2) In the existing comments table add another column news_id next to article_id.

In both cases, it turns out somehow clumsily. If tomorrow it will be necessary to add comment functionality to another third table (for example, to user posts or pictures), will we have to create another table or a third field in the existing table? Fifth to tenth? Not that ... Polymorphic connections come to the rescue .

The essence of polymorphic bonds


Polymorphic relationships are dynamic relationships between tables using an entity type.
To make it clear, we will slightly change our tables and make polymorphic connections between them.

Our other table is news:
+ ---- + -------------------------------- + ----------- - +
| id | text | date |
+ ---- + -------------------------------- + ----------- - +
| 1 | Some news | 2015-07-05 |
+ ---- + -------------------------------- + ----------- - +


And we change the comments table to become exactly!

comments:
+ ---- + -------------------------------------------- -------- + ----------- + ------------- + ------------ +
| id | text | entity_id | entity_type | created_at |
+ ---- + -------------------------------------------- -------- + ----------- + ------------- + ------------ +
| 1 | Nice comment | 1 | article | 2015-07-05 |
| 2 | Nice comment | 1 | article | 2015-07-05 |
| 3 | Nice comment | 2 | article | 2015-07-05 |
| 4 | Comment | 1 | news | 2015-07-05 |
+ ---- + -------------------------------------------- -------- + ----------- + ------------- + ------------ +


The essence of polymorphic relationships becomes clear when viewing the comments table - entity_id - id of some entity to which we leave a comment, entity_type - the type of this entity itself. Neither entity_id nor entity_type are previously known, so these relationships can be called dynamic.

It is worth using polymorphic relationships when we have two or more tables that have a one-to-many relationship with some other one and the same table (articles-comments, news-comments, posts-comments, etc. .). If, however, you only have relationships between 2 tables and are no longer provided for, polymorphic ones are best replaced with the usual one-to-many relationships.

Polymorphic relationships can be implemented, and as many-to-many.
It does not make sense to show tables with data; I will show only an approximate structure.
articles:
id - integer
text - text

posts:
id - integer
text - text

tags:
id - integer
name - string

tags_entities
tag_id - integer
tag_entity_id - integer
tag_entity_type - string (post | article)

Cons of polymorphic bonds


Not everything is as perfect as it might seem at first glance. Due to its dynamic nature of polymorphic relationships, between fields of linked tables, you cannot put foreign key relationships using a DBMS, and even more so, constraints on changing or deleting records. This, in fact, is the biggest minus of polymorphic bonds. You will have to either write your triggers (procedures or something else) for the DBMS itself, or, more often, shift the work of synchronizing rows and imposing restrictions between tables on ORM and a programming language.

The second, less significant minus of polymorphic bonds is the type of entity. It is necessary to somehow describe what type, which table belongs. This may not be obvious if, for example, the name of a table has changed, or if you have set the entity type with numbers. This problem can be solved, for example, by creating a separate table, or by writing in the project code an associative array with a type and entity mapping.

Work ORM with polymorphic bonds


It should be said that modern frameworks and their ORMs without special difficulties are able to work with these relationships.
For example, as mentioned above, Ruby on Rails supports them out of the box. The Laravel php framework also has convenient methods (morphTo, morphMany, etc.) for these types of relationships in its ORM implementation, and uses the full name of the model class as an entity type. In the Yii2 framework, there are no specific methods out of the box for this kind of relationships, but they can be implemented through the usual methods hasOne, hasMany with additional conditions for prescribing relationships.

From all of the above, beginners should pay attention to when to use polymorphic bonds. Do not shove them right and left, from project to project, just because it's cool. We need to figure a little ahead, and whether tomorrow there will be new tables, new entities with the same functionality and requirements that could be made and made dynamic, and based on the answer, design your databases.

Also popular now: