var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Referential integrity and subsets
I'm working on a schema in which the following kind of dependency occurs a lot. I'm not even sure what to call it, which has made searching for discussion difficult.
Informally, I have companies which contain employees as well as teams. Teams group employees, but here's the problem: I want to ensure that teams only group employees from the same company.
employees (employee_id -> company_id)
teams (team_id -> company_id)
team_members (team_id, employee_id)
One approach is to incorporate the company_id into team_members:
This way, I can enforce referential integrity:
team_members (team_id, employee_id, company_id)
However, it breaks 2NF and complicates my schema significantly. I frequently have to incorporate fields into every level of a hierarchy down to the level where I need it. For example:
CONSTRAINT tc FOREIGN KEY (team_id, company_id) REFERENCES teams (team_id, company_id),
CONSTRAINT ec FOREIGN KEY (employee_id, company_id) REFERENCES employees (employee_id, company_id)
Companies have projects, projects are decomposed into modules, modules into tasks, and each task is allocated to a specific employee. In order to enforce that the employee allocated belongs to the company owning the project, I have to 'carry' company_id into tasks via modules, e.g.
projects (project_id -> company_id)
modules (module_id -> project_id)
tasks (task_id -> module_id, employee_id)
And so on. Am I just being stupid and/or overcomplicating things? Is there a better way?
modules (module_id -> project_id, company_id)
CONSTRAINT mc FOREIGN KEY (project_id, company_id) REFERENCES projects (project_id, company_id),
tasks (task_id -> module_id, employee_id, company_id)
CONSTRAINT kc FOREIGN KEY (module_id, company_id) REFERENCES modules (module_id, company_id),
CONSTRAINT ec FOREIGN KEY (employee_id, company_id) REFERENCES employees (employee_id, company_id),
Tags for this Thread