ERD - 如何用第三个实体作为“属性”来建模两个实体之间的关系

发布于 2024-09-30 16:12:02 字数 231 浏览 7 评论 0原文

我正在对实体关系图进行建模并陷入困境。我不确定我的考虑是否错误,或者 ERD 无法模拟我想要的内容:

我有三个实体:员工、项目和角色。员工和项目之间存在关系:员工正在从事项目。但该员工不仅仅在这个项目上工作,他/她还有一个作为角色指定的操作领域。但关系不就是用属性来描述的吗?我怎样才能做出类似“一名员工在这个项目上工作的身份......”?当然,我使用 roleId 作为属性,因为我将其设计为数据库,但是 ERD 中的关系是什么?

I'm modeling an entity relationship diagram and got stuck. I'm not sure if my considerations are wrong or an ERD can't model what I want:

I have three entities: Employee, Project and Role. There is a relation between Employee and Project: an employee is working on a project. But this employee isn't just working on this project, he/she has a field of operation that is given as a role. But isn't a relation just described by attributes? How can I make something like "An employee works on this project as ..."? Of course I got use a roleId as an attribute as I would design it as a database, but what's the relation in an ERD?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

陌生 2024-10-07 16:12:02

EMPLOYEE

  • 雇员 ID (pk)

项目

  • 项目 ID (pk)
  • 项目描述

角色

  • 角色 ID (pk)
  • 角色描述

如果一名员工每个项目只能拥有一个角色:

EMPLOYEE_PROJECT_MAP

  • 项目 ID (pk, fk 到项目)
  • 雇员 id (pk, fk 到 EMPLOYEE)
  • role_id (fk 到角色) )

如果一个员工每个项目只能有1+个角色:

EMPLOYEE_PROJECT_MAP

  • project_id (pk, fk to PROJECT)
  • employee_id (pk, fk to EMPLOYEE)
  • role_id (pk, fk to ROLE)

两者的区别在于复合主键包含role在后一个版本中。作为所有三列的复合主键,值的组合必须是唯一的,使得以下内容有效:

project_id  employee_id  role_id
---------------------------------
1           1            1
1           1            2

而如果 role_id 是不是包含在复合主键中的用户和项目只能一个组合 - 这意味着一个用户只能拥有一个角色。

CHECK 约束不起作用 - 它只检查行,而不检查整个表。虽然触发器可以工作,但当您可以通过复合主键或唯一约束强制执行关系时,为什么还要麻烦呢?触发器在 ERD 中不可见,在 CREATE TABLEDESC table_name 等语句中也不可见。

EMPLOYEE

  • employee_id (pk)

PROJECT

  • project_id (pk)
  • project_description

ROLE

  • role_id (pk)
  • role_description

If an employee can only have one role per project:

EMPLOYEE_PROJECT_MAP

  • project_id (pk, fk to PROJECT)
  • employee_id (pk, fk to EMPLOYEE)
  • role_id (fk to ROLE)

If an employee can only have 1+ role per project:

EMPLOYEE_PROJECT_MAP

  • project_id (pk, fk to PROJECT)
  • employee_id (pk, fk to EMPLOYEE)
  • role_id (pk, fk to ROLE)

The difference between the two is the composite primary key includes role in the latter version. Being a composite primary key of all three columns, the combination of values must be unique, making the following is valid:

project_id  employee_id  role_id
---------------------------------
1           1            1
1           1            2

Whereas if role_id is not included in the composite primary key, only one combination of user and project can be made - which means a user could only have one role.

A CHECK constraint wouldn't work - it only checks the row, not the entire table. While a trigger would work, why bother when you can enforce the relationship via a composite primary key or unique constraint? A trigger wouldn't be visible in an ERD, nor statements like CREATE TABLE or DESC table_name.

时间你老了 2024-10-07 16:12:02

如何建模关系...实体...属性?

在设计数据库之前,我想将问题建模为实体关系图(使用 Chen 的表示法)。在此图中,我想在员工和项目之间创建关系,而不查看后面的键和约束。
附录:我只知道通过属性扩展的两个实体之间的关系,但是如何建模这种“三实体关系”?

这是完全可以理解的,而且非常正确。纸张很便宜,而数据库中的对象的更改成本要高一些。对需求进行建模并不断改进,直到您有信心为止,然后实施。

许多站点的问题是,有许多木匠,尽管意图良好,但将每个问题视为钉子,并提供 DDL,而不是所要求的建模帮助。缺少的是上下文和含义,因此最终结果是一个具有固定“键”但缺乏上下文和含义的快速实现。建模使我们能够对与我们相关的各个方面进行建模,而无需关心 DDL 中的情况。

另一种说法是,OMG 已经回答了一个问题我如何单独建模“一名员工在这个项目上工作......”?;我正在结合上下文回答您的整个问题。

