What if the number of many-to-many relationship tables starts to exceed all reasonable limits?

Question

MySQL

There are tables that store individual entities (users, posts, files, comments ...) in the amount of N > 20. And there are tables that store relationships between pairs of different entities (users_contacts, users_files_downloads, favorite_posts, ...) in the amount of ~ N*(N+1)/2. The number of tables of the second type grows much faster. For example, if you add a new entity, you will have to create ~ (N+1) link tables.
In general, it’s impossible to live like this, so I implemented the following solution:
1. We refuse numerous tables of links and instead of them we introduce two new tables:

CREATE TABLE IF NOT EXISTS `entities` (
  `id_entity` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор сущности',
  `type` int(11) NOT NULL COMMENT 'Тип сущности',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Дата и время создания',
  PRIMARY KEY (`id_entity`)
) ENGINE=InnoDB COMMENT='Сущности';

CREATE TABLE IF NOT EXISTS `entities_relations` (
  `id_entity` INT unsigned NOT NULL COMMENT 'Идентификатор сущности',
  `id_entity_related` INT unsigned NOT NULL COMMENT 'Идентификатор связанной сущности',
  `type` TINYINT NOT NULL COMMENT 'Тип связи',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Дата создания связи',
  PRIMARY KEY (`id_entity`,`id_entity_related`)
) ENGINE=InnoDB COMMENT='Связи сущностей';

2. Entity tables (users, posts, files, comments ...) must be linked to the `entities` table.
Pros:
1. Now, if some new entity appears, it is enough to create just one table for it.
2. Fields common to all entities can be moved to the `entities` table. Now, for example, with a simple query, you can get the identifiers of all created entities (of different types) for a certain period of time. This is convenient for news feeds, which includes information about various added objects, and not just the same type.
3. Referential integrity is preserved.
Cons:
1. Database queries involving more than one entity have become more complicated, since they will now be joined through the `entities_relations` table.
2. Adding now requires two queries: first we add a row to the `entities` table, we get the auto-incremented value of `id_entity`, and with it we add the entity data to the main table (users, posts, comments, ...).
What do you think or where can I read about the practice of using this approach? How can it be improved?
What are the alternatives? Perhaps the built-in MySQL tools can better solve this problem?

Answer 1

Where can I read about the practice of using this approach?

Building One-to-Different Tables

Answer 2

use a one-to-many relationship

Answer 3

Why bother at all? Well, a lot of tables, so what? They are all necessary, all correct and well-tuned. Queries on them are simple and fast, indexes on them are beautiful and correct, complex selections are convenient and understandable to build.
Why twitch?
There are so many parts in the car, you don’t want to reduce their number.

Answer 4

You can build a hierarchy of entities, for example: message<-posts; message<-comments and bind to the underlying UserMessage type.
In this case, you can store post and comments in one wide, but sparse table (some of the fields are used to store posts data, a part for comments, not a normalized form of db) or sacrifice performance due to unnecessary table joins (message join posts).