ER图中3个实体之间的关系-是否足够三元或还需要2个二进制？ [英] relationships between 3 entities in ER diagram--is a ternary enough or are 2 binaries also needed?

问题描述

我正试图为我的项目管理软件
绘制一张ER图，描述以下内容。它包含以下实体：

I'm trying to draw an ER diagram for my project management software describing the following. It contains these entities:

• 项目-软件项目


• 任务-可以破坏的软件项目


• 员工-属于该软件的员工

并且： / p>

And:

1. 项目可以分为任务。
   （任务可以由管理员用户创建，管理员用户可以将这些任务分配给选定的项目。这里仅将任务分配给项目，而没有将员工分配给项目。）

1. A project can be divided into tasks. (Tasks can be created by the admin user, who can assign those tasks to selected projects. Here there is only assignment of tasks to projects, not assignment of employees to projects.)

可以将员工分配到项目中。

（可以将员工分配到项目中。这里仅将员工分配到项目中，而没有分配到项目任务中。）

Employees can be assigned to projects.
(An employee can be assigned to projects. Here there is only assignment of employees to projects, not assignment to tasks of projects.)

对于选定项目的选定任务，我们可以从资源池中分配雇员-在2中分配给该项目的雇员。
（这次我们必须指定项目，任务和雇员；所有3个选择都是必需的。）

For a selected task of a selected project we can assign employees from the pool--employees that are assigned to that project in 2. (This time we must specify project, task & employee; all 3 selections are mandatory.)

1、2&以上3可以在系统的单独页面中完成。
您可以先选择其中的任何一个。

The input processes for 1, 2 & 3 above can be done in separate pages in the system. You can select any of them first.

对于上述关系，我创建了此ERD：

For the above relationships I created this ERD:

考虑

• 项目与任务之间的关系1


• 项目与员工之间的关系2

需要两个独立的关系（如ER图所示），
关系为1&关系2？

Is there a need for the two separate relationships as in the ER diagram, relationship no 1 & relationship no 2?

或

我们能否仅使用项目3，员工和任务之间的关系3？ 3，还可以吗？

can we use only relationship 3 among project, employee and task, relationship no 3, for that also?

推荐答案

TL; DR 您需要所有三种关系类型/表。因为如果您丢掉一个字，那么在某些情况下会丢失数据-无法使用其余的字回答所有相同的问题。

TL;DR You need all three relationship types/tables. Because if you drop one then in some situations you lose data--there is no way to use the remaining ones to answer all the same questions.

不同的约束可能意味着我们可以删除一个关系/表，因为它可以用其他形式表达。对较高NF（正常形式）的归一化告诉我们何时可以用较小/较简单的NF替换关系/表。

Different constraints could mean we can drop a relationship/table because it can be expressed in terms of others. Normalization to higher NFs (normal forms) tells us when we can replace a relationship/table by smaller/simpler ones.

每个关系表都包含参与关系的行。我们可以通过谓词（语句模板）来描述这种关系：

Each relationship table holds the rows that participate in the relationship. We can describe the relationship via a predicate (statement template):

1 Divides_to 持有（T，P）行，其中项目P划分为任务T

2 有持有（E，P）行，其中雇员E被分配给项目P

3持有（E，T，P）行，其中雇员E被分配给项目P上的任务T

1 Divides_to holds (T, P) rows where project P divides to task T
2 Has holds (E, P) rows where employee E is assigned to project P
3 holds (E, T, P) rows where employee E is assigned to task T on project P

我们可以丢1吗？如果我们忽略3中的员工，则将得到行，其中一些员工被分配给项目P上的任务T 。但是（按上）不是1中的行。也许项目p1在1中被划分为任务t1，但是没有雇员被分配到项目p1中的任务t1；那么1中的（t1，p1）行不是3中的子行。2中也没有任务信息，因此我们不能使用3& 2代替1。

