主页 / dba / 问题 / 82710
Accepted
AlwaysLearningNewStuff
Asked: 2014-11-15 16:39:29 +0800 CST

无法将这种类型的 ER 图转换为 SQL 或关系表

  • 772

我在数据库设计过程中偶然发现了一个问题。让我解释一下它的全部内容：

合同有一些描述他的简单属性，可能有附件和支付动态。

因此我决定制作主表Contracts，并将简单的属性作为列。

支付动态是Contracts表的一个复杂属性。它可以没有、一个或多个值。通过互联网搜索，我了解到这称为多值属性，并且发现这个例子似乎很好地说明了我的情况（支付动态相当于链接示例中的爱好表）。

至于附件，它是一个复杂的属性Contract。合同可以有很多，一个或一个。

附件具有与合约完全相同的简单属性，也可以具有支付动态。附件与支付动态的关系与合同与支付动态的关系相同。

综上所述，Annex 和 Contract 一切都是一样的，唯一的区别是 Annex 是 Contract 的一个复杂属性。

以此为参考，我绘制了我的 ER 图的草图：

在此处输入图像描述

这是我第一次看到这种类型的关系，所以我请求社区帮助我将这个 ER 图转换为 SQL 或关系表。

很抱歉没有提供更多信息，如果您有进一步的要求，请发表评论，我会立即更新我的帖子。

感谢您的理解和帮助。

此致。

database-design

3 个回答

  1. Best Answer

    我同意@MladenUzelac 的方法可能是这种情况下更常见的模式，但是有一个潜在的理论缺陷会随着时间的推移而暴露出来并使生活变得困难。那个缺陷是：在一天结束时，合同不是附件。当然，我强调“潜力”，因为目前我不知道它们目前是否被认为是同一件事（即合同===附件）。但是，在一定程度上不会改变我的建议，以发现它们是相同的。任何事情都可能在任何时候发生变化，即使极不可能发生，所以今天具有相同的性质并不意味着它们将始终具有相同的性质，而且经常有足够多的事情朝着不同的方向发展。所以是的，它们今天具有相同的属性，并且它们与今天的动态支付具有相同的关系；但明天又是另一天，事情的性质发生了变化，即使是出于非理性的原因；-)。

    不要误会我的意思：有时自引用实体是合适的解决方案，但我认为这要求实体具有相同的性质。我能想到的两个最常见的例子是：

    • 员工/经理：在 [Employee] 表中有一个 [ManagerID]（或者我更喜欢 [ManagerUserID]）字段是有意义的，它是一个返回 [UserID] 的自引用 FK。这是有效的，因为经理是雇员。

    • 部门：在公司/零售层次结构中，在 [Department] 表中有一个 [ParentDepartmentID] 字段是有意义的，该字段是返回 [DepartmentID] 的自引用 FK。这是有效的，因为“父”部门是一个部门。

    在这两种情况下，父子关系都不是这些实体的主要决定属性。相反，关系是一个属性，就像实体的名称一样。在这两种情况下，关系对于子实体的存在并不重要，即使没有关系，定义为“子”的实体也存在。另一方面，如果附件需要合同才能存在，那是完全不同的事情。

    归根结底是：仅仅因为两件事看起来相同并不意味着它们是相同的。

    将两个“相似但不同”的实体存储在一起，同时减少一些表/连接/代码，会导致一些实际问题，即使只是在物理存储（即数据库层）方面。

    我已经在一个系统上工作了多年，该系统是在我到达那里之前构建的，其中这种模式被多次使用，并且用于与此处描述的情况几乎相同的情况。我不确定这些实体在第一天是否具有完全相同的属性，但至少随着时间的推移，它们开始出现分歧并具有彼此不同的属性。在某些情况下，添加了适合其中一个实体但不适合另一个实体的字段，因此我们只需要知道 NULL 字段何时适合该类型的行，或者它是否是一个错误。其他时候采用“多值属性”方法，结构有效：[EntityID]、[AttributeID]、[AttributeValue]。

    正如预期的那样，为了获得“父”实体的列表，我们将使用WHERE ParentEntityID IS NULL. 并提取我们将查询的“子”实体列表WHERE ParentEntityID IS NOT NULL. 当我们需要这两个实体时，这很有趣，因为它们实际上是另一个主要实体的属性，因此我们需要将两者都显示为主要实体的单独属性。有趣的是自引用连接如何损害性能（尽管索引策略的微小变化可能会解决这个问题）。但是当我们将 EntityID 作为 FK 放置在其他实体中时，它变得超级有趣，我们只想引用这两种实体类型之一，但并不总是保证我们想要“子”实体（尽管那是最常见的实体参考）。最后，当我开始创建新表并重构旧表时，我将实体名称包含在相关实体的 FK 字段的名称中，只是为了让它“自我记录” 对于需要创建新查询的表的任何人。在某些情况下，我添加了两个 FK 字段，每个实体类型一个，只是为了避免代价高昂的自联接（并且非规范化是安全的，因为一旦创建父子关系就永远不会改变）。

    所以，我会从与@Peter 相同的方向开始，在合同和附件的单独表格方面（即使它们相同或几乎相同的结构），但我会处理它们与支付动态的关系不同。我似乎记得过去曾尝试过两种不同的 FK 字段，但并不真正喜欢它的结果。至少随着时间的推移，随着时间的推移，它似乎不太容易管理，因为添加了可能与支付动态相关的其他实体，您需要继续将 FK 添加回新父母。并且您还需要在表上设置一个 CHECK CONSTRAINT，以确保这些 FK 字段中的一个且只有一个不为空。

    相反，我会为每个Annex和Contract创建一个单独的桥/关系表，以与Dynamic of Payment相关。是的，当添加可能与支付动态相关的新实体时，您将需要添加新的桥梁/关系表，但不知何故我感觉更好，因为它不会改变支付动态的结构：支付实体的动态是无论 1 或 100 个实体与之相关，都相同。

    （下面的 SQL 是使用 Microsoft SQL Server T-SQL 语义）

    主要实体

    CREATE TABLE SchemaName.[Contract]
