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.
Join tables normally have primary key which is composite : (saleperson_id, lead_id) in your case, each of these columns is also a foreign key to appropriate table. So there is no need in surrogate ID PK in simple cases.
However, you have tasks and person information (details) which are specific to relationship itself and should have FK to saleperson_has_lead (I'd get rid of "has" in table name, but it's outside of question scope). Thus, logically your model looks good to me.
There are some options on how to implement this model :
1. Add a surrogate PK (as you did), but don't forget you still need unique constraint on (saleperson_id, lead_id)
2. Don't add it, but refer to already existing composite PK in saleperson_has_lead table -(saleperson_id, lead_id).
Personally I prefer the first approach because it seems more clear.
Side note.
From what I see many of your entities (lead, saleperson, saleperson_has_lead) storing information about people. You may want to think of adding concept of role to your model because "lead" and "saleperson" are really just roles that a particular person can play in your system.
Best Answer
For practical purposes, if you're implementing a many-to-many relationship with an intersection table - which is by far the most common way to do it, then you will in effect have a "zero or more to zero or more" relationship.
Relational databases don't have declarative referential constraints that enforce the child end of a one to many relationship. In other words, your
CARtable can't have a constraint (imposed by DRI) that enforces 0, 1 or many owners. DRI works on the table with the foreign key, not the parent (referenced) table.This means that unless you implement some additional procedural logic in your application, you wouldn't be able to enforce 1 or more, so you in effect get 0, 1 or more to each of
CARandPERSONfrom the intersection (OWNERSHIP).Implementing a many-to-many relationship in this way is consistent with correct normalization.