Can we drop 1? If we ignore employees in 3 then we get rows where some employee is assigned to task T on project P. But (per above) that is not the rows in 1. Maybe project p1 divides to task t1 in 1 but no employee is assigned to task t1 on project p1; then row (t1, p1) in 1 is not a subrow in 3. And there is no task info in 2. So we can't use 3 & 2 to replace 1.

我们可以放弃2吗？同样：如果忽略3中的任务，则将获得行雇员E被分配给项目P 上某些任务的行。但是（按上）不是2中的行。也许雇员e1被分配给项目p1，但没有分配给项目p1上的任务；那么2中的（e1，p1）行不是3中的子行。1中也没有员工信息。因此我们不能使用3& 1代替2。

Can we drop 2? Similarly: If we ignore tasks in 3 then we get rows where employee E is assigned to some task on project P. But (per above) that is not the rows in 2. Maybe employee e1 is assigned to project p1 but is not assigned to a task on project p1; then row (e1, p1) in 2 is not a subrow in 3. And there is no employee info in 1. So we can't use 3 & 1 to replace 2.

我们可以放弃3吗？使用1& 2我们可以得到以下行，其中将雇员E分配给项目P，而项目P划分到任务T 。但是（上方）不是3中的行。如果没有将分配给项目的雇员分配给所有任务，或者没有为项目的任务分配所有雇员，则它们会有所不同。没有其他方法可以从1& 2.所以我们不能使用1& 2代替3。

Can we drop 3? Using 1 & 2 we can get rows where employee E is assigned to project P AND project P divides to task T. But (per above) that is not the rows in 3. They differ if an employee assigned to a project isn't assigned to all its tasks or if a task of a project doesn't have all its employees assigned to it. There's no other way to generate 3 from 1 & 2. So we can't use 1 & 2 to replace 3.

所以我们需要所有三个关系。

So we need all three relationships.

当约束成立时，某些查询表达式总是返回与某些其他结果相同的结果，否则将不会。因此，在不同的约束下，我们可以删除关系/表，因为我们可以通过其他人的查询/视图来表达其内容。我们可能会选择不同的关系/表。

When constraints hold, certain query expressions always return the same results as certain others that otherwise wouldn't. So under different constraints we might be able to drop a relationship/table because we can express its content via queries/views of others. And we might choose different relationships/tables.

对较高NF的归一化指导将关系分解为更简单的其他关系，通过该关系可以代替某些约束来表达该关系。

Normalization to higher NFs guides decomposing a relationship into simpler others by which it can be expressed instead per certain constraints.

这就是为什么我们需要实体类型/表而不仅仅是关系类型/表的原因。 （如果我们无论如何都不希望它们具有特定于实体的属性或仅是ER建模约定。）例如，这三个关系无法告诉您未分配给项目或任务和员工的员工。项目。对于任务&

PS 1 That's also why we need the entity types/tables and not just the relationship types/tables. (If we didn't want them anyway for entity-specific attributes or just ER modeling conventions.) Eg the three relationships can't tell you about employees that aren't assigned to a project or to a task & project. Similarly for tasks & for projects.

PS 2我们忽略了关系代数中的一个属性，因为它不是 project ing。我们不通过 select 忽略SQL中的列。结果的谓词是属性/列的FOR SOME值，旧的谓词成立。关系自然联接给出其关系/谓词为输入关系/谓词的AND的行。在SQL中，没有重复的行&没有从选择不同的 来自 自然联接的共享的可为空的列。

PS 2 We ignore an attribute in relational algebra by not projecting on it. We ignore a column in SQL by not selecting it. The result's predicate is that FOR SOME value for the attribute/column, the old predicate holds. Relational natural join gives the rows whose relationship/predicate is the AND of input relationships/predicates. In SQL for no duplicate rows & no shared nullable columns that's select distinct from natural join.