在逻辑层面上,多对多关系是正确的。这种没有其他考虑的关系在物理层被呈现为关联表。但同样,现在做出决定还为时过早,因为您仍在对关系的上下文和含义进行建模。

...也不在 SO markdown 表示法的范围内提供它。 IME、Oracle Designer 等工具会在您创建实体后生成此类图表

废话。建模的整个理念是在编写一行代码、购买平台或必须实现 DDL 之前,使用图表在纸上开发和改进某些内容。该评论只是关于对许多产品提供的现有数据库进行逆向工程。

建模示例,进展

使用对您有意义的任何符号来建模您需要的内容。当然,标准符号更容易被普遍理解。这是一个
为您提供 ERD
(我不知道“SO markdown notation”如何限制提供事前建模建议)。我提供了一个可能发生的进展示例。没有什么是“对”或“错”,都是纸片;直到决定哪些元素值得确认,然后才有可能进行下一步。

  1. 出发点当然是简单的多对多关系,根据您的标题,您知道一些事情。试图对名义上的三向关系进行建模是不正确的,是一个建模错误:为了解决三角恋,你需要首先分别识别每一方之间的离散关系;这意味着所有关系都是双向的。

  2. 项目、员工和角色实体都很清晰,我们对它们有所了解。这里我没有开发主要实体,因为它们很“强大”,而且它们不是您关注的重点。

  3. 该进程使用示例关系的属性,您可以使用自己的属性。 (我们的比利时同事已经用文字指出了这个问题,我只是用图片来提供。) 在通常的做法中,有很多事情是人们不做的,而他们应该做的;我关心真正的建模,从上到下,以便取得进展并达到正确的数据模型。删除所有垃圾,然后继续前进。

  4. 我假设关系的属性证明了一个实体,所以我现在把它们画进去了。这里我使用了椭圆形,你可以使用菱形或 V 形来满足我的需要,只需使用一些符号来模拟您需要的内容。

  5. 这里我们可以清楚地看到:我们不想要 Project::Employee::Role,因为这将允许 Employee 执行任何角色;我们希望仅当员工之前已被批准担任该角色时才会被选择。因此,Employee::Role 正在变得“更强”。

  6. 因此,Employee::Role 是一个实体。粉红色的事物是该特定组合或 Employee+Role 的子级,而不是所有 Employee 或所有 Role 的子级。

  7. 同样,我们不希望任何员工在任何可能的项目中担任任何可能的工作,我们希望他们只在批准的项目中担任批准的工作。所以 Project::Role 正在成为一个强大的身份,并且无论如何它都有属性。

  8. 因此,Project::Role 是一个实体。剩下的椭圆形是项目+角色的特定组合的子项,而不是所有项目的子项。

  9. 我们的粉红色孩子获得实体状态,具有其特定属性。更重要的是,它的约束来自于之前受约束的实体,而不是简单的实体。

  10. 数据具有自然的顺序或层次结构,考虑到这一点绘制的图表很容易理解。我们现在有机会查看属性。它们可能看起来相同、相似或令人困惑;而现在,由于上下文和层次结构,它们具有明确的含义。

我已经介绍了标识符的概念,没有扩展它,如果有必要的话,我将把它留给讨论。我认为您可以看到标识符实际上非常非常重要,它们作为建模练习的普通部分公开。

一般而言(您的问题,而不是我的示例进展),当我们到达标准化后,最初的三个椭圆可能最终成为一个或两个,或者仍然是三个对象;没有属性的简单关联表;或者作为具有属性的真实实体......但我们不,也不应该现在关心这一点。再说一遍,现阶段对于 DDL 或规范化来说还为时过早。我们不知道钥匙是什么;哪些属性与它们相关;以及与他们有什么关系。更重要的是,我们不在乎。就示例而言,是的,实体是清晰且明确的。

请反馈,以便您能够进步。

编辑:图表已更新,多页。

How to model a relation ... entity ... attribute ?

