What if the number of table relationships many-to-many begins to exceed all reasonable limits?

Have a table where are stored individual entities (users, posts, files, comments ...) in the number N > 20. And there is a table in which are stored the relations between different pairs of 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, will have to get ~ (N+1) tables relationships.

In General it can not go on living, so I implemented the following solution:
1. Refuse from multiple tables, relations and instead introduced two new tables:
CREATE TABLE IF NOT EXISTS `entities` (
 `id_entity` int(11) NOT NULL AUTO_INCREMENT COMMENT 'entity ID',
 `type` int(11) NOT NULL COMMENT 'the entity Type',
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'date and time of creation',
 PRIMARY KEY (`id_entity`)
) ENGINE=InnoDB COMMENT='Entity';

CREATE TABLE IF NOT EXISTS `entities_relations` (
 `id_entity` INT unsigned NOT NULL COMMENT 'entity ID',
 `id_entity_related` INT unsigned NOT NULL COMMENT 'the ID of the related entity',
 `type` TINYINT NOT NULL COMMENT 'Type of link',
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'date of creation connection',
 PRIMARY KEY (`id_entity`,`id_entity_related`)
) ENGINE=InnoDB COMMENT='Context entity';

2. Table of entities (users, posts, files, comments ...) should be associated with the table `entities`.

Pros:
1. Now, if there was some kind of new entity, it is sufficient to have only one table.
2. Fields common to all entities can be put in the table `entities`. Now, for example, a simple query to obtain the IDs of all created entities (of different types) for a certain period of time. This is useful for feeds that gets information about the different added objects and not just the same type.
3. Stored referential integrity.

Cons:
1. Complicated database queries, which involve more than one entity, because jointsa they will now be using the table `entities_relations`.
2. To add will now require two requests: first add a row in table `entities`, autoincrementing get the value of `id_entity`, and have him add the entity data into the main table (users, posts, comments, ...).

What do you think or where you can read about the practice of using this approach? How can it be improved?
What are the alternatives? Perhaps the built-in tools MySQL allows optimal solve this problem?
September 18th 19 at 23:59
4 answers
September 19th 19 at 00:01
Solution
Where can I read about the practice of using this approach?

What is a polymorphic relationship?
Polymorphic when
Polymorphic regard for the little ones

What are the alternatives?

Build tables "One-to-different"
September 19th 19 at 00:03
use relationship one-to-many
How then, for example, to store which user which files are downloaded, the user's contact list, a list of favorite posts of a user? - nella_Mayert0 commented on September 19th 19 at 00:06
and why is the contact list of users you are going to describe many-to-many? no, I understand that in reality the user can be 10 email addresses, and that 10 users can use the same address. but we need not simulate the world, it is necessary to solve the problem. why do I know that Katya and Pasha are living at the same address? sometimes necessary, but.. - Freida.Windler commented on September 19th 19 at 00:09
no, I understand that in reality the user can be 10 email addresses, and that 10 users can use the same address

A contact list is meant a list of users that the user has added to his contacts. - nella_Mayert0 commented on September 19th 19 at 00:12
September 19th 19 at 00:05
But why even bother? Well, a lot of tables, so what? They are all needed, all proper and well configured. The queries for the simple and fast indexes on them are nice and well, complex selections, to build a convenient and understandable.
What twitch?
In the car there is so much parts, you don't want the number to decrease.
1. Over time, will increase the number of new entity types, and significantly, and therefore the number of tables, relationships;
2. At the level of software code would be much easier to work with only one link table and consider several types of relations than N*(N+1)/2 tables and even in the face of rising N.

If the analogy with cars: imagine that when you add new items, you would have to double the number of its parts. - nella_Mayert0 commented on September 19th 19 at 00:08
: lacking examples. - Freida.Windler commented on September 19th 19 at 00:11
: Examples of what? - nella_Mayert0 commented on September 19th 19 at 00:14
Why do you want to give up a large number of tables? - nella_Mayert0 commented on September 19th 19 at 00:17
: Because on 20+ tables of entities have 200+ tables relations. All the tables that you need. And with the advent of new entities, you have to create a bunch of new table. All relations are necessary. - Unique_Grant commented on September 19th 19 at 00:20
Something there is some feeling that if the structure twenty tables and two hundred ties, the designer does not understand something. Or vreditelstvu. - nella_Mayert0 commented on September 19th 19 at 00:23
September 19th 19 at 00:07
You can build a hierarchy of entities such as: message<-posts; message<-comments and communicate with the base type UserMessage.
You can post comments and stored in a single wide, but sparse table (some of the fields used for storing data posts part for comments, not the normalized form db) or sacrifice performance because of the excess of table joins (join message posts).
You can build a hierarchy of entities such as: message<-posts; message<-comments and communicate with the base type UserMessage.

This is already done, we are talking about entities which cannot be linked in a hierarchy.

You can post comments and stored in a single wide, but sparse table (some of the fields used for storing data posts part for comments, not the normalized form db)

So you can do all the entities in one table to thrust))) Is not a solution, and create a new one. - nella_Mayert0 commented on September 19th 19 at 00:10
If the number of related entities is not great, as the option to write a list of associated id in the field. reference integrity to observe have, but the queries where id in (....) with a small list of slow down will not.

Think everywhere there need communication many-to-many :) - Freida.Windler commented on September 19th 19 at 00:13
: What You propose is not a solution - it is its replacement by another, even greater. M2M communications are needed everywhere, once in the database not stored. - nella_Mayert0 commented on September 19th 19 at 00:16

Find more questions by tags SQLMySQLDatabases