Как обрабатывать отношения "многие ко многим" с более чем 2 таблицами?
Вот где я сейчас нахожусь. У меня есть четыре таблицы: задача, проект, возможность и task_xref. Таблицы проекта и возможностей имеют отношение "один ко многим" с задачей. Я храню эти отношения в task_xref. Схема выглядит примерно так для каждой таблицы (упрощенно):
task
----
id(pk)
name
project
-------
id(pk)
name
...
opportunity
-----------
id(pk)
name
...
task_xref
---------
task_id(task id)
fkey(other table id)
Предположим, что ключи в проекте и возможности не будут одинаковыми (GUID), поэтому возможность не может извлекать задачи для проекта и так далее. Это хорошо работает на поверхности, одна таблица внешних ссылок, чтобы поддерживать отношения между задачей и проектом, возможность (или любые другие таблицы, которые могут нуждаться в связи задачи в будущем).
Моя нынешняя дилемма - двунаправленность. Если я ищу все задачи для отдельного проекта или возможности, это не проблема. Если я откладываю задание и хочу узнать название соответствующего проекта или возможности, я не могу. У меня нет никакого способа узнать, является ли связанный fkey проектом или возможностью. В будущем у меня, вероятно, будут другие таблицы со связями задач; хотя сейчас у меня есть 2 таблицы, в будущем их может быть гораздо больше.
Вот возможные решения, о которых я думал до сих пор: 1) отдельная таблица внешних ссылок для каждой пары (например, task_project_xref, task_opportunity_xref...) минусы: мне нужно выполнить запрос для каждой таблицы внешних ссылок, чтобы найти связи для задачи
2) третий столбец в task_xref, указывающий на минусы родительской таблицы: мне это кажется клуджем
3) сохранить первичные ключи в проекте, возможность идентифицируемым способом (например, proj1, proj2, proj3, opp1, opp2, opp3), чтобы я мог сказать, к какой таблице относится задача, посмотрев на минусы fkey: это похоже на I ' Я делаю первичные ключи в проектах и возможностях волшебными, наполняя их большим смыслом, чем просто идентификатором для одной записи (может быть, я слишком обдумываю это)
Мой вопрос заключается в следующем: есть ли другие решения, которые я пропускаю? Какое решение лучше / хуже других?
Я стараюсь ограничить количество соединений, если это возможно, и производительность как можно лучше. Я также не против объединения данных в коде, если это поможет упростить ситуацию.
Я использую PHP и MySQL (в настоящее время таблицы MyISAM, но я буду использовать INNODB, если есть причина для этого).
2 ответа
Я предпочитаю иметь отдельную таблицу внешних ссылок для различных концепций. Таким образом, связь между концептом и его задачами легче поддерживать и изолировать от отношений другого концепта к задачам.
Возможно, я бы предпочел единую внешнюю ссылку для всех связанных понятий, если бы любая таблица в вашем дизайне могла иметь задачи, и тогда у меня был бы дополнительный столбец, чтобы указать, какой тип объекта является родителем задач.
Ваши SQL-соединения будут лучше, если у task_xref есть отдельные поля project_id и chance_id. Это хороший способ сделать это, потому что запросы просты. Кроме того, просто определить тип задачи, просто увидев, какой из внешних ключей не равен NULL. Это также эффективно. При объединении с задачами необходимы два запроса, поскольку проект и возможности, несомненно, будут иметь разную структуру, поэтому результаты не могут быть unionвсе равно.