Search code examples
databasedata-modelingconceptualternary

Conceptual data model using ternary relationship

I have the following statements to create my conceptual model:

  • EMPLOYEE belongs to one DEPARTMENT;
  • EMPLOYEE can work in many PROJECTs of his DEPARTMENT;
  • PROJECT is managed by only one DEPARTMENT;
  • DEPARTMENT can have many PROJECTs;

So 1 EMPLOYEE can work in N PROJECTs as long as those PROJECTs belongs to his DEPARTMENT. How can i guarantee that the EMPLOYEE and the PROJECT belongs to the same DEPARTMENT using a ternary relationship?

Making some test I found out that using a ternary relantionship I can have an EMPLOYEE in a PROJECT that does not belongs to the same PROJECT Example:

enter image description here

As shown in the image, the second and third line is allowed, but it is not valid considering that the PROJECT and the EMPLOYEE is from DEPARTMENT 1. The second line says that the EMPLOYEE is from DEPARTMENT 2 but it's previously define DEPARTMENT 1. The third line says PROJECT 1 is from DEPARTMENT 2 but it is also defined to be from DEPARTMENT 1 in the first line. What do i do to solve this problem?

Solution

  • You said EMPLOYEE belongs to one DEPARTMENT and PROJECT is managed by only one DEPARTMENT but didn't model those requirements. Once you do, you can add foreign key constraints to WORK for (EMPLOYEE, DEPARTMENT) as well as (PROJECT, DEPARTMENT).