Рекурсивная структура группы в MySQL

Я разрабатываю систему, которая должна позволять объединять пользователей в группы. Эти группы могут свободно создаваться, редактироваться и удаляться другими привилегированными пользователями системы. Эта часть проста; просто создайте таблицу group_users , которая связывает пользователей в группы. (Если вы сторонник нормализации, вы можете создать таблицу group , в которой просто перечисляются группы, а затем иметь таблицу group_users , которая связывает их вместе - это тоже хорошо)

Вот где все усложняется. Клиент хочет, чтобы группы также содержали группы, до произвольной глубины и с произвольным перекрытием (группы могут быть в нескольких группах, а группы могут содержать несколько групп). Это достаточно легко сохранить (с таблицей group_groups ), но сложно выполнить запрос без какого-либо расширения сортировки, такого как Oracle CONNECT BY.

Эта рекурсивная иерархия также должна иметь обратную силу - это означает, что если group A содержит группу B, а группа B изменена, тогда группа A также будет изменена, поэтому я не могу обмануть и просто сгладить структуру. Если вы мне не верите, что его нельзя просто сплющить, подумайте об этой ситуации. У вас есть группа под названием «крутые люди», в которую входят пользователи 1 и 2. Кто-то создает группу под названием «ДЕЙСТВИТЕЛЬНО крутые люди», которая включает пользователя 3 и группу «крутые люди». Когда я спрашиваю «ДЕЙСТВИТЕЛЬНО крутые люди», я должен сделать вывод, что пользователи 1, 2 и 3 находятся в группе. Теперь предположим, что кто-то решает, что пользователь 2 больше не классный человек, и удаляет пользователя 2 из числа «крутых людей». После этого момента «ДЕЙСТВИТЕЛЬНО крутые люди» будут содержать только пользователей 1 и 3. Если бы я изначально выровнял структуру, я бы не знал, что нужно удалить пользователя 2 из «ДЕЙСТВИТЕЛЬНО крутых людей», когда я удалил его из «крутых людей» ".

Таким образом, тривиальное выравнивание в этом сценарии не сработает. Другие варианты, которые я рассмотрел:

• Выполнение рекурсии в коде.
  • Слишком медленно для сложных групп, а также требует, чтобы вы затем выполняли связанные объединения в памяти, а не в базе данных.
• Выровнять структуру до group_users_flattened , но также поддерживать group_groups таблица. Создайте триггер для group_users_flattened при INSERT / UPDATE / DELETE, который перейдет в таблицу group_groups , найдет все группы, содержащие эту группу, и динамически внесет соответствующие изменения в group_users_flattened .
  • Я могу представить, как это работает, но это кажется запутанным и подверженным ошибкам, и у меня такое чувство, что я не вижу ошибки.

Есть ли другие идеи, которые я не рассматривал?