0

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?

  • I wouldn't be surprised at all if there is another question that is logically equivalent to this but has different table/field names; I just can't think of the right terms to search – user45623 Jan 11 '17 at 0:13
  • <opinion>Yet another case of trying to do more than FKs were designed for.</opinion> – Rick James Jan 11 '17 at 4:54
  • @RickJames: let's say that E-R diagrams and other modeling tools are (at least in some cases) richer than SQL, and we would like to translate this richness as much as possible. If the tool should be FK or another type of constraint, I agree, it's open for debate. I love the database making sure all constraints are always satisfied. Makes the guarantees on the data far stronger, and the number of assumptions reduced. – joanolo Jan 11 '17 at 8:47
1

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.

  • It's a little silly, but we can handle this without a circular reference or needing to defer things by creating a UNIQUE KEY (team_id, is_lead) and allowing is_lead to be null. Then the lead for each team would have an is_lead of 1, and all other members of the team would have is_lead set to null . I was hoping to resolve without having to use constraints, but it sounds like that's not possible – user45623 Jan 11 '17 at 4:18
  • A PRIMARY KEY is UNIQUE, so it does not make much sense to have both PRIMARY KEY(u) and UNIQUE(u,t). – Rick James Jan 11 '17 at 4:51
  • @user45623: Yes you can, but that guarantees that you have no teams with more than one leader, but it does not guarantee that all teams have one leader. It is a weaker constraint. – joanolo Jan 11 '17 at 8:05
  • I agree that (t,u) potentially adds something. UNIQUE(t,u) adds an unnecessary uniqueness constraint; INDEX(t,u) will suffice. – Rick James Jan 11 '17 at 18:39
  • Eh? Since u is declared unique (by being PK), there is unnecessary overhead in making (t,u) also unique. (I'm talking from a MySQL/MariaDB point of view; I don't know postgres.) – Rick James Jan 11 '17 at 21:27
-1

It's not at all complicated. You already must have a cross reference/intersection table for the m-m User-Team relationships:

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

The FK constraint is typical of such tables. It assures the same player cannot be listed more than once for the same team.

The Unique constraint assures the same player cannot be listed more than once across all teams.

You would think that the Unique constraint renders the PK superfluous. Not really.

Though it can be placed in a separate table, the team leader is an attribute of the team so I'll show it as part of the Teams table:

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

By referencing the FK to the intersection table, we guarantee that the team leader is a user already defined as a player on the team.

So you guarantee that each user is on exactly one team and a team has exactly one user in the team_lead role and that user is one that is playing on the team..

  • "You already must have a cross reference/intersection table for the m-m User-Team relationships" As described at the beginning of the question and demonstrated in the example tables, this is a one-to-many relationship (a user can only be on one team), so we currently don't have an intersection table. I think your SQL syntax on the constraints is also a little off, but I get the gist. Unfortunately I'm not sure that it's very applicable to the question. – user45623 Jan 14 '17 at 9:21
  • @user45623 If you look at the design, though I stated "m-m" in the text, the table actually implements a "1-m" relationship. How is the solution not applicable? Where does it fail to implement the listed specifications? – TommCatt Jan 16 '17 at 17:02
  • Your answer is confusing for many reasons. It does not use standard SQL syntax; it does not even use consistent syntax (you declare primary key in a different manner between the two tables); it incorrectly describes the problem as m-m and uses an intersection table for a 1-m relationship. I got hung up enough on all of those issues that I had trouble analyzing your solution. Additionally, I don't want to use an intersection table for a 1-m relationship. Finally, your solution creates a circular reference, a problem already discussed in joanolo's answer which was posted before yours. – user45623 Jan 17 '17 at 4:09
  • Which standard SQL syntax? Yes, I use a pseudo-code syntax because the object here is to supply answers, not write code. You have a mysql tag but the question is not mysql specific, so the pseudo-code seemed sufficient. For simplicity's sake, I like to add "primary key" on the field definition -- but when more than one field, a specific constraint definition works better. If you want to have people here submit answers to your questions, you may want to hold back on criticizing inconsequential details of said answers. – TommCatt Jan 17 '17 at 8:17
  • You have a point in regards to the circular reference. I'll submit a fix for that later...if I feel up to it. – TommCatt Jan 17 '17 at 8:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.