Relationship between 3 objects in an ER diagram - triple enough or are two binaries needed?

TL; DR You need all three types of relations / tables. Because if you throw it, then in some situations you lose data - there is no way to use the remaining answers to all the same questions.

---

Each relationship table contains rows that are involved in this relationship. We can describe the relationship through a predicate (assertion pattern):

1 Divides_to

contains lines (T, P)

where project P divides to task T


2 Has

contains (E, P)

lines where employee E is assigned to project P


3 contains lines (E, T, P)

whereemployee E is assigned to task T on project P

Can I reset 1? If we ignore employees at 3, we get lines where some employee is assigned to task T on project P

. But (as per above), not lines in 1. Perhaps project P is divided by task T in 1, but no employee is assigned to task T in project P; then this string at 1 is not a substring at 3. And there is no information about the problem in 2. Therefore, we cannot use 3 and 2 to replace 1.

Can I reset 2? Likewise, if we ignore tasks at 3, we don't get 2, and there is no employee information at 1. Therefore, we cannot use 3 and 1 to replace 2.

Can I fold 3? Using 1 and 2, we can get the lines where employee E is assigned to project P AND project P divides to task T

. But (as per above) that are not lines in 3. They differ if the employee assigned to the project is not assigned to all of his tasks, or if the project task does not have all of his employees assigned to him. There is no other way to generate 3 from 1 and 2. Therefore, we cannot use 1 and 2 to replace 3.

So we need all three relationships.

---

PS 1 Also we need entity types / tables, not just relationship types / tables. (If we didn't want them to be tied to intrinsic attributes anyway or just ER modeling conventions). For example, three relationships cannot tell you about employees who are not assigned to a project or task and project. Likewise for tasks and projects.

PS 2 We ignore the attribute in relational algebra, but not project

on it. We are ignoring the SQL column, not select

. The result predicate is that the old predicate is executed for SOME value for the attribute / column. Relational natural join

gives rows whose relations / predicates are the AI ​​of the input relations / predicates. There are no duplicate rows in SQL and no common nullable columns that select distinct

from

natural join

.

PS 3 Your design satisfies certain constraints: if a pair of project tasks appears in 3, it should appear in 1, and if an employee-project pair appears in 3, then it should appear in 2. One of the ways to reflect this in ER modeling is by combining project-to-project and employee-to-project relationships with associative objects, and then replacing 3 with ER causing a binary link to objects. In reality the relation / table is still threefold, where certain bugs identify these entities. The way to obtain a limited relational binary code 3 is to add a PK identifier (primary key) of a project employee or a CK (candidate) key in 2 and replace such a complex FK (foreign key) in 3 with such an identifier. Then we have a binary for entities and values.Several pseudo-ER methods do this.

PS 4 This (true Chen) ER chart style generally does not use null SQL values. But, as it happens, you can replace all three relationships in option 3 with zeros. You would null

extend the binary relationship and union

with the ternary. As usual, zeros complicate predicates. We usually add a nullable column as an alternative to adding a separate table that uses a null CK (candidate key). But this is different, without saving space or combining; it just complicates things. (Including important restrictions.)

    E IS NULL AND task T is of project P
OR  T IS NULL AND employee E is assigned to project P
OR  employee E is assigned to task T of project P

      

        
        
        
      

    

(Also it is problematic in SQL because SQL unique

, primary key

and join

are not relational things by those names because they specifically refer to null

.)

PS 5 Some answers to my such ternary and binary relations (ships) types / tables / predicates:
If this ER diagram uses triple relations instead of The
best solution is triple or binary relations
Why can't you just join a fan? Both design and predicates:
Is there any rule of thumb for creating an SQL query from a human-readable description?

PS 6 Has

is a uselessly generic name / value / relationship table. Use meaningful names such as Is_assigned_to

or Assignment

.