Database design for constraint enforcing pairing

Question

How do I best design a database where I have one table of players (with primary key player_id) which I want to pair into teams of two so that the database can enforce the constraint that each team consists of exactly two players and each player is in at most one team?

I can think of two solutions, but both I'm not too happy about.

One possibility is to have two columns player1_id and player2_id that are unique foreign keys pointing to the player_id column in the player table. An additional check is needed so that no player is at the same time player1 of one team and player2 of a second team.

The other possibility that comes to my mind is to connect the player table and the team table with a team membership table that has a unique foreign key to the player_id column in the player table and a second foreign key pointing to the primary key of the team table. Here a check has to be added that every team has exactly two members.

Is there a better design that simplifies the checking of the constraints?

If it matters: the database I'm using is PostgreSQL 8.4 and I prefer its powerful rule system to triggers wherever possible.

EDIT: A solution based on the answer of AlexKuznetsov

It doesn't feel perfect to me yet, but I like it much better than what I had before. I modified Alex' solution since I don't want to have a foreign key from players to teams, as there is an application phase where players can enroll.

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)
);

This definition makes sure that team memberships come in couples (and will be inserted pairwise). Now players can be in at most one team and teams can have exactly 0 or exactly 2 players. To ensure that each team has members I could add a foreign key in the team table that points to any of its two memberships. But like Erwin I'm not a fan of deferred constraint checking. Any ideas how to improve with respect to this? Or is there a completely different, better approach?

PS: The methods works also for teams with n>2 players. One just has to replace OtherNumberInTeam by NextNumberInTeam with the value (i.e. constraint) NumberInTeam+1 mod n.

Answer 1

This sounds like something that would be much easier to enforce in a user interface than it would in the database.

When adding players to teams, don't allow more than 2 players to be added, and only allow players not already in a team to be added.

Answer 2

The first solution is limited if it may ever be necessary to change to teams with three members.

I like the idea of the teamMemebrs table, but to enforce your constraints, you will not ever be able to insert only one record at a time. All your inserts will have to be the set of two. Further, you get into complexity as to what do you do when player A ia in Team A and you want him to be moved to team B. Now you have to find someone to move to team A and then add him and his partner to team B.

Might it be better to create and populate the teams but make them only active if they have two members and each memeber is only on one team? At least then you can do the changes one at a time. SP you could move Person A from Team A to Team B and make Team A inactive until you find another person to add to it.

Answer 3

It seems to me that with the player1-id player2_id design, you can have all your constraints checked after a single insert to your team table.

In the teams+team-membership design, you'd get into the ugliness of deferred constraint checking, over and above all the triggering/rule stuff you'd have to setup.

Incidentally : systems such as SIRA_PRISE are built with the explicit purpose of handling precisely these kinds of constraint enforcement problem purely declaratively, i.e. WITHOUT any of the triggers hassle, or any other form of programming involved. You might be interested.

Answer 4

I don't know if this can work on Postgress, but here is a SQL Server solution:

CREATE TABLE dbo.Teams(TeamID INT NOT NULL PRIMARY KEY);
GO
CREATE TABLE dbo.Players(PlayerID INT NOT NULL PRIMARY KEY,
  TeamID INT NOT NULL FOREIGN KEY REFERENCES dbo.Teams(TeamID),
  NumberInTeam INT NOT NULL CHECK(NumberInTeam IN (1,2)),
  TeamMateID INT NOT NULL,
  TeamMatesNumberInTeam INT NOT NULL,
-- if NumberInTeam=1 then TeamMatesNumberInTeam must be 2
-- and vise versa
  CHECK(NumberInTeam+TeamMatesNumberInTeam = 3), 
  UNIQUE(TeamID, NumberInTeam),
  UNIQUE(PlayerID, TeamID, NumberInTeam),
  FOREIGN KEY(TeamMateID, TeamID, TeamMatesNumberInTeam)
    REFERENCES dbo.Players(PlayerID, TeamID, NumberInTeam)
);

INSERT INTO dbo.Teams(TeamID) SELECT 1 UNION ALL SELECT 2;
GO

-- you can only insert players in complete pairs

INSERT INTO dbo.Players(PlayerID, TeamID, NumberInTeam, TeamMateID, TeamMatesNumberInTeam)
SELECT 1,1,1,2,2 UNION ALL
SELECT 2,1,2,1,1;

You can try inserting a single player, or deleting a player from a team, or inserting more than two players per team - all will fail due to a complete set of constraints.

Note: the practice in SQL Server is to explicitly name all constraints. I did not name my constraints just in case that is not compatible with Postgres.