Я в базах данных, как уже признавался, не очень большой специалист. То есть, всякие там селекты-джойны написать могу, расписать структуру базы данных под первые три нормальные формы, НАДЕЮСЬ, тоже могу, но всё же есть у меня некоторая неустроенность.
Возьмём вот в качестве примера, допустим, я собираюсь написать базу данных для заказчика X-Com. Помните же эту игру, да?
Есть у меня таблица Base, допустим. Каждая запись в ней – это одна из баз X-Com.
Есть у меня таблица LandingCraft. Каждая запись – один из имеющихся на учёте десантных кораблей. В LandingCraft есть FK на Base – каждый корабль приписан к одной из баз.
Есть у меня таблица Trooper. Каждая запись – один из зольдат. В Trooper также есть FK на Base – каждый зольдат приписан к какой-то из баз.
Пока вроде всё в пределах первых трёх нормальных форм, да?
Теперь мы формируем десант. Помним – зольдат может быть приписан к какому-то крафту, а может быть ни к одному не приписан, на базе торчать, поэтому FK на крафт, во-первых, нулябельный, а во-вторых, не может транзитивно подменять собой FK на Base – если зольдат не приписан к десанту, приписка к базе у него не пропадает.
И вот тут у нас случается апож.
С одной стороны, у Trooper должны быть FK и на Base, и на LandingCraft, по уже озвученной причине – ни один из этих двух FK не может подменить собой другой, ибо и корабль может не нести десант, и зольдат может не быть поставлен в десант. Но с другой стороны, если зольдат поставлен в десант, в его строке информация о базе хранится дважды, что, во-первых, создаёт избыточность, а во-вторых, из-за этой избыточности никоим образом не препятствует создать средствами SQL некорректный десант – когда зольдат с одной базы ВНЕЗАПНО™ поставлен в десант, отправляющийся с другой базы.
Понятно, что на уровне бизнес-логики приложения мы это запретим. Но всё же – нет ли механизмов, позволяющих избежать такой щекотливой ситуации на уровне базы? Ещё лучше, если это можно сделать грамотным расписыванием структуры, а не наложением констрейнтов – чисто интуитивно это мной ощущается как недочёт именно структуры.
UPD: Одно из решений я придумал, но есть в нём некоторая корявость.
1. Создаём таблицу TrooperLocation, в которой FK на Base.
2. Делаем небиективное отношение 1:1 TrooperLocation - LandingCraft. То есть, каждый LandingCraft есть TrooperLocation, но не каждый TrooperLocation есть LandingCraft (и если он таковым не является, значит, это просто локация на базе).
3. В Trooper убираем FK на Base, а нулябельный FK на LandingCraft делаем ненулябельным FK на TrooperLocation.
Корявость в том, что никто не мешает нам сделать несколько "локаций на базе" (а это несколько отяжеловесит запрос "дай всех зольдат, кто на базе и не в десанте"), и в том, что для каждой базы придётся завести свою такую локацию.