如何将主键字段与多个表相关联？ [英] How can I relate a primary key field to multiple tables?

问题描述

我有4张桌子：

User 
Reports 
Photos 
Locations 

我可以举报照片，用户和位置。

I am allowed to report photos, users, and locations.

我的 Reports 表的主键是（ User_Id，Reported_Id ）

Reported_Id 可能属于以下3种：照片，用户和位置。

Reported_Id could belong to any of these 3: photos, users and locations.

如何在实体关系中表示这种关系

How can I represent this relationship in an entity relationship model?

推荐答案

问题

The Problem

_{您无法执行此操作-外键（Reported_Id）无法一次引用三个表}

似乎是关于数据理解的问题……而不是关于指向三个PK的FK的技术问题。

Seems to be a problem regarding the understnading of the data ... rather than a technical problem regarding an FK pointing to three PKs.

_{或三分之一（取决于表中的其他列）。}

那是不正确的。在关系数据库中，这样的要求非常简单：

That is not correct. Such a requirement is fairly straight-forward in a Relational database:

• 关系模型是合乎逻辑的，它是基于一阶谓词演算（也称为一阶逻辑）建立的。
  
  具有扎实的数学基础会赋予它强大的力量。

• the Relational Model is logical, it is founded on First Order Predicate Calculus (aka First Order Logic).
  Having a solid mathematical basis gives it great power.

亲戚是逻辑生物

物理记录ID 不符合逻辑

the relations are logical creatures
Physical Record IDs are not logical

在FOL中可以定义的内容没有限制，

在FOL中没有不能定义的内容

因此，在关系数据库（当然还有SQL，它的数据子语言）中没有不能定义的任何内容。

。

there is no limit to what can be defined in FOL,
there is nothing that cannot be defined in FOL
therefore there is nothing that cannot be defined in a Relational database (and of course SQL, its data sublanguage)
.

_{请注意，理论家所倡导和推销的关系实际上是1960年代的备案系统没有关系完整性；关系力量；或符合关系模型的数据库具有的关系速度。通过使用物理记录ID 来识别此类系统。在这样的原始系统中，是的，数据不是逻辑的，并且不能定义逻辑关系。此外，所需的SQL代码太可怕了。}

_{Note that what the "theoreticians" promote and market as "relational" is in fact a 1960's Record Filing System, which has none of the Relational Integrity; Relational Power; or Relational Speed that a database that complies with the Relational Model has. Such systems are identified by their use of physical Record IDs. In such primitive systems, yes, data is not logical, and logical relations or relationships cannot be defined. Further, the SQL code required is horrendous.}

正在逻辑文章中找到或门。需要定义或门的特定性质（有几种形式）：即建模练习。

What you are seeking in a logic article, an OR Gate. The specific nature of the OR Gate (there are several forms) needs to be defined: that is the modelling exercise.

忘记 ID 列，这只会削弱数据建模工作。专注于数据，数据的含义以及与之相关的其他数据。也许您正在尝试按照以下方式声明某些内容（这些是FOPC / FOL 谓词）：

Forget about ID columns, that will only serve to cripple the data modelling exercise. Concentrate on the data, what the data means, and what other data it relates to. Perhaps you are trying to declare something along these lines (these are FOPC/FOL Predicates):

• 每个用户是独立的


• 每张照片都是独立的


• 每个位置都是独立的


• 每个用户进行0到n个报告


• 每个报告都是{strong |任意位置|用户}

• Each User is Independent
• Each Photo is Independent
• Each Location is Independent
• Each User makes 0-to-n Reports
• Each Report is any of { Photo | Location | User }

这很宽松，我们可以收紧它。让我们继续...

That is very loose, we can tighten it up. Let's proceed to ...

此数据模型（ER级别）实现了<报表的strong>非排他子类型集群。

This data model (ER level) implements a Non-Exclusive Subtype cluster for Report.

所有谓词在IDEF1X数据模型中是显式的，因此可以是从中读取，但是，我在右侧以文本形式给出了相关谓词 。

All Predicates are explicit in an IDEF1X data model, and thus can be read from it, however, I have given the relevant Predicates in text form on the right.

• 我所有的数据模型都以 IDEF1X ，自1993年以来为关系数据库建模的标准

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993

我的 IDEF1X简介对于初学者来说是必不可少的阅读

My IDEF1X Introduction is essential reading for beginners

IDEF1X解剖学是那些过失的人的复习。

The IDEF1X Anatomy is a refresher for those who have lapsed.

非排他性亚型-请参阅 Subtype 了解有关子类型实现的完整详细信息。

Non-Exclusive Subtype - refer to Subtype for full details on Subtype implementation.

• 有关对比度或兴趣的独家子类型，请参见此答案。

• for contrast or interest re Exclusive Subtype, refer to this answer.

上面实现了谓词：

• 每个报告是{照片|的任何位置|用户}

• Each Report is any of { Photo | Location | User }

表示报告必须至少是{图片|位置|用户}。

meaning that a Report must be at least one of { Photo | Location | User }.

但是，如果不是这种情况，则报表可以 {位置|用户}，即{照片|位置|用户}是每个可选列。

However, if that is not the case, if Report can be none of { Photo | Location | User }, that means { Photo | Location | User } are each Optional Columns.

_{取决于表中其他列的三个之一}

确定哪个或所有子类型或每个报告使用的可选列都不是问题：

Determining which, or all, the Subtypes or Optional Columns that are used for each Report is not an issue:

• 专有子类型
  
  是的，确实需要在基本类型中添加一个 Discriminator 列。

非排他性子类型

基本类型有多个子类型，因此基本类型中的Discriminator列无关紧要。

Non-Exclusive Subtype
There are multiple Subtypes for the Basetype, therefore a Discriminator column in the Basetype is irrelevant.

• 确定是通过Subtype表中的 SELECT 进行的（根据定义，与基本类型表具有完全相同的PK。）

• Determination is via a SELECT from the Subtype table (which, by definition, has the exact same PK as the Basetype table).

可选列

基本类型中的指标将是多余的。

Optional Column
An indicator in the Basetype would be redundant.

• 确定是通过可选列表（ditto）中的 SELECT 确定的。 / li>
  
• 通常，人们会为基本类型创建一个 VIEW ，例如 Report_V ，并包括所有可能的列。

• Determination is via a SELECT from the Optional Column table (ditto).
• Generally, one would create a VIEW for the Basetype, eg Report_V, and include all possible columns.

这篇关于如何将主键字段与多个表相关联？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！