(
  ContractID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  ContractName VARCHAR(50) NOT NULL,
  ContractDetails NVARCHAR(MAX) NULL
);

CREATE TABLE SchemaName.Annex
(
  AnnexID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  ContractID INT NOT NULL REFERENCES SchemaName.[Contract] (ContractID),
  AnnexName VARCHAR(50) NOT NULL,
  AnnexDetails NVARCHAR(MAX) NULL
);

    支付动态

    [DynamicsOfPaymentType] 表是一个查找表，它假定“动态”是命名属性，可能模仿应用层中的枚举。我选择了 TINYINT (0 - 255)，因为通常大多数事物都有少于 255 个不同的属性/属性。如果需要超过 255 个，则使用下一个更高的尺寸。但是不要超出您的需要，因为该字段被复制到可能会变大的关系表中，特别是有很多Contract和Annex，每个都有几个DynamicOfPayment条目。

    请注意，DynamicsOfPayment 上的 PK 是由 [DynamicsOfPaymentID] 和 [DynamicsOfPaymentTypeID] 组成的复合键。虽然从技术上讲，PK 中只需要 [DynamicsOfPaymentID] 字段，因为它保证是唯一的，但拥有 [DynamicsOfPaymentTypeID] 字段允许我们将该属性推到关系表中，以用于限制每个实体只有一个实例DynamicsOfPayment属性。有关详细信息，请参阅下一节中的注释。

    CREATE TABLE SchemaName.DynamicsOfPaymentType
(
  DynamicsOfPaymentTypeID TINYINT NOT NULL PRIMARY KEY, -- SMALLINT if > 255 are possible
  DynamicsOfPaymentType VARCHAR(50) NOT NULL
);

CREATE TABLE SchemaName.DynamicsOfPayment
(
  DynamicsOfPaymentID INT IDENTITY(1, 1) NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL REFERENCES
                          SchemaName.[DynamicsOfPaymentType] (DynamicsOfPaymentTypeID),
  Value NVARCHAR(MAX) NULL,
  PRIMARY KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

    支付关系动态的主要实体

    请注意，两个关系表的 PK 是由主实体 ID 和 [DynamicsOfPaymentTypeID] 字段组成的复合键，而不是 [DynamicsOfPaymentID] 字段。如前几节所述，原因是为每个主要实体强制执行任何特定DynamicsOfPayment属性的单个实例。如果一个实体可以有多个DynamicsOfPayment实例属性，那么将特定关系表上的 PK 调整为由主实体 ID 和 [DynamicsOfPaymentID] 字段（或者如果您愿意，[DynamicsOfPaymentTypeID] 和 [DynamicsOfPaymentID] ] 字段）。如您所见，此模型不仅允许关系强制执行相关属性的单个实例或允许关系接受多个相关属性，而且还允许将两者混合，因为关系表不必使用PK 的相同字段组合：一些关系可以受到限制，而另一些则不能。因此，附件可能仅限于任何特定DynamicsOfPayment属性的单个实例，但合同允许具有特定属性的多个实例。

    CREATE TABLE SchemaName.[ContractXDynamicsOfPayment]
(
  ContractID INT NOT NULL,
  DynamicsOfPaymentID INT NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL,
  PRIMARY KEY (ContractID, DynamicsOfPaymentTypeID),
  FOREIGN KEY (ContractID) REFERENCES SchemaName.[Contract] (ContractID),
  FOREIGN KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID) REFERENCES
              SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

