Constraint many to many table between two child tables

Question

New to this exchange site so I hope my question isn't inappropriate.

Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently.

One table has a One-To-Many relationship with two other tables. These two tables then have a Many-To-Many relationship between them. Shouldn't I somehow ensure (other then in the programming insertion) that the items that are linked together belong to the same parent table.

Let's put it in a Scenario (This is a fictional scenario):

Division table where each row is a division in a company. It has One-To-Many relations with a Employee table. So each employee belongs to one and only one division.

The Division table also has One-To-Many relations with a Project table where specific projects are logged. The project also has to register how many hours a employee has logged with this specific project. I would do this with a Many-To-Many table between Employee and Project and a Hours column.

This should all work fine ( I think at least) except that theoretically nothing in the database would ensure that the Employee and Project belong to the same Division even when they should and in fact both belong to a entity in that table.

What is a good way to ensure the intention of this schema and the integrity of the data is handled by some abstract relational database. Would you construct the database in any other way or would you put some constraint on the Many-To-Many relationship?

Answer 1

Why must the employees working on a project strictly belong to the same division that owns the project? That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI. You said you have a many-to-many table associating employees with projects. Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID) 
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID) 
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

• We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
• These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
• The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.

Answer 2

nothing in the database would ensure that the Employee and Project belong to the same Division even when they should and in fact both belong to a entity in that table.

Based on this statement, I am assuming that an Employee and a Project are permanently tied to a Division. In which case you should consider using a composite key for the PK:

--postgres DDL for example:
create table division         ( division_id serial primary key );

create table employee         ( division_id integer references division, 
                                employee_id serial unique,
                                primary key (division_id, employee_id) );

create table project          ( division_id integer references division, 
                                project_id serial unique,
                                primary key (division_id, project_id) );

create table employee_project ( division_id integer, 
                                employee_id integer, 
                                project_id integer,
                                primary key (division_id, employee_id, project_id),
                                foreign key (division_id, employee_id) references employee,
                                foreign key (division_id, project_id) references project );

Modelled like this:

• the constraint that employees can only work on projects with matching divisions comes free
• the model is more rigid when it comes to employees/projects shifting between divisions (which may or may not be a good thing depending on your real requirements).