Как правильно разруливать круговые связи между таблицами в БД? И можно ли реализовать такое наследование в Entity Framework?
Есть таблицы: Юзеры, Группы, Роли. В таблице Юзеры есть ссылка на т. Группы (она заполняется, если юзер принадлежит какой-то группе, и остается NULL, если не принадлежит). В таблице Группы есть ссылка на т. Роли, всегда ненуллабельная, т.к. каждой группе соответствует 1 роль. И в таблице Юзеры тоже есть ссылка на т. Роли, т.к. у каждого юзера должна быть 1 роль. И есть требование, чтобы при смене роли для группы автоматически менялась роль у всех юзеров, принадлежащих этой группе.
Проблема: мы получили круговые связи между тремя таблицами, т.е. т. Юзеры ссылается на т. Роли как напрямую (назовем эту связь прямой ссылкой), так и через т. Группы как посредника, т.к. таблица Группы ссылается на т. Роли (назовем эту связь косвенной ссылкой). Вопрос1: нормально ли проектировать схему БД таким образом, или это плохо, когда есть круговая связь таблиц (т.е. когда для получения каких-то данных мы можем выбирать из различных комбинаций джойнов таблиц)?
Дальше, есть варианты (предполагаем, что прямая ссылка у нас - это обычный столбец-форейн кей в т. Юзеры, ведущий на т. Роли): 1) сделать поле-прямую ссылку на Роль в таблице Юзеры нуллабельной и заполнять его только для юзеров, которые не принадлежат никакой группе, а для остальных юзеров брать роль по косвенной ссылке ГруппаИД->РольИД. 2) сделать поле-прямую ссылку на Роль в таблице Юзеры ненуллабельной и для всех юзеров заполнять его (тут для согласованности данных нужно обязательно, чтобы для групповых юзеров прямая ссылка т. Роль и косвенная ссылка ГруппаИД->РольИД вели на одну и ту же роль).
В случае первого варианта минус в том, что для получения роли нам нужно сджойнить не 2 таблицы, а 3 (т.е. это должно быть медленнее). В случае второго варианта минус в том, что у групповых юзеров фактически будет 2 ссылки на роль, хотя у нас юзер имеет только 1 роль (даже если считать, что обе ссылки всегда будут вести на одну и ту же роль, мне не нравится сам факт того, что в БД нет однозначности насчет того, по какой ссылке мы должны получать роль юзера), к тому же при изменении роли для группы мы дополнительно должны не забыть изменить прямую ссылку на роль у юзеров из этой группы. Минусы обоих вариантов в том, что нам нужен дополнительный механизм, чтобы контролировать согласованность данных по обеим ссылкам, т.е. что при варианте 1 у нас никогда не заинсертится в базу групповой юзер с ненуллабельной прямой ссылкой на группу , а в случае варианта 2, что у нас никогда не заинсертится групповой юзер, у которого прямая ссылка и косвенная ссылка будут вести на разные роли. Вопрос 2: какой вариант лучше, 1 или 2? И зависит ли это от кол-ва джойнов, которые следует сделать по косвенной ссылке, т.е. например, если нам нужно сджойнить более 10 таблиц, то для улучшения быстродействия нам лучше добавить прямую ссылку, если менее 10 таблиц, то для уменьшения неоднозначности в БД нам лучше оставлять прямую ссылку для негрупповых юзеров равной NULL?
Дальше, есть 2 варианта хранения прямой ссылки из юзера на роль : 1) обычное поле в таблице, хранящее реальные данные (форейн кей, ссылающийся на роль) 2) "виртуальное" поле в таблице (не хранит данные, а возвращает данные из другого столбца). В случае первого варианта мы должны иметь механизм, которым мы должны контролировать согласованность данных, если у нас на сущность ведет более одной ссылки. Это можно делать в коде приложения, но мне не нравится эта идея, т.к. я считаю, что согласованность своих данных база должна контролировать сама. Как я понимаю, лучшим вариантом для этого будет написать триггер INSTEAD OF на изменение Юзеров (для вставляемого юзера сами достаем ссылку на роль по косвенной ссылке ГруппаИД->РольИД и вставляем в БД) и триггер AFTER на изменение Групп (после изменения ссылки на роль у группы ищем всех юзеров в этой группе и меняем ссылки на роль у них). Минус такого варианта - лишнее поле в БД, хранящее дубликат данных, которые итак можно получить через косвенную ссылку, которое нужно дополнительно валидировать при изменениях и которое никак не будет обозначено, что это должно быть неизменяемое поле (т.е. плохо то, что писатели update\insert скриптов или Entity Framework могут пытаться обновлять это поле, хотя триггер не даст им это сделать). В случае второго варианта - я так понимаю, прямую ссылку можно сделать как вычисляемое поле, на которое повесить функцию, которая будет по косвенной ссылке ГруппаИД->РольИД возвращать роль. Минусы этого - нельзя будет сделать эту прямую ссылку форейн кеем (т.к. вычисляемые столбцы не могут быть форейн кеями), увеличивается кол-во джойнов таблиц для нахождения ссылки на роль, и самое главное, этот способ тупо не подходит для юзеров, которые не принадлежат группе (т.к. у таких юзеров нет косвенной ссылки ГруппаИД->РольИД),т.е. мы не может вообще использовать этот вариант при такой схеме БД. Вопрос 3: каким способом это лучше реализовать (и есть ли еще другие способы)?
Вообще, я бы лучше в этой ситуации изменил схему БД так: добавил таблицу ГрупповыеЮзеры и таблицу ИндивидуальныеЮзеры, которые бы ссылались на т. Юзеры связью 1-к-1. Тогда ссылку на т. Группы можно было бы вынести из т. Юзеры в т. ГрупповыеЮзеры, а ссылку на т. Роли можно было бы вынести из т. Юзеры в т. ИндивидуальныеЮзеры. Тогда возможность нарушения целостности ссылок пропадает, т.к. все групповые юзеры будут хранить ссылку на роль через косвенную ссылку, а все негрупповые юзеры будут хранить ссылку на роль через прямую ссылку. Но насколько я знаю, в Entity Framework нет поддержки создания наследования сущностей. То есть получается, что в этом варианте в модели dbContext я должен создать классы с наследованием:
User{}
GroupUser: User{}
IndividualUser: User{}
И если я хочу получить всех юзеров, я пишу что-то вроде:
List users = dbContext.Users.ToList();
то Entity должен прочитать данные не только из таблицы Юзеры, но и из таблицы ГрупповыеЮзеры и таблицы ИндивидуальныеЮзеры и соотвественно создать объекты классов GroupUser и IndividualUser. Насколько я знаю, в EF это невозможно? Вопрос4: можно ли реализовать такую схему с наследованием в Entity Framework?
каким способом это лучше реализовать (и есть ли еще другие способы)?
Да. Таблицы Пользователь - Группа - Роль связаны связью 1-М (внешний ключ). Даю подсказку: пользователю назначается группа по-умолчанию. Оная может иметь ссылку на роль или нет — вам решать своё противоречие: группы нет, но роль должна быть. Если решите, что роль обязательна, то назовите группу так же как роль.
Так же неплохо ознакомиться с любой готовой системой Пользователь, Группа, Роль. Там есть множественные связи пользователя с группами и ролями. Итоговые роли являются суммой ролей групп и индивидуальных ролей. Усложнение: роль может быть как разрешительная, так и запретительная. MS в этом случае даёт привилегию запрету.
Даю подсказку: пользователю назначается группа по-умолчанию.
Это можно рассматривать как костыль (нужно создавать строки для групп, которых нет в помине и которые только для прослойки юзера к роли).
Там есть множественные связи пользователя с группами и ролями. Итоговые роли являются суммой ролей групп и индивидуальных ролей
Допустим, требование такое, что нет никаких сумм ролей, потому что роль у юзера всегда должна быть только 1. При этом юзер может быть групповым и не групповым (т.е. у негруппового нельзя получить роль по ссылке на группу, т.к. нет группы).
Вот как лучше такую схему в БД представить? Чтобы без костылей, без избыточности\возможности нарушения целостности , и чтобы еще с EF эта схема работала.
Средний
