Polymorphic bonds for the smallest
Recently, doing another functional on one of the projects, I came across a bit unusual connections in relational DBMS, which, as it turned out later, have an intricate name - Polymorphic connections. What it is, how and where to apply them, I will try to explain in this article.
The topic of polymorphic links has already 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 experience in designing a database. But for beginners (I was), little is clear from those articles, so in this article I will try to tell everything on my fingers, abstracting from the language, except that I’ll touch ORM of popular frameworks on the web a little.
Everyone understands the usual "relationship" of tablets in relational databases: one-to-one, one-to-many, many-to-many. And if they are not clear, here are some simple examples of their implementation.
One to one . One record from the first table corresponds to only one record from the second table. It's simple. The most common example is the user and user_profile table (for each user, one profile corresponds).
')
One to many . Relationship is built in such a way that several records from another table can correspond to each record in one table. An example is the articles table (articles), the comments table (comments). There can be a lot of comments on one article.
Many-to-many . Communication is realized when one row from one table can correspond to several records from another and vice versa. A good example is there is a table of articles (articles), there is a table of tags (tags), they are connected through an intermediate table (pivot table or junction table) tags_articles, in which there is an article_id, tag_id.
It seems that everything is simple and clear.
Previous relationships (one-to-one, one-to-many, many-to-many) are created for static entities from tables that can be constrained by the DBMS.
Let's return to the one-to-many example.
articles:
comments:
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 the 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, two options appear:
1) Create a new comments table (for example, comments_news) with an identical structure, like the comments table, but instead of article_id, put news_id.
2) Add another news_id column next to article_id to the existing comments table.
In both cases, it turns out somehow clumsy. If tomorrow we need to add comments to another third table (for example, to posts of users or to pictures), do we have to create another table or a third field in an existing table? Fifth-tenth? Not that ... Polymorphic links come to the rescue.
Polymorphic relationships are dynamic relationships between tables using an entity type.
To make it clear, let's change our tables a bit and make polymorphic links between them.
Our another table is news:
And we change the comments table to make it smooth!
comments:
The essence of polymorphic links becomes clear, when viewing the comments table — entity_id — id of some entity to which we leave a comment, entity_type is the type of this entity. Neither entity_id nor entity_type are unknown in advance, so these relationships can be called dynamic.
It is worthwhile to use polymorphic links when we have two or more tables that will 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 connections between 2 tables and are no longer envisaged, it is better to replace polymorphic ones with ordinary one-to-many.
Polymorphic bonds can be realized, and as many-to-many.
Show the table with the data does not make sense, 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)
Not everything is as perfect as it might seem at first glance. Due to the dynamic nature of polymorphic relationships, between the fields of the tables being linked, it is impossible to affix foreign key relationships using a DBMS, and even more so constraints on changing or deleting records. This is actually the biggest minus of polymorphic links. You have to either write your own triggers (procedures or whatever) for the DBMS itself, or, more often, do shift the work of synchronizing strings and imposing restrictions between tables on the ORM and programming language.
The second, less significant, minus of polymorphic links is in the type of entity. It is necessary to somehow describe what type, which table belongs to. This may not be obvious if, for example, the name of a table has changed or if you specified the type of an entity in numbers. You can solve this problem, for example, by creating a separate table, or by writing in the project code an associative array with a type and entity mapping.
It should be said that modern frameworks and their ORM, without too much difficulty, are able to work with these links.
For example, as mentioned above, Ruby on Rails supports them out of the box. The Laravel php framework, in its ORM implementation, also has convenient methods for these types of connections (morphTo, morphMany, etc.), and as the entity type uses the full name of the model class. In the framework of Yii2, there are no specific methods out of the box for such kind of links, but they can be implemented through the usual hasOne, hasMany methods with additional conditions when prescribing links.
From the above, beginners should pay attention to when to use polymorphic links. You should not push them right and left, from project to project, just because it's cool. It is necessary to estimate a little in advance, and whether new tables will appear tomorrow, new entities with the same functionality and requirements that could be rendered and made dynamic, and proceeding from the answer to design their own databases.
The topic of polymorphic links has already 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 experience in designing a database. But for beginners (I was), little is clear from those articles, so in this article I will try to tell everything on my fingers, abstracting from the language, except that I’ll touch ORM of popular frameworks on the web a little.
Everyone understands the usual "relationship" of tablets in relational databases: one-to-one, one-to-many, many-to-many. And if they are not clear, here are some simple examples of their implementation.
One to one . One record from the first table corresponds to only one record from the second table. It's simple. The most common example is the user and user_profile table (for each user, one profile corresponds).
')
One to many . Relationship is built in such a way that several records from another table can correspond to each record in one table. An example is the articles table (articles), the comments table (comments). There can be a lot of comments on one article.
Many-to-many . Communication is realized when one row from one table can correspond to several records from another and vice versa. A good example is there is a table of articles (articles), there is a table of tags (tags), they are connected through an intermediate table (pivot table or junction table) tags_articles, in which there is an article_id, tag_id.
It seems that everything is simple and clear.
Where did any polymorphic links come from, if the previous links are already quite logical and do not seem to require additions?
Previous relationships (one-to-one, one-to-many, many-to-many) are created for static entities from tables that can be constrained by the DBMS.
Let's return to the one-to-many example.
+ -------------- + | articles | | comments | + -------------- +
articles:
+ ---- + -------------------------------------------- ------------ + ------------ + | id | text | date | + ---- + -------------------------------------------- ------------ + ------------ + | 1 | Text cool article | 2015-07-05 | | 2 | The text of another cool article | 2015-07-05 | + ---- + -------------------------------------------- ------------ + ------------ +
comments:
+ ---- + -------------------------------------------- -------------------- + ------------ + ------------ + | id | text | article_id | created_at | + ---- + -------------------------------------------- --------------------------------- + ------------ + | 1 | Good comments | 1 | 2015-07-05 | | 2 | Good comments | 1 | 2015-07-05 | | 3 | Good comments | 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 the 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, two options appear:
1) Create a new comments table (for example, comments_news) with an identical structure, like the comments table, but instead of article_id, put news_id.
2) Add another news_id column next to article_id to the existing comments table.
In both cases, it turns out somehow clumsy. If tomorrow we need to add comments to another third table (for example, to posts of users or to pictures), do we have to create another table or a third field in an existing table? Fifth-tenth? Not that ... Polymorphic links come to the rescue.
The essence of polymorphic bonds
Polymorphic relationships are dynamic relationships between tables using an entity type.
To make it clear, let's change our tables a bit and make polymorphic links between them.
Our another table is news:
+ ---- + -------------------------------- + ----------- - + | id | text | date | + ---- + -------------------------------- + ----------- - + | 1 | Some news | 2015-07-05 | + ---- + -------------------------------- + ----------- - +
And we change the comments table to make it smooth!
comments:
+ ---- + -------------------------------------------- -------- + ----------- + ------------- + ------------ + | id | text | entity_id | entity_type | created_at | + ---- + -------------------------------------------- -------- + ----------- + ------------- + ------------ + | 1 | Good comments | 1 | article | 2015-07-05 | | 2 | Good comments | 1 | article | 2015-07-05 | | 3 | Good comments | 2 | article | 2015-07-05 | | 4 | Comment | 1 | news | 2015-07-05 | + ---- + -------------------------------------------- -------- + ----------- + ------------- + ------------ +
The essence of polymorphic links becomes clear, when viewing the comments table — entity_id — id of some entity to which we leave a comment, entity_type is the type of this entity. Neither entity_id nor entity_type are unknown in advance, so these relationships can be called dynamic.
It is worthwhile to use polymorphic links when we have two or more tables that will 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 connections between 2 tables and are no longer envisaged, it is better to replace polymorphic ones with ordinary one-to-many.
Polymorphic bonds can be realized, and as many-to-many.
Show the table with the data does not make sense, 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 links
Not everything is as perfect as it might seem at first glance. Due to the dynamic nature of polymorphic relationships, between the fields of the tables being linked, it is impossible to affix foreign key relationships using a DBMS, and even more so constraints on changing or deleting records. This is actually the biggest minus of polymorphic links. You have to either write your own triggers (procedures or whatever) for the DBMS itself, or, more often, do shift the work of synchronizing strings and imposing restrictions between tables on the ORM and programming language.
The second, less significant, minus of polymorphic links is in the type of entity. It is necessary to somehow describe what type, which table belongs to. This may not be obvious if, for example, the name of a table has changed or if you specified the type of an entity in numbers. You can solve this problem, 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 links
It should be said that modern frameworks and their ORM, without too much difficulty, are able to work with these links.
For example, as mentioned above, Ruby on Rails supports them out of the box. The Laravel php framework, in its ORM implementation, also has convenient methods for these types of connections (morphTo, morphMany, etc.), and as the entity type uses the full name of the model class. In the framework of Yii2, there are no specific methods out of the box for such kind of links, but they can be implemented through the usual hasOne, hasMany methods with additional conditions when prescribing links.
From the above, beginners should pay attention to when to use polymorphic links. You should not push them right and left, from project to project, just because it's cool. It is necessary to estimate a little in advance, and whether new tables will appear tomorrow, new entities with the same functionality and requirements that could be rendered and made dynamic, and proceeding from the answer to design their own databases.
Source: https://habr.com/ru/post/261835/
All Articles