Есть таблицы, где хранятся отдельные сущности (users, posts, files, comments ...) в количестве N > 20. И есть таблицы в которых хранятся связи между парами разных сущностей (users_contacts, users_files_downloads, favorite_posts, ...) в количестве ~ N*(N+1)/2. Количество таблиц второго типа растёт гораздо быстрее. Например, если добавить новую сущность, придётся завести ~ (N+1) таблиц связей.
В общем так дальше жить нельзя, поэтому реализовал следующее решение:
1. Отказываемся от многочисленных таблиц связей и вместо них вводим две новые таблицы:
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. Таблицы сущностей (users, posts, files, comments ...) должны быть связаны с таблицей `entities`.
Плюсы:
1. Теперь, если появится какая-то новая сущность, для неё достаточно завести всего одну таблицу.
2. Поля общие для всех сущностей можно вынести в таблицу `entities`. Теперь, например, простым запросом можно получить идентификаторы всех созданных сущностей (разных типов) за определённый промежуток времени. Это удобно для лент новостей, в которую попадает информация о разных добавленных объектах, а не только однотипных.
3. Сохранена ссылочная целостность.
Минусы:
1. Усложнились запросы к базе данных, в которых участвует более одной сущности, т. к. джойниться они теперь будут через таблицу `entities_relations`.
2. Для добавления теперь потребуется два запроса: сначала добавляем строку в таблицу `entities`, получаем автоинкрементированное значение `id_entity`, и уже с ним добавляем данные сущности в основную таблицу (users, posts, comments, ...).
Что думаете или где можно почитать о практике использования такого подхода? Как его можно улучшить?
Какие есть альтернативы? Возможно, встроенные средства MySQL позволяют оптимальнее решить эту проблему?
Простой
Простой
Простой
