0

Project --- Skill --- Employee

A project requires many skills. A skill can be required for many projects.

An employee can have many skills. A skill can be 'had' by many employees.

Is this information enough in order to deduct which employees are in which projects? Or should there be another relationship between Projects and Employees?

Like this: A project can have many employees. An employee can be a part of many projects.

So it would basically become a triangle of these 3 entities, with weak entities in-between? Or is there a better solution?

radrow
  • 6,419
  • 4
  • 26
  • 53
Javid Isayev
  • 9
  • 1
  • 2
    I would add another relation between project and employee. Just because an employee has the necessary skills doesn't automatically mean she/he is part of the project. After all there is a natural upper limit on how many projects an employee can work on. –  Jan 22 '21 at 08:29
  • 1
    It depends on what you're trying to model, but the project to employee relationship is the primary relationship. Project to skill and employee to skill are secondary many-to-many relationships. – Gilbert Le Blanc Jan 22 '21 at 10:23
  • A ternary relationship could be defined for Employee, Skill, and Project. But I'm at a loss to figure out what that relationship would mean in the subject matter. What are you trying to model? – Walter Mitty Jan 22 '21 at 13:06
  • Similar problem: https://stackoverflow.com/questions/13079586/modeling-3-entities-with-relationships/13089670#13089670 – Damir Sudarevic Jan 22 '21 at 14:37

1 Answers1

0

ERDs are great if you already grok how to automatically verbalize diagrams. When in doubt use plain text: predicates, constraints, relations. Better and more powerful than ERDs.

-- Project PRO exists.
--
project {PRO}
     PK {PRO}

-- Employee EMP exists.
--
employee {EMP}
      PK {EMP}

-- Skill SKL exists.
--
skill {SKL}
   PK {SKL}

-- Employee EMP has skill SKL.
--
emp_skl {EMP, SKL}
     PK {EMP, SKL}

FK1 {EMP} REFERENCES employee {EMP}
FK2 {SKL} REFERENCES skill    {SKL}

-- Project PRO requires skill SKL.
--
pro_skl {PRO, SKL}
     PK {PRO, SKL}

FK1 {PRO} REFERENCES project {PRO}
FK2 {SKL} REFERENCES skill   {SKL}

-- Employee EMP with skill SKL, is assigned
-- to project PRO, that requires that skill.
--
emp_pro {EMP, PRO, SKL}
     PK {EMP, PRO}

FK1 {EMP, SKL} REFERENCES emp_skl {EMP, SKL}
FK2 {PRO, SKL} REFERENCES pro_skl {PRO, SKL}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71