27cm
@27cm
TODO: Написать статус

Что делать, если количество таблиц связей многие-ко-многим начинает превышать все разумные пределы?

Есть таблицы, где хранятся отдельные сущности (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 позволяют оптимальнее решить эту проблему?
  • Вопрос задан
  • 3367 просмотров
Решения вопроса 1
Пригласить эксперта
Ответы на вопрос 3
@2bastu3
использовать связь один ко многим
Ответ написан
А зачем вообще дёргаться? Ну, много таблиц, ну и что? Они все нужные, все правильные и хорошо настроенные. Запросы по ним простые и быстрые, индексы на них лежат красиво и правильно, сложные выборки строить удобно и понятно.
Чего дёргаться-то?
В автомобиле вон сколько деталек, вы же не хотите их число уменьшить.
Ответ написан
Можно выстроить иерархию сущностей например: message<-posts; message<-comments и связывать с базовым типом UserMessage.
При этом можно post и comments хранить в одной широкой, но разреженной таблице (часть полей используется для хранения данных posts часть для comments, не нормализованная форма db) или пожертвовать производительностью из за лишних объединений таблиц (message join posts).
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы