Hi,
I have question on usage of composite primary keys. I know it is suggested not to use them for performance reasons (wide indexes). But when it comes to enforcing the integrity constraints, composite keys help me.
Scenario:
I have the following tables.
Projects and Employees.
These tables share a m:n relationship and there is an intermediary table called "ProjectAssignments" with the following structure.
ProjectID, EmployeeID, EmpRole, AssignedBy, AssignedOn. (Primary Key: ProjectID & EmployeeID)
Employees with reviewer role, review the tasks of their reviewees on the specified date. This is captured with the following structure.
ReviewID, ProjectID, EmployeeID_Reviewer, EmployeeID_Reviewee, ReviewDate, TaskDesc. (PrimaryKey: ReviewID)
I would like the design to enforce the constraint that reviewer and reviewee are on the same project. I have defined the foreign keys as follows.
FOREIGN KEY([PRJ_ID], [EMP_ID_Reviewee]) REFERENCES [dbo].[ProjectAssignments]
([AGN_PRJ_ID], [AGN_EMP_ID])
FOREIGN KEY([PRJ_ID], [EMP_ID_Reviewer]) REFERENCES [dbo].[ProjectAssignments]
([AGN_PRJ_ID], [AGN_EMP_ID])
Is this is a good design?
The other alternative I thought has the following design. But, this does not enforce the above mentioned constraint.
Table ProjectAssignments: AssignID, ProjectID, EmployeeID, EmpRole, AssignedBy, AssignedOn. (Primary Key: AssignID)
(Unique Constraint: ProjectID, EmployeeID)
Table ProjectReviews:
ReviewID, AssignID_Reviewer, AssignIDReviewee, ReviewDate, ReviewTask. (Primary Key: ReviewID)
Which one should I prefer and why?
Start Free Trial
