Несколько отношений «многие ко многим» к одной таблице

В моей базе данных есть несколько категорий, к которым я хочу прикрепить пользовательские текстовые «заметки». Например, запись в таблице высокого уровня с именем jobs может иметь несколько примечаний, написанных пользователем, но то же самое может быть и запись более низкого уровня в sub_projects. Поскольку все эти заметки будут иметь одинаковый формат, мне интересно, могу ли я упростить ситуацию, имея только одну таблицу заметок, а не серию таблиц, таких как job_notes или project_notes, а затем использовать несколько отношений «многие ко многим», чтобы связать ее к нескольким другим столам одновременно.

Если это не ошибочная идея с самого начала (дайте мне знать, если это так!), мне интересно, как лучше всего это сделать. Насколько я понимаю, я мог бы сделать это двумя способами:

  1. Создайте таблицу соединений «многие ко многим» для каждой более крупной категории, например job_notes_mapping и project_notes_mapping, и управляйте отношениями MtM по отдельности.

  2. Иметь единую соединительную таблицу, связанную либо с перечислением, либо с отдельной таблицей для table_type, которая указывает, на какую таблицу сопоставляется отношение MtM:

    +-------------+-------------+---------------+
| note_id     | table_id    | table_type_id |
+-------------+-------------+---------------+ 
|           1 |           1 | jobs          |
|           2 |           2 | jobs          |
|           3 |           1 | project       |
|           4 |           2 | subproject    |
| ........... | ........... | ........      |
+-------------+-------------+---------------+

Простите меня, если какие-либо из этих идей совершенно ужасны, но я подумал, что это может быть интересным вопросом, по крайней мере, концептуально.


mysql database many-to-many junction-table
person Luciasar    schedule 13.07.2015    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Идеальным способом, IMO, было бы иметь супертип заданий, проектов и подпроектов — давайте назовем это действиями — для которых вы могли бы определить любые общие типы фактов.

Например (я предполагаю, что задания, проекты и подпроекты образуют иерархию сдерживания):

activities (activity PK, activity_name, begin_date, ...)
jobs (job_activity PK/FK, ...)
projects (project_activity PK/FK, job_activity FK, ...)
subprojects (subproject_activity PK/FK, project_activity FK, ...)

К сожалению, большинство схем баз данных определяют уникальные автоматически увеличивающиеся идентификаторы PER TABLE, что очень затрудняет реализацию супертипизации после загрузки данных. PostgreSQL позволяет повторно использовать последовательности, и это здорово, некоторые другие СУБД (например, MySQL) совсем не упрощают эту задачу.

Моим вторым выбором будет ваш вариант 1, поскольку он позволяет определять ограничения внешнего ключа. 2 вариант мне вообще не нравится.

person reaanb    schedule 13.07.2015
comment
Если кого-то интересует соглашение об именах столбцов в моем примере, это role_domain, а не table_id. - person reaanb; 13.07.2015
comment
К сожалению, при втором взгляде этот ответ на самом деле не то, что я ищу. Так как действия будут ссылаться через внешний ключ, это больше не будет отношением «многие ко многим» — у вас может быть только одна запись действия для одного задания. В моем случае Notes я хотел бы иметь несколько заметок, описывающих работу/проект/подпроект. - person Luciasar; 15.07.2015
comment
Моя цель состояла в том, чтобы супертипировать задания, проекты и подпроекты, чтобы можно было использовать сопоставление «многие ко многим» между действиями и заметками вместо отдельных сопоставлений для каждого уровня. Я не предлагал занятия вместо заметок. - person reaanb; 15.07.2015
comment
Итак, вы описываете систему, в которой все идет job->activity<-activity_note_mapping->notes? Можете ли вы описать последовательность, в которой вы будете создавать записи, и как это связано с проблемой супертипизации, которую вы описали? - person Luciasar; 15.07.2015
comment
По моему мнению, каждое задание (а также проект и подпроект) является действием, поэтому вы можете напрямую присоединиться к заданию в activity_note_mapping. Если идентификаторы заданий, проектов и подпроектов перекрываются, можно вместо этого добавить Activity_id к каждому из них, но тогда это уже не идеально. - person reaanb; 15.07.2015
comment
Чем он менее идеален, чем случай прямого отображения? Наличие одной дополнительной таблицы, на которую все еще распространяются ограничения внешнего ключа, кажется мне довольно хорошей идеей - я не уверен, в чем преимущество прямого присоединения заданий к activity_note_mapping с точки зрения поиска. Не будет ли объединение всех таблиц в рамках набора действий намного медленнее? - person Luciasar; 15.07.2015
comment
Я имею в виду, что наличие Activity_id для каждой работы и необходимость присоединения от заданий к действиям перед присоединением к activity_note_mapping менее идеальны, чем job_id, являющийся подмножеством activity_id и возможностью присоединения от заданий непосредственно к activity_note_mapping. Я не уверен, что вы подразумеваете под объединением всех таблиц. - person reaanb; 15.07.2015

arrow_upward
0
arrow_downward

К сожалению, мы пришли к самому уродливому ответу на этот вопрос, который состоит в том, чтобы иметь таблицу заметок для каждого типа записи — job_notes, project_notes и subproject_notes. Наши причины для этого были следующие:

  • Таблица с одним соединением со столбцом, содержащим «тип» соединения, имеет низкую производительность, поскольку ни один из внешних ключей не является «настоящим» и должен искаться вручную. Это усугубляется тем фактом, что поле «Примечания» содержит много текста для каждой записи.

  • Соединительная таблица для каждой записи добавляет дополнительную таблицу по сравнению с простым наличием отдельных таблиц заметок для каждого типа таблиц, и, хотя это выглядит немного красивее, это не дает существенного прироста производительности.

Я не удовлетворен этим ответом, потому что кажется таким расточительным эффективно дублировать одну и ту же таблицу Notes для каждой описываемой таблицы заданий/проектов/подпроектов. Однако мы не смогли найти ответ, который бы поддерживал производительность в долгосрочной перспективе. Я оставлю это открытым, если у кого-то есть лучшие рекомендации о том, как это сделать!

person Luciasar    schedule 15.07.2015