Enforce referential integrity in a ternary relation

user_14798970 0
Viewed 84

Question

Employees are organized into teams. Each team can have multiple employees, and each employee can belong to multiple teams. This many-to-many relationship is represented by the team_membership table.

Each project is assigned to one team. Projects are subdivided into tasks, and each task is assigned to an employee.

Is it possible to guarantee that a task's employee is a member of the corresponding project's team, without adding triggers or redundant columns?

Example tables

CREATE TABLE employee 
  ( 
     employee_id   bigserial PRIMARY KEY, 
     employee_name text 
  ); 

CREATE TABLE team 
  ( 
     team_id   bigserial PRIMARY KEY, 
     team_name text 
  ); 

CREATE TABLE team_membership 
  ( 
     team_id     bigint NOT NULL REFERENCES team, 
     employee_id bigint NOT NULL REFERENCES employee, 
     PRIMARY KEY (team_id, employee_id) 
  ); 

CREATE TABLE project 
  ( 
     project_id   bigserial PRIMARY KEY, 
     team_id      bigint NOT NULL REFERENCES team, 
     project_name text 
  ); 

CREATE TABLE task 
  ( 
     task_id     bigserial PRIMARY KEY, 
     task_name   text, 
     project_id  bigint NOT NULL REFERENCES project, 
     employee_id bigint NOT NULL REFERENCES employee 
  );

What I have already tried

Use a trigger to check validity when data changes. This would require writing similar trigger procedures for the employee and team_membership tables.

CREATE FUNCTION check_employee_member_of_team() RETURNS trigger AS $$
  BEGIN
    IF NOT new.employee_id IN (
      SELECT employee_id FROM team_membership tm
      JOIN project pr ON tm.team_id = pr.team_id
      WHERE pr.proejct_id = new.project_id
    ) THEN
        RAISE EXCEPTION 'Employee is not a member of project';
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_or_update_task_trigger
    BEFORE INSERT OR UPDATE ON task
    FOR EACH ROW EXECUTE PROCEDURE check_employee_member_of_team();

Add a team_id column to the task table, and enforce the constraint using composite foreign keys. project.team_id and task.team_id are redundant.

CREATE TABLE project 
  ( 
     project_id   bigserial PRIMARY KEY, 
     team_id      bigint NOT NULL REFERENCES team, 
     project_name text, 
     UNIQUE (project_id, team_id) 
  ); 

CREATE TABLE task 
  ( 
     task_id     bigserial PRIMARY KEY, 
     task_name   text, 
     project_id  bigint NOT NULL, 
     team_id     bigint NOT NULL, 
     employee_id bigint NOT NULL REFERENCES employee, 
     FOREIGN KEY (project_id, team_id) REFERENCES project (project_id, team_id), 
     FOREIGN KEY (team_id, employee_id) REFERENCES team_membership (team_id, employee_id) 
  );
database-normalization postgresql foreign-keys sql
3 Answers

Add a redundant team_id to task and add the new column to the foreign key referencing project. This requires a redundant unique constraint on project(team_id, project_id).

Then create a foreign key constraint from task to team_membership. Then the task can only be assigned to a team member.

user_64643080

The following solution, a variation on that of @LaurenzAlbe, reduces the redunduncy while maintaining all the constraints:

employee (empl_id, empl_name), primary key empl_id

team (team_id, team_name), primary key team_id

team_membership (empl_id, team_id),
  primary key (empl_id, team_id),
  foreign key empl_id references employee,
  foreign key team_id references team

project (project_num, team_id, project_name)
  primary key(project_num, team_id)
  foreign key team_id references team

task (task_num, project_num, team_id, empl_id, task_name)
  primary key(task_num, project_num, team_id)
  foreign key (project_num, team_id) references project,
  foreign key (empl_id, team_id) references team_membership

Note that project_num is a sequential number internal to each task (so different tasks can have the same set of numbers for their projects), while task_num is a sequential number internal to each project.

Finally, note that, as for the comment of @DamirSudarevic, the foreign key on team_membership is enough to ensure that empl_id and team_id reference to an existing employee and team, respectively.

user_23827340

Normalisation to the n-th degree might not always be a very good idea. Storage is far cheaper now than days of Boyce–Codd. The cost of additional complexity and operational costs are far higher by orders of magnitude. Avoiding triggers and such native features will make the solution less portable - lets say in a few years from now your business was super successful and you have to scale this out into a no-sql solution or a platform where triggers are not supported

It might be wise to drop the purist pursuit of too much normalisation and look at more pragmatic solution. Then there is many ways to skin this cat by using foreign keys alone. Other answers are also good in this respect. One way I can think is below and I haven't denormalised too much yet:

CREATE TABLE employee 
  ( 
     employee_id   bigserial PRIMARY KEY, 
     employee_name text 
  ); 

CREATE TABLE team 
  ( 
     team_id   bigserial PRIMARY KEY, 
     team_name text 
  ); 

CREATE TABLE team_membership 
  ( 
     team_id     bigint NOT NULL REFERENCES team, 
     employee_id bigint NOT NULL REFERENCES employee, 
--     PRIMARY KEY (team_id, employee_id) 
     teamp_membership_id bigserial PRIMARY KEY
  ); 

CREATE TABLE project 
  ( 
     project_id   bigserial PRIMARY KEY, 
--     team_id      bigint NOT NULL REFERENCES team, 
     project_name text 
  ); 

CREATE TABLE project_team
  (
     project_team_id bigserial PRIMARY KEY, 
     project_id   bigint REFERENCES project, 
     teamp_membership_id bigint REFERENCES team_membership
  ); 


CREATE TABLE task 
  ( 
     task_id     bigserial PRIMARY KEY, 
     task_name   text, 
     project_team_id  bigint NOT NULL REFERENCES project_team
  );

Surrogate key to team_membership called team_member_id Remove team_id from project table Create project-team mapping tables because probably not all employees in a team may be in a project. Also modify the task table.

user_61057190
Related