Проектирование базы данных для принудительного сопряжения
Как мне лучше спроектировать базу данных, где у меня есть одна таблица игроков (с первичным ключомplayer_id), который я хочу объединить в группы по два человека, чтобы база данных могла применять ограничение, из которого состоит каждая командаexactly two игроки и каждый игрок находится вat most one команда?
Я могу придумать два решения, но оба меня не очень устраивают.
Одна возможность состоит в том, чтобы иметь две колонкиplayer1_id а такжеplayer2_id которыеunique внешние ключи, указывающие наplayer_id колонка в таблице игроков. Требуется дополнительная проверка, чтобы ни один игрок не являлся одновременно игроком 1 одной команды и игроком 2 второй команды.
Другая возможность, которая приходит мне в голову, - это соединить стол игрока и стол команды с таблицей членства в команде, которая имеетunique внешний ключ кplayer_id столбец в таблице игрока и второй внешний ключ, указывающий на первичный ключ таблицы команды. Здесь необходимо добавить проверку, что в каждой команде ровно два члена.
Есть ли лучший дизайн, который упрощает проверку ограничений?
Если это имеет значение: база данных, которую я использую, - это PostgreSQL 8.4, и я предпочитаю ее мощнуюсистема правил триггеры, где это возможно.
РЕДАКТИРОВАТЬ: решение на основе ответа Алексея Кузнецова
Мне это пока не кажется идеальным, но мне это нравится гораздо лучше, чем раньше. Я изменил Алекса ' решение, поскольку я не хочу иметь внешний ключ от игроков к командам, поскольку на этапе подачи заявки игроки могут зарегистрироваться.
create table TeamMemberships(
player_id int not null unique references Players(player_id),
team_id int not null references Teams(team_id),
NumberInTeam int not null check(NumberInTeam in (0,1)),
OtherNumberInTeam int not null, -- check(OtherNumberInTeam in (0,1)) is implied
check(NumberInTeam + OtherNumberInTeam = 1)
foreign key (team_id, OtherNumberInTeam) references TeamMemberships(team_id, NumberInTeam),
primary key (team_id, NumberInTeam)
);
Это определение гарантирует, что членство в команде происходит парами (и будет введено попарно). Теперь игроки могут состоять максимум из одной команды, а в командах может быть ровно 0 или ровно 2 игрока. Чтобы убедиться, что в каждой команде есть участники, я мог бы добавить внешний ключ в таблицу команд, который указывает на любое из двух членств. Но, как и Эрвин, я не фанат отложенной проверки ограничений. Есть идеи, как улучшить это? Или есть совершенно другой, лучший подход?
PS: метод работает также для команд с n & gt; 2 игроками. Нужно просто заменить OtherNumberInTeam на NextNumberInTeam со значением (т.е. ограничением) NumberInTeam + 1 mod n.