Before I design the database I want to model the problem as an entity-relationship diagram (using Chen's notation). In this diagram I want to create a relation between employee and project without having a look at the keys and constraints that follow.
Addendum: I just know relations between two entities that are extended by attributes, but how do I model this "three-entity-relation"?

That's completely understandable, and quite correct. Paper is cheap, objects in a database are a bit more expensive to change. Model the requirement and keep improving it, until you are confident, then implement.

The problem with many sites is, there are many carpenters, who although well intentioned, see every problem as a nail, and supply DDL, not the modelling assistance requested. What is missing is context and meaning, so the end result is a hard-and-fast implementation with fixed "keys" but lacking context and meaning. Modelling allows us to model various aspects that are relevant to us, without concern about what that would look like in DDL.

Another way of saying it is, OMG has answered one question how do I model "An employee works on this project as ..."? in isolation; I am answering your entire question in context.

At the logical level, many-to-many relations are correct. Such relations with no other considerations are rendered at the physical level as Associative tables. But again, it is too early to decide that, because you are still modelling the context and meaning of the relations.

... nor is it within the realm of SO markdown notation to provide it. IME, tools like Oracle Designer generate such diagrams after you've created the entities

Nonsense. The whole idea of modelling is to develop and improve something on paper, using diagrams, long before writing a line of code, or buying a platform, or having to implement DDL. The comment is about merely reverse-engineering an existing database, after the fact, which many products provide.

Example of Modelling, Progression

Use whatever symbols are meaningful to you, to model what you need. Of course standard symbols are more universally understood. Here is an
ERD for you
(I have no idea how "SO markdown notation" poses a limitation on providing before-the-fact modelling advice). I have provided a example of the progression that might occur. Nothing is "right" or "wrong", it is all bits of paper; until you decide which elements are worth confirming, and then the next progression is possible.

  1. The starting point is of course, simple many-to-many relations, that you know some things about, as per your title. Trying to model a notional three-way relation is incorrect, a modelling error: in order to resolve a love triangle you need to first identify the discrete relations between each of the parties, separately; that means all relations are two-way only.

  2. The Project, Employee and Role Entities are clear, and we know something about them. Here I have left the major Entities undeveloped, because they are "strong", and they are not what you are focussing on.

  3. The progression uses example attributes of a relation, you can use your own. (Our Belgian colleague has already identified the issue in words, I am merely providing it in pictures.) There is a lot that people do not do in common practice, that they should do; I am concerned about true modelling, from the top, down, in order to progress and arrive at the correct data model. Remove anything that is rubbish, and continue progressing.

  4. I've made assumptions that the attributes of the relation justify an Entity, so I have now drawn them in. Here I have used ovals, you can use diamonds or chevrons for all I care, just use some symbol, to model what you need.

  5. Here comes the point where we can clearly see: we do not want Project::Employee::Role, because that would allow an Employee to perform any Role; we want Employees to be selected only if they have previously been approved for that Role. So, Employee::Role is becoming "stronger".

  6. Therefore, Employee::Role is an Entity. And the pink Thing is a child of that specific combination or Employee+Role, not of all Employee or of all Role.

  7. Likewise, we do not want any Employees to take any possible job in any possible Project, we want them to take only approved jobs in approved Projects. So Project::Role is becoming a strong identity, and it has attributes anyway.

  8. Therefore, Project::Role is an Entity. And that remaining oval is a child of that specific combination of Project+Role, not of all Project.

  9. Our pink child attains Entity status, with its specific attributes. More important, its constraints are derived from previously constrained Entities, not simple ones.

  10. Data has a natural order or hierarchy, and a diagram drawn with that in mind is a lot easily to understand. We now have the opportunity to look at the attributes. They may have seemed the same or alike or confusing; whereas now they have clear meaning, due to context and hierarchy.

I have introduced the concept of Identifiers, without expanding it, I will leave that for discussion, if it is necessary. I think you can see that Identifiers are actually very, very important, and they are exposed as an ordinary part of the modelling exercise.

In general terms (your question, as opposed to my example progression), When we get to Normalisation, the three initial ovals may end up as one or two or remain as three objects; simple Associative tables with no attributes; or as true Entities with attributes... but we do not, and should not care about that right now. And again, it is too early for DDL, or for Normalisation at this stage. We have little idea what the keys are; what attributes are associated with them; and in what relationship to them. What's more, we don't care. In terms of the example, yes, the Entities are clear and unambiguous.

Feedback please, so that you can progress.

Edit: Diagram updated, multi-page.

寄离 2024-10-07 16:12:02

“在设计数据库之前,我想将问题建模为实体关系图(使用 Chen 的表示法)。在该图中,我想在员工和项目之间创建关系,而无需查看后面的键和约束。”

如果两者(员工和项目)之间的“有效”关系是多对多,并且该关系具有描述(/提供有关)该关系(发生)的更多详细信息的进一步属性,那么您通常没有其他属性选择但“实例化”关系,即将其定义为额外的实体。一些工具支持 ERD 方言,允许为任何关系指定附加属性(在指向关系箭头的圆形框中),但在我看来,这不是常见的做法。

"Before I design the database I want to model the problem as an entity-relationship diagram (using Chen's notation). In this diagram I want to create a relation between employee and project without having a look at the keys and constraints that follow."

If the relationship "works-on" between the two (Employee and project) is many-to-many, AND that relationship has further attributes describing(/providing further detail about) (occurences of) the relationship, then you often have no other choice but to "instantiate" the relationship, i.e. defining it as an extra entity. Some tools support an ERD dialect that allows specifying additional attributes for any relationship (in a rounded box arrowing to the relationship arrow), but this is imo not common practice.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文