Início / dba / Perguntas / 160646
Accepted
user45623
Asked: 2017-01-11 16:12:00 +0800 CST

Líder de equipe de normalização

  • 772

Cada um userestá exatamente em um team. Uma equipe tem exatamente um usuário na team_leadfunção. Como podemos normalizar isso no banco de dados? Exemplos em pseudo-SQL:

Estratégia 1:

table user:
id       int  pk
team_id  int  fk references team(id)
is_lead  int  //1 or 0

table team
id            int  pk

Problema: vários usuários em uma equipe podem ser marcados como o líder da equipe

Estratégia 2:

table user:
id       int  pk
team_id  int  fk references team(id)

table team
id            int  pk
team_lead_id  int  fk references user(id)

Problema: não há garantia de que o líder de equipe referenciado seja realmente um membro dessa equipe

Existe alguma maneira de normalizar isso no design do banco de dados ou temos que usar restrições ou impor no front-end?

mysql database-design

2 respostas

  1. Best Answer

    Existe uma maneira (SQL padrão) de obter as garantias que você deseja, mas receio que o mySQL 5.7 não seja capaz de lidar com isso. Meu exemplo foi testado no PostgreSQL.

    Você pode definir sua tabela de usuários desta maneira:

    -- Schema for everything
CREATE SCHEMA teams ;

-- Users
CREATE TABLE teams.users
(
  user_id integer NOT NULL PRIMARY KEY,
  team_id integer,
  CONSTRAINT users_team_id_user_id_key UNIQUE (user_id, team_id)
);

    Em seguida, tenha suas equipes definidas da seguinte maneira:

    -- Teams have one team_lead
CREATE TABLE teams.teams
(
  team_id integer NOT NULL PRIMARY KEY,
  team_lead_id integer NOT NULL,

  -- This constraint ensures that 'team_lead_id' is a 'user_id', and 
  -- this user is actually a member of the team
  CONSTRAINT teams_team_lead_id_fkey FOREIGN KEY (team_lead_id, team_id)
      REFERENCES teams.users (user_id, team_id) MATCH FULL
      ON UPDATE RESTRICT ON DELETE RESTRICT
      DEFERRABLE INITIALLY DEFERRED
);

    Ao usar um composite foreign key constraint, você realmente garante que o usuário que está verificando é realmente um membro da equipe.

    Depois que esta segunda tabela foi definida, agora você pode adicionar a restrição de chave estrangeira users.team_id -> teams.team_id:

    -- The 'team_id' on users is a 
ALTER TABLE teams.users
  ADD CONSTRAINT users_team_id_fkey FOREIGN KEY (team_id)
      REFERENCES teams.teams (team_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
      DEFERRABLE INITIALLY DEFERRED ;

    Observe que ambas as tabelas se referem uma à outra. Você tem referências circulares.

    Sendo assim: as restrições devem ser verificadas ao final de uma transação, não apenas ao final de um extrato. No caso do PostgreSQL, eles precisam ser definidos DEFERRABLEe INITIALLY DEFERRED(é com isso que o mySQL não consegue lidar). Você precisa inserir dados para a primeira equipe (tabela teams) e seu líder de equipe (tabela users) em uma única transação ou, caso contrário, não há como satisfazer as duas FOREIGN KEYrestrições:

    Vamos supor que sejam 'usuário 1' e 'equipe 5':

    BEGIN TRANSACTION ;
INSERT INTO teams.users (user_id, team_id)      VALUES (1, 5) ;
INSERT INTO teams.teams (team_id, team_lead_id) VALUES (5, 1) ;
COMMIT TRANSACTION ;

    Infelizmente, a documentação do mySQL 5.7 sobre restrições de chaves estrangeiras afirma:

    [...] InnoDB verifica restrições de chaves estrangeiras imediatamente ; a verificação não é adiada para confirmação da transação. De acordo com o padrão SQL, o comportamento padrão deve ser a verificação adiada. Ou seja, as restrições só são verificadas após o processamento de toda a instrução SQL. Até que o InnoDB implemente a verificação de restrição adiada, algumas coisas são impossíveis, como excluir um registro que se refere a si mesmo usando uma chave estrangeira.

    A documentação do MariaDB sobre FKs não menciona a possibilidade de adiamento.

    • 1

  2. Não é nada complicado. Você já deve ter uma tabela de referência cruzada/interseção para os relacionamentos mm Usuário-Equipe:

    create table TeamPlayers(
  TeamID   int  not null references Teams( ID ),
  PlayerID int  not null referneces Users( ID ),
  constraint PK_TeamPlayers primary key( TeamID, PlayerID ),
  constraint UQ_TeamPlayers_Player unique( Playerid )
);

    A restrição FK é típica dessas tabelas. Ele garante que o mesmo jogador não pode ser listado mais de uma vez para o mesmo time.

    A restrição Unique garante que o mesmo jogador não pode ser listado mais de uma vez em todas as equipes.

    Você pensaria que a restrição Unique torna o PK supérfluo. Na verdade, não.

    Embora possa ser colocado em uma tabela separada, o líder da equipe é um atributo da equipe, então vou mostrá-lo como parte da tabela Teams:

    create table Teams(
  ID    int  auto_generate primary key,
  ...
  Lead  int,
  ...,
  constraint FK_TeamsLeader( ID, Lead )
    references TeamPlayers( TeamID, PlayerID )
);

    Ao referenciar o FK à tabela de cruzamento, garantimos que o líder da equipa é um utilizador já definido como jogador da equipa.

    Assim, você garante que cada usuário está em exatamente um time e um time tem exatamente um usuário na função team_lead e esse usuário é aquele que está jogando no time.

    • -1

relate perguntas