CREATE TABLE SchemaName.[AnnexXDynamicsOfPayment]
(
  AnnexID INT NOT NULL,
  DynamicsOfPaymentID INT NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL,
  PRIMARY KEY (AnnexID, DynamicsOfPaymentTypeID),
  FOREIGN KEY (AnnexID) REFERENCES SchemaName.[Annex] (AnnexID),
  FOREIGN KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID) REFERENCES
              SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

    专业提示

    1. 是的，使用此模型的表、连接和应用程序代码会稍微多一些。但是，请记住，重构应用程序代码很多比重构表更容易，尤其是当应用程序上线并且数据在那里时。现在花一点时间来编写代码，并在多年来尝试对更简单的模型进行更改，或者在几年后出现项目时，为自己节省很多很多时间，只是决定加入黑客/乐队- 帮助，因为企业不会在 10 到 20 （或更多）小时内对简化模型进行更改，而现在可以为您节省 1 到 2 小时。相信我，数据库程序员/架构师并不便宜（至少现在不是），而且我的雇主花费了大量的时间和金钱——以我的薪水（以及我的数据库同事的薪水）的形式——为了我重构糟糕的设计选择，甚至可以节省 1 天的初始开发时间，

    2. 您可以通过将 FK 标记为 来协调关系表的清理ON DELETE CASCADE。RDBMS 之间的语法可能略有不同，但我希望它们中的大多数（如果不是全部）都支持级联删除。这样，如果您删除Dynamic of Payment条目，则无需担心它在任一关系表中的条目。

    3. 根据您使用的 RDBMS，您甚至可以协调关系记录的创建。Microsoft SQL Server 有一个非常酷的OUTPUT子句，它返回 DML 操作所做的更改。因此，您可以在 [DynamicsOfPayment] 中插入一条记录，并且在该操作（以及交易）的上下文中，您可以插入到关系表中。假设您有一个插入过程，该过程接受一个额外的 @ContractID 参数，而 [DynamicOfPayment] 表最初不需要该参数。它可以按如下方式使用：

      INSERT INTO SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentTypeID, Value)
OUTPUT @ContractID,
       INSERTED.DynamicsOfPaymentID, -- server-generated auto-increment field
       INSERTED.DynamicsOfPaymentTypeID
INTO   SchemaName.[ContractXDynamicsOfPayment]
       (ContractID, DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
VALUES (@DynamicsOfPaymentTypeID, @Value);

      现在您只需要两个存储过程（因为每个都需要引用不同的关系表）：

      • DynamicsOfPayment_CreateForContract (@DynamicsOfPaymentTypeID, @Value, @ContractID)
      • DynamicsOfPayment_CreateForAnnex (@DynamicsOfPaymentTypeID, @Value, @AnnexID)
    • 4

  2. 合同和附件（分层结构）的自我连接似乎更好。
    合约和附件的命名可能不同，id 和 self_id 可能不同。

    create table contract(
   id serial primary key,
   self_id int,
   document_id text,
   constraint contract_self_fk foreign key(self_id) references contract(id)
);

create table dynamics_of_payment(
    id serial primary key,
    contract_id int,
    constraint dynamics_of_payment_contract_fk foreing key(contract_id) references contract(id)
 );

    由于 PostgreSQL 支持可写公用表表达式 (CTE)，您可以更新、插入、插入和删除值。

    我将向您指出将向您解释更多信息的指南：
    解释递归结构的精彩视频
    PostgreSQL 中的常用表表达式 - 官方文档
    Recursive queries with postgres
    Recursive 3
    Tree structure with PostgreSQL

    这是关于该问题的 Oracle 教程：
    Oracle 和 CTE

    我将演示如何轻松保持合同和附件的排序。
    当您在 Contract 表中存储值时，请遵循以下模式：

    (id, self_id, document_id)
  1,   1,       contract xxx
  2,   1,       annexx xxx_01
  3,   2,       annexx xxx_02
  4,   3,       annexx xxx_03

    因此，当您进行递归查询时，您可以轻松获得订购的官方文档。

    这里有一个关于 SQL 递归结构的免费短期课程： https ://class.stanford.edu/courses/DB/Recursion/SelfPaced/about

    • 2

  3. 我会创建3个表：

    合同：合同 id (pkey) 属性 1 属性 2 等

    附件：附件 id (pkey) Contract id (fkey) Attrib 1 Attrib 2 etc

    动态：支付 id (pkey) Contract id (fkey) Annex id (fkey) Attrib 1 Attrib 2 etc.

    据我了解您的描述，这将允许动态表中的条目与合同或附件或两者相关。

    • 0

相关问题