MySQL Database Design – Normalizing Team Leader

database-designMySQLnormalization

Each user is on exactly one team. A team has exactly one user in the team_lead role. How can we normalize this in the database? Examples in pseudo-SQL:

Strategy 1:

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

table team
id            int  pk

Problem: Multiple users on one team could be marked as the team lead

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

Problem: There is no guarantee the referenced team lead is actually a member of that team

Is there any way to normalize this in the database design, or do we have to use constraints or enforce on the front-end?

Best Answer

There's a (standard SQL) way of getting the guarantees you want, but I'm afraid mySQL 5.7 is not able to handle it. My example has been tested on PostgreSQL.

You can define your users table this way:

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

Then, have your teams have be defined in the following fashion:

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

By using a composite foreign key constraint you actually make sure that the user you are checking for, is actually a member of the team.

After this second table has been defined, you can now add the foreign key constraint 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 ;

Note that both tables are referrrig to each other. You have circular references.

Being this the case: the constraints must be checked at the end of a transaction, not just at the end of a statement. In the case of PostgreSQL, they need to be defined DEFERRABLE and INITIALLY DEFERRED (this is what mySQL cannot handle). You need to enter data for the first team (table teams) and its team leader (table users) all in one transaction, or, otherwise, there's no way to satisfy both FOREIGN KEY constraints:

Let's assume these are 'user 1' and 'team 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 ;

Unfortunately, mySQL 5.7 documentation about foreign key constraints states:

[...] InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key.

MariaDB documentation about FKs doesn't have mentions about deferrability.

Related Question