主页 / dba / 问题 / 112576
Accepted
Luciano Andress Martini
Asked: 2015-08-29 11:51:37 +0800 CST

实体关系问题

  • 772

我有 4 个这样的表（这是一个例子）：

Company:
ID
Name
CNPJ

Department:
ID
Name
Code
ID_Company 

Classification:
ID
Name
Code
ID_Company

Workers:
Id 
Name
Code
ID_Classification
ID_Department

假设我有一个classificationwith id = 20, id_company = 1。还有一个department有id_company = 2（代表另一家公司）。

这将允许创建来自两家公司的工人，因为分类和部门分别链接到公司。我不希望这种情况发生，所以我觉得我的人际关系有问题，我不知道如何解决。

database-design constraint

4 个回答

  1. 我不认为你的人际关系有问题。相反，我认为问题在于，通过为每个表使用代理键（即 ID），生成的数据库无法阻止插入其部门属于一家公司而分类属于另一家公司的工人，反之亦然。理解这一点的一个好方法是使用 ER 图表工具可视化模式。我将使用可免费下载的Oracle Data Modeler工具。

    ER图

    在此处输入图像描述

    就目前而言，您可以拥有 2 家公司 - 比如说IBM和Microsoft。 IBM可以有Software Development部门，微软也可以有Desktop Software部门。IBM可以有一个Software Engineer分类，微软可以有一个Software Developer分类。Department现在，因为您有一个和的代理键，所以对于未来的子关系， is an department 和is a departmentClassification的事实将丢失。也是这种情况。因此很容易不小心分配，谁是部门的员工，分类是Software DevelopmentIBMDesktop SoftwareMicrosoftClassificationHarlan MillsIBMSoftware DevelopmentSoftware DeveloperMicrosoft分类！同样，工人可能被赋予正确的分类和错误的部门！这是显示第一个示例的图表：

    在此处输入图像描述

    1个Id代表IBM，2个Id代表Microsoft。我用红色突出显示了Harlan Mills和Bill Gates被分配到错误部门的场景，这通过与 200 分类 ID 关联的 10 个部门 ID 可视化，反之亦然。

    要解决的选项

    那么有什么选择可以防止他的事情发生呢？有两个即时选项。第一个是通过为每个表使用代理键来意识到这个问题的存在，并引入额外的编程来验证它不会发生。这可以在应用程序中完成，但如果插入和更新可以发生在应用程序之外，那么不正确的关联仍然可能发生。更好的方法是创建一个在插入和更新员工时触发的触发器，以确保分配的部门与分配的分类属于同一家公司，如果插入或更新没有失败。

    第二种选择是不对每个表使用代理键。相反，仅对Company没有父表的基本表使用代理键，然后创建与子表的标识关系。和表现在有一个 PK加上一个序列号或名称来区分它们。然后，from和to的关系也变成了，因此 PK of变成了，加上（我在这个例子中使用的是序列号），再加上。结果是只有在表中。现在不可能分配一个DepartmentClassificationDepartmentClassificationCompany IdDepartmentClassificationWorkeridentifyingWorkerCompany IdDepartment NumberClassification Numberone Company IdWorkerWorker到Department一个Company和Classification另一个Company。

    为什么这是不可能的？Worker这是不可能的，因为模式在和Department之间实现了参照完整性Classification。如果尝试在一个和另一个中插入一个Workerfor a ，则相应父表中不存在的组合将触发引用完整性冲突，并且插入将不起作用。DepartmentCompanyClassification

    这是第二个选项的实现的更新图： 在此处输入图像描述

    首选方案

    在这两个选项中，出于两个原因，我绝对更喜欢第二个 - 使用标识关系和级联键。首先，此选项无需额外编程即可实现所需规则。开发触发器并非易事。它必须被编码、测试和维护。确保触发逻辑是最佳的，以免影响性能也很重要。Applied Mathematics for Database Professionals一书详细介绍了此类解决方案的复杂性。其次，规则暗示 Department 和 Classification不能存在于 的上下文之外Company，因此架构现在更准确地反映了现实世界。

    这是一个很好的问题，因为它准确地说明了为什么简单地假设每个表都需要一个代理键是一个坏主意。 Fabian Pascal就此主题发表了一篇出色的博客文章，表明从数据完整性的角度来看，代理键不仅是个坏主意，还会导致某些检索变慢在物理层面上，正是因为需要连接，如果键被正确级联，就没有必要了。这个问题揭示的另一个有趣的话题是，数据库无法确保插入其中的所有数据相对于现实世界都是准确的。相反，它只能确保插入其中的数据与向它声明的规则一致。在这种情况下，我们可以通过使用级联键方法来确保DBMS 可以根据规则保持数据的一致性，即 a Workerof a given Companyneeds to be assigned a Classificationand a Departmentof that same Company。但是，如果在现实世界Microsoft中有一个名为的部门Desktop Software，但数据库的用户断言该部门是Software DevelopmentDBMS 什么也做不了，只能假设它已经被赋予了一个真实的事实。

    • 26
  2. Best Answer

    您的问题源于您的模型中缺少实体类型这一事实。考虑以下 ERD：

    研发部

    请注意，我在和之间添加了一个交集实体类型。这个新的实体类型：提供模型中隐含的信息，即特定部门有一组给定的各种分类的工作。DEPARTMENTCLASSIFICATIONPOSITION

    POSITION作为显式实体 添加到您的模型有几个优点。

    1. 它避免了您担心WORKER可能被分配到不同公司的部门和分类的问题。
    2. 它为您提供了可能适用于某个职位的其他谓词的轨迹，例如薪水等级等。
    3. 它允许您记录一个位置存在的事实，即使WORKER该位置当前没有 s，这很可能是有用的信息。

    请注意，为避免为不同公司的部门和分类定义职位的问题，我扩展了DEPARTMENT和的键CLASSIFICATION，这很好，您可以在 Todd Everett 的回答中详细阅读。

    当心 上面的模型假设了一个简化。具体来说，它假设每个位置只记录一次。这可能适合也可能不适合您的业务规则。如果你需要POSITION一个公司内的同一个部门和分类的多条记录，那么你可以在POSITION.

    • 9

  3. 我理解这个问题的方式是，“工人”表的 ID_Classification 字段应该只允许为各自工人的公司定义的分类。因此，验证（通过附加规则或通过触发器）插入/更新到 Workers.ID_Classification 字段中的信息足以满足此要求。

    • 1

  4. 根据我的阅读，我仍然不明白这个分类是什么以及为什么它需要有ID_Company。如果它像这里提到的某人的位置，我认为包含所有位置的静态表会更好。

    如果您这样做是为了轻松找到公司中的分类/职位，请添加一个简单的查询/视图以连接分类-工人-部门并检索分类的公司 ID。

    现在，有更智能的视图或技术，例如物化视图和连接索引，所以如果您的问题是查询的性能，请使用它们。

    • 1

相关问题