Is this the right way to design a MySQL database?

Question

Database design

Hello! I'm slowly bringing the site to a "clean" version, refactoring the code and the database, respectively. Before that, I had separate tables for likes under posts, photos, comments. I considered this approach incorrect, because when adding a new type of resource, you will have to add a new table and sew it into connections, which over time can be many. I came to such a decision


. That is, I created a common table, in this case, for all comments, in the resource_type field I am going to write the type of resource to which the comment is left, for example, image, post, comment etc, and then pull out the necessary one according to two conditions, for example , if I want comments to the photo, then according to the conditions type=photo&id=n.

How correct is this approach, and what are the effective solutions? Thanks in advance

Answer 1

This approach is acceptable and is used in various frameworks. For example, in Django this is Generic Relations . But from a database design perspective, this is an anti-pattern and is not recommended. Because data integrity is violated. You can't assign a foreign key to a resource_id and do, say, a cascading delete. That is, it turns out that resource_id can contain any number that does not correspond to the existing id of any table.
Do it or not, it's up to you. But personally I wouldn't do and keep separate tables.