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`.
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.
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?