postgresql - Can my ERD have a triangular relationship between 3 entities?

Question

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?

question from:https://stackoverflow.com/questions/65841591/can-my-erd-have-a-triangular-relationship-between-3-entities

Answer 1

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