Diseño de database para el emparejamiento de aplicación de restricciones

¿Cómo mejor diseñar una database donde tengo una tabla de jugadores (con la key principal player_id ) que quiero emparejar en equipos de dos para que la database pueda hacer cumplir la restricción de que cada equipo consiste exactamente en dos jugadores y cada jugador está en como máximo un equipo?

Puedo pensar en dos soluciones, pero no estoy muy feliz por las dos cosas.

Una posibilidad es tener dos columnas, player1_id y player2_id, que son keys externas únicas que apuntan a la columna player_id en la table del jugador. Se necesita un control adicional para que ningún jugador sea al mismo time jugador1 de un equipo y jugador2 de un segundo equipo.

La otra posibilidad que me viene a la mente es conectar la table del jugador y la table del equipo con una tabla de membresía del equipo que tiene una key externa única para la columna player_id en la table del jugador y una segunda key externa apuntando a la tecla principal del equipo table. Aquí se debe agregar un cheque que cada equipo tiene exactamente dos miembros.

¿Hay un mejor layout que simplifique la verificación de las restricciones?

Si es importante: la database que estoy usando es PostgreSQL 8.4 y prefiero que su poderoso sistema de reglas se active siempre que sea posible.

EDIT: una solución basada en la respuesta de AlexKuznetsov

Todavía no me parece perfecto, pero me gusta mucho mejor de lo que tenía antes. Modifiqué la solución de Alex porque no quiero tener una key externa de jugadores a equipos, ya que hay una fase de request donde los jugadores pueden inscribirse.

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

Esta definición asegura que las membresías del equipo se realicen en parejas (y se insertán en pares). Ahora los jugadores pueden estar en un equipo como máximo y los equipos pueden tener exactamente 0 o exactamente 2 jugadores. Para asegurar que cada equipo tenga miembros, puedo agregar una key externa en la tabla del equipo que apunte a cualquiera de sus dos membresías. Pero al igual que Erwin, no soy partidario de la verificación de restricciones diferidas. ¿Alguna idea de cómo mejorar con respecto a esto? ¿O hay un enfoque completamente diferente y mejor?

PD: Los methods también funcionan para equipos con n> 2 jugadores. Uno solo tiene que replace OtherNumberInTeam por NextNumberInTeam con el valor (es decir, restricción) NumberInTeam + 1 mod n.

No sé si esto puede funcionar en Postgress, pero aquí hay una solución de SQL Server:

 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 

– solo puedes insert jugadores en pares completos

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

Puedes intentar insert un solo jugador, o eliminar un jugador de un equipo, o insert más de dos jugadores por equipo: todos fallarán debido a un set completo de restricciones.

Nota: la práctica en SQL Server es nombrar explícitamente todas las restricciones. No mencioné mis restricciones solo en caso de que no sea compatible con Postgres.

Esto parece algo mucho más fácil de aplicar en una interfaz de usuario que en la database.

Cuando agregue jugadores a equipos, no permita que se agreguen más de 2 jugadores, y solo permita que se agreguen jugadores que aún no están en un equipo.

La primera solución es limitada si alguna vez es necesario cambiar a equipos con tres miembros.

Me gusta la idea de la tabla teamMemebrs, pero para hacer cumplir sus limitaciones, nunca podrá insert solo un logging a la vez. Todos tus insertos tendrán que ser el set de dos. Además, te vuelves complejo en cuanto a lo que haces cuando el jugador A ia en el Equipo A y quieres que se mueva al equipo B. Ahora tienes que encontrar a alguien para pasar al equipo A y luego agregarlo a él y a su compañero al equipo SEGUNDO.

¿Sería mejor crear y poblar los equipos pero hacerlos solo activos si tienen dos miembros y cada miembro está solo en un equipo? Al less entonces puedes hacer los cambios uno a la vez. SP puede mover a la Persona A del Equipo A al Equipo B y hacer que el Equipo A permanezca inactivo hasta que encuentre a otra persona para agregarle.

Me parece que con el layout player1-id player2_id, puede tener todas sus restricciones marcadas después de una sola inserción en su table de equipo.

En el layout de los equipos + membresía del equipo, te meterías en la fealdad de la verificación de restricciones diferidas, además de todo lo que deberías configurar para disparar / regla.

Dicho sea de paso: los sistemas como SIRA_PRISE se construyen con el propósito explícito de manejar precisamente este tipo de problemas de imposition de restricciones de manera puramente declarativa, es decir, SIN la molestia de los desencadenantes ni ninguna otra forma de progtwigción involucrada. Tú podrías estar interesado.