PS 3按照常识，您的设计满足某些约束：如果一个任务项目对出现在3中，则它必须出现在1中，如果一个雇员项目对出现在1中。 3，则它必须出现在2中。在ER建模中反映这一点的一种方法是通过对任务项目&员工与关联实体之间的项目关系，然后用ER在这些实体上的二元关系代替3。在关系上，关系/表仍然是值的三元组，其中某些子行恰好标识了那些实体。获取受约束的相关二进制3的一种方法是在2中添加一个雇员项目PK（主键）或CK（候选键）id，并用这种id替换3中的复合FK（外键）。然后我们有一个关于实体和值的二进制文件。 PS 4这种类型的（真正的Chen）ER图通常不使用SQL空值。

PS 3 Under common sense your design satisfies certain constraints: If a task-project pair appears in 3 then it must appear in 1 and if an employee-project pair appears in 3 then it must appear in 2. One way to reflect that in ER modeling is by reifying the task-project & employee-project relationships to associative entities then replacing 3 by a what ER calls a binary relationship on those entities. Relationally, the relationship/table is still ternary on values, where certain subrows happen to identify those entities. A way to get a constrained relationally binary 3 is to add an employee-project PK (primary key) or CK (candidate key) id in 2 and replace the composite FK (foreign key) in 3 by such an id. Then we have a binary on entities and on values. Some pseudo-ER methods do this.

PS 4但是，当发生这种情况时，您可以将所有三个关系替换为3（带空值）。您将 null -扩展二进制关系，并使用三元数<< c $ c> union 。像往常一样，null使谓语复杂化。通常，我们添加一个可为空的列作为添加共享无空CK（候选关键字）的单独表的替代方法。但这是不同的，没有节省空间或连接。它只会使事情复杂化。 （包括重要的约束。）

PS 4 This style of (true Chen) ER diagram doesn't typically use SQL nulls. But as it happens you could replace all three relationships by a variant of 3 with nulls. You would null-extend the binary relations and union them with the ternary. As usual, nulls complicate predicates. Usually we add a nullable column as an alternative to adding a separate table sharing a null-free CK (candidate key). But this is different, without the savings in space or joins; it just complicates things. (Including important constraints.)

    E IS NULL
AND task T is of project P
AND NOT EXISTS E [employee E is assigned to task T of project P]
OR  T IS NULL
AND employee E is assigned to project P
AND NOT EXISTS T [employee E is assigned to task T of project P]
OR  employee E is assigned to task T of project P

（也这在SQL中是有问题的，因为SQL 唯一，主键和 join 不是这些名称的关系事物，因为它们特别对待 null 。）

(Also it's problematic in SQL because SQL unique, primary key & join are not the relational things by those names because they treat null specially.)

PS 5一些答案我的这样的三元关系与二进制关系（船舶）类型/表/谓词：

此ER图是否应使用三元关系代替

最佳解决方案-三元或二进制关系

为什么您不能只加入粉丝陷阱？

并重新设计&谓词：

对关系数据库中相同实体之间的多对多关系进行建模

实体关系模型和关系模型之间有什么区别？

< a href = https://stackoverflow.com/a/33952141/3404097>是否有任何经验法则可以从易于理解的描述中构造SQL查询？

PS 5 Some answers of mine re such ternary vs binary relation(ship) types/tables/predicates:
Should this ER diagram use a ternary relationship instead
Best Solution - Ternary or Binary Relationship
Why can't you just join in fan trap?
And re design & predicates:
Modeling multiple many to many relationships between the same entities in a relational database
What is the difference between an entity relationship model and a relational model?
Is there any rule of thumb to construct SQL query from a human-readable description?

PS 6 有是一个无用的通用关系名称/含义/表。使用有意义的名称，例如 Is_assigned_to 或 Assignment 。

PS 6 Has is an unhelpfully generic relationship name/meaning/table. Use meaningful names like Is_assigned_to or Assignment.

这篇关于ER图中3个实体之间的关系-是否足够三元或还需要2个二进制？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！