Правильная модель базы данных для системы обратной связи с пользователем (интересный случай)

Question

Я занимаюсь разработкой приложения с использованием PHP и Yii Framework. Я думал о наиболее подходящей структуре базы данных для данной функциональности и вот что я придумал. Но я не уверен на 100%, что так и должно быть, поэтому я решил спросить сообщество.

Описание приложения:

Зарегистрированные пользователи могут участвовать в мероприятии. Каждое событие может иметь неограниченное количество пользователей, называемых "участниками мероприятия").

После окончания мероприятия каждый участник может оставить отзыв о каждом другом участнике того же события.

Структура базы данных:

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

Другие таблицы говорят сами за себя.

И вот самое главное:

Каждый участник события может иметь максимальное количество отзывов, равное количеству участников одного и того же события, исключая данного участника (например, если в мероприятии 5 участников, данный участник может получить 4 отзыва от участников одного и того же события). событие).

Подчеркну, что только участники одного мероприятия могут оставить отзыв (и только один) о данном участнике.

Ниже приведены шаги, которые я предпринял для обеспечения целостности базы данных:

1. Я создал столбец "id" в таблице "Участники", чтобы дать уникальный идентификатор каждому пользователю, который участвует в определенном событии. Этот идентификатор является составным (user_id и practice_id соединены вместе). Таким образом, идентификатор участника 23 пользователя, который участвовал в событии 14, будет 14-23.

Вы можете спросить, почему я решил создать отдельный столбец с этим идентификатором вместо простого определения первичного ключа, например:

PRIMARY KEY (user_id, event_id)

Читать дальше.

По окончании мероприятия каждый участник может оставить отзыв о других. Теперь этот идентификатор участника может быть ссылкой по внешним ключам "sender_id" и "receient_id" в таблице обратной связи.

Кроме того, первичный ключ таблицы обратной связи также будет сформирован путем объединения "sender_id" и "receient_id", поэтому, если пользователь 23 хочет оставить отзыв о пользователе 45 (оба участвовали в событии 71), Первичный ключ для обратной связи будет: 71-45-71-23.

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

Вопросы:

• Имеет ли этот подход право на существование?
• Каковы плюсы и другие, лучший способ приблизиться к этой функциональности?
• Могу ли я сгенерировать первичные ключи на основе значений других столбцов автоматически при вставке записи?

Answer 1

Это плохой дизайн. Просто создайте первичный ключ из 2 столбцов и внешние ключи из 2 столбцов. Это фундаментальный антишаблон, называемый "информация кодирования в ключах", который (тем самым) называется "умными", "интеллектуальными" или "сцепленными" ключами. Хороший ключ - это "тупой" ключ.

Например:

Несмотря на то, что в настоящее время легко реализовать Smart Key, трудно рекомендовать создать один из ваших собственных, который не является естественным ключом, потому что они, как правило, в конечном итоге сталкиваются с проблемами, какими бы ни были их преимущества, потому что это затрудняет работу баз данных. для рефакторинга навязывает порядок, который трудно изменить и который может быть неоптимальным для ваших запросов, требует сравнения строк, если интеллектуальный ключ содержит нечисловые символы, и менее эффективен, чем составной ключ, для помощи агрегированию на основе диапазона. Это также нарушает базовое правило, согласно которому в каждом столбце должны храниться атомарные значения.

Умные ключи также имеют тенденцию перерастать свои исходные ограничения кодирования

Кроме того, нет необходимости делать это.

Многие СУБД допускают "вычисляемые столбцы", значения которых автоматически вычисляются из других столбцов. Чтобы сделать один первичный ключ или внешний ключ, вам обычно нужно, чтобы он "сохранялся", то есть занимал память, как обычный столбец, по сравнению с простым вычислением при необходимости, как представление. В MySQL их нет, но в 5.7.5 есть некоторые функции, которые называются "сгенерированными столбцами", которые можно "хранить". Но не делайте этого для ПК или ФК!

Фактическая проблема проектирования - обработка базы данных / подтипов SQL / иерархий / наследования / полиморфизма.