SQL 架构 : Is this a justified case to have only one table storing multiple entity types?(使用自联接)

Question

我很少遇到这样的情况，多个实体类型的单个表似乎比每个实体类型一个表更好。这是一个对我有意义的例子，但在学术上似乎是错误的。

问题:我可以这样做并且仍然拥有“声音”架构吗？

示例如下

假设有两种实体类型，一个公司和一个人。一家公司通常由一个人所有，但有时另一家公司拥有一家公司。

坚持这个想法，并补充说，假设每个公司都有一个注册代理人，负责公司的合法创建。进一步说明，注册代理人可以是个人或公司。

如果您认为公司 [子] 的所有者 [母公司] 可以是个人或公司，您可能会开始看到保持第三范式和避免冗余的挑战。

与我的示例相反，如果只有人可以拥有 Corporations，那么 Ownership 链接表非常常规，具有以下列:OwnershipID(有点不必要)、CorporationID、PersonID。

相反，您需要类似的信息:OwnershipID、CorporationID、OwnerID、OwnerEntityType(公司或个人)。不要误会我的意思，你可以让这个工作，但至少可以说它不会很有趣。

继续我给出的示例，您需要为每个公司分配一个代理。通常，代理人是所有者之一(一个人)。在这种情况下，您确实希望链接回该人的一个记录。您不想将该人记录为所有者然后再次作为代理(在代理表中)。那将是多余的。

与该“问题”类似，注册代理人也可以是公司，例如律师事务所、注册会计师或商业文件公司，举一些典型的例子。就像代理人一样，代理人公司真的不应该获得自己作为代理人实体的记录。相反，它需要链接回 Corporation 表中其公司存在的 。 [除了我最终说没有 CorporationEntity 表]

就像将每个公司与其任何类型、个人或公司的所有者相匹配的链接表一样，您可以拥有一个代理链接表:AgentRepresentationID、CorporationID、AgentID、AgentType……但同样，它会很丑( IMO) 当您必须将相关代理召集在一起时——一些来自 Person 表，一些来自 Corporation 表。

这就是为什么我说，在这种情况下，您可以看到中性实体类型如何具有优势。它会是这样的:

表:实体全部
关键列:EntityID、EntityType(或 EntityTypeID，如果您坚持，请链接出来以获取描述)、EntityName(有关于名称和不同类型的问题......本文的主题之外)

链接表:公司所有权
关键列:OwnershipID(同样，我认为这是不必要的)，ChildEntityID(被拥有的实体；为了清楚起见，命名为“Child”，我不会这样命名)ParentEntityID(父实体)

链接表:代理代表
关键列:AgentRepresentationID(...我不会说)、CorporationEntityID(被表示的公司实体)、AgentEntityID(来自实体表，相当于这里的代理记录)

虽然您可能对我的架构没意见，但您应该对链接表中的列命名有些困扰。这让我很烦。通常，这些表中的第二个和第三个列名与您在每个实体的各自表中加入的列的名称完全匹配(哈哈，但每个实体都没有各自的表，因此您不能让链接表列名匹配源列名称，因为它们是同一列)。从技术上讲，这并不重要，但它会破坏您的命名约定，这很重要，但还不足以不这样做。

如果我还没有把它开回家，这里是你将它拉到一起的方法。您自己加入 EntityAll 表以获得您需要的内容。

列出所有军团及其所有者(在 T-SQL 中):

SELECT Corp.EntityName as CorpName, Owner.EntityName as OwnerName
FROM EntityAll as Corp
JOIN CorporationOwnership as Link on (Corp.EntityID = Link.ChildEntityID)
JOIN EntityAll as Owner on (Link.ParentEntityID = Owner.EntityID)

因此，您会做同样的事情来返回代理，而不是所有者。

我意识到这不是我们训练来构建表的方式，但我非常强烈地感觉到我的解决方案消除了冗余数据并使编码、管理和阅读变得更容易。

附言我最近提供了这个例子作为对 SO 上一个老问题的回答。作为一个老问题，没有对话。我需要实现这个例子，我很好奇这个架构的后果。

这是上一个问题/答案:
Good db table design: One table mixing different entities or separate table for each entity

Answer 1

我认为是 Hugh Darwen 创造了术语“分布式键”和“分布式外键”，其中一个被引用的键值恰好存在于多个引用相关变量(表)之一中；这将需要一个相关的“多重赋值”概念，以便原子地插入到引用和引用的 relvars。

虽然这在理论上可以在 SQL-92 中使用延迟模式级实现 ASSERTION s(或者可能是 CHECK 支持子查询的约束)，这是一个相当笨重的过程，是程序性的(而不是基于集合的)，并且没有支持此功能的 SQL 产品(或者永远不会，我怀疑) .

对于可用的 SQL 产品，我们能做的最好的事情是使用复合键 (entity_ID, entity_type)与 CHECK entity_type 上的约束在引用表中以确保引用键值不超过一个(请注意，这与“恰好一个引用键值”不同)，例如

CREATE TABLE LegalPersons
(
 person_ID INTEGER IDENTITY NOT NULL UNIQUE, 
 person_type VARCHAR(14) NOT NULL
    CHECK (person_type IN ('Company', 'Natural Person')), 
 UNIQUE (person_type, person_ID)
);

CREATE TABLE Companies
(
 person_ID INTEGER NOT NULL UNIQUE, 
 person_type VARCHAR(14) NOT NULL
    CHECK (person_type = 'Company'), 
 FOREIGN KEY (person_type, person_ID)
    REFERENCES LegalPersons (person_type, person_ID), 
 companies_house_registered_number VARCHAR(8) NOT NULL UNIQUE
 -- other company columns and constraints here
);

CREATE TABLE NaturalPersons
(
 person_ID INTEGER NOT NULL UNIQUE, 
 person_type VARCHAR(14) NOT NULL
    CHECK (person_type = 'Natural Person'), 
 FOREIGN KEY (person_type, person_ID)
    REFERENCES LegalPersons (person_type, person_ID) 
 -- natural person columns and constraints here
);

这种父类(super class)-子类模式在 SQL 中很常见。

理想情况下，表名应该反射(reflect)整个集合的性质。你们很多人需要考虑的不仅仅是其他系列名称的组合；也许请教特定业务领域的专家，例如会计师可以使用术语“payroll”而不是“EmployeesSalaries”。

另一个理想是列的名称在整个架构中保持不变，但使用子类化方法，您通常需要限定它们(这让我很困扰!)例如

CREATE TABLE CompanyAgents
(
 company_person_ID INTEGER NOT NULL UNIQUE, 
 company_person_type VARCHAR(14) NOT NULL
    CHECK (company_person_type = 'Company'), 
 FOREIGN KEY (company_person_type, company_person_ID)
    REFERENCES LegalPersons (person_type, person_ID), 
 agent_person_ID INTEGER NOT NULL, 
 agent_person_type VARCHAR(14) NOT NULL, 
 FOREIGN KEY (agent_person_type, agent_person_ID)
    REFERENCES LegalPersons (person_type, person_ID), 
 CHECK (company_person_ID <> agent_person_ID)
);

注意我会为 agent_person_ID 使用单列键例如

 agent_person_ID INTEGER NOT NULL
    REFERENCES LegalPersons (person_ID)

因为实体类型没有限制。原则上，我对在整个架构中为所有引用保留两列复合键感觉更好，并且在实践中我发现我现在经常不需要实体类型，所以这个 SQL DDL 保存了 JOIN在 SQL DML 中 :)