主页 / dba / 问题 / 140604
Accepted
AlwaysLearningNewStuff
Asked: 2016-06-08 03:32:48 +0800 CST

在具有互斥子类的类型/子类型设计模式中实现子类型的子类型

  • 772

介绍

为了让这个问题对未来的读者有用，我将使用通用数据模型来说明我面临的问题。

我们的数据模型由 3 个实体组成，分别标记为A和。为了简单起见，它们的所有属性都是类型。BCint

实体A具有以下属性：D,E和X;

实体B具有以下属性：D,E和Y;

实体C具有以下属性：D和Z;

由于所有实体都有共同的属性D，我决定应用类型/子类型设计。

重要提示：实体是互斥的！这意味着实体是 A 或 B 或 C。

问题：

实体A还有B另一个共同的属性E，但是这个属性在实体中是不存在的C。

问题：

如果可能的话，我想使用上述特性来进一步优化我的设计。

老实说，我不知道如何做到这一点，也不知道从哪里开始尝试，因此这篇文章。

sql-server database-design

3 个回答

  1. 根据 Martin Fowler 的说法，表继承问题有 3 种方法：

    • 单表继承：一张表代表所有类型。未使用的属性为 NULL。
    • 具体表继承：每个具体类型一个表，每个表列对应该类型的每个属性。表之间没有关系。
    • 类表继承：每种类型一个表，每个表仅具有新的非继承属性的属性。表是相关的，反映了实际的类型继承层次结构。

    您可以从这些开始，搜索每种方法的优缺点。它的要点是，所有方法都有很大的缺点，没有一种方法具有压倒性的优势。更好地称为对象关系阻抗失配，这个问题尚未找到解决方案。

    就我个人而言，我发现糟糕的关系设计可能导致的问题类型比糟糕的类型设计引起的问题严重几个数量级。糟糕的数据库设计会导致查询缓慢、更新异常、数据量激增、死锁和应用程序无响应，以及数十到数百 GB 的数据以错误的格式沉没。糟糕的类型设计导致难以维护和更新代码，而不是运行时。因此，在我的书中，正确的关系设计一遍又一遍地胜过任何 OO 类型的纯度。

    • 21
  2. Best Answer

    就这个问题而言，我的类型/子类型设计模式（对于互斥的子类）的实现是否正确？，这本身就是不知道如何将变量实体转换为关系表的延续，我会问：你到底想优化什么？贮存？对象模型？查询复杂度？查询性能？在优化一个方面与另一个方面时存在权衡，因为您无法同时优化所有方面。

    我完全同意Remus关于以下方面的观点：

    • 每种方法都有利有弊（即永远存在的“它取决于”因素），并且
    • 第一要务是数据模型的效率（效率低下的数据模型无法通过干净和/或高效的应用程序代码来纠正）

    也就是说，您面临的选择是以下之间的选择，按照从最低标准化到最高标准化的顺序排列：

    • 将属性E提升到基本类型表
    • 将其保存在多个子类型表中
    • 完全规范化E为一个新的中间子类表，与,C将直接成为 ( @MDCCL 的答案)的子类AB

    让我们看看每个选项：

    将属性移动E到基本类型表

    专业人士

    • E降低了需要但不需要X,Y或的查询的复杂性Z。
    • 由于没有 JOIN ，对于需要E但不需要X,Y或（尤其是聚合查询）的查询可能更有效。Z
    • 可能在其上创建索引（如果是这样，可能在 EntityType <>(D, E)上创建过滤索引，如果允许这样的条件）(D, E)C

    缺点

    • 无法标记E为NOT NULL
    • 需要额外CHECK CONSTRAINT的基本类型表以确保E IS NULL当 EntityType = 时C（尽管这不是一个大问题）
    • 当 EntityType = 时，需要教育数据模型的用户为什么E必须NULL，甚至应该完全忽略C。
    • E当是固定长度类型时效率稍低，并且大部分行用于 EntityType C（即不使用E因此它是NULL），并且不使用SPARSE列上的选项或聚集索引上的数据压缩
    • 对于不需要的查询可能效率较低，E因为E基本类型表中的存在会增加每行的大小，这反过来会减少数据页上可以容纳的行数。但这高度依赖于EFILLFACTOR 的确切数据类型、基本类型表中有多少行等。

    E在每个子类型表中保留属性

    专业人士

    • 更清晰的数据模型（即不必担心教育其他人为什么E不应该使用基本类型表中的列，因为“它真的不存在”）
    • 可能更接近于对象模型
    • 可以将列标记为NOT NULL好像这是实体的必需属性
    • 不需要额外CHECK CONSTRAINT的基本类型表来确保E IS NULL当 EntityType = 时C（尽管这不是一个巨大的收获）

    缺点

    • 需要 JOIN 以子类型表来获取此属性
    • 由于 JOIN，在需要时效率可能会稍低E，具体取决于您拥有多少行A+B而不是有多少行C。
    • 对于仅处理实体A和B（而不是 C）作为相同“类型”的操作来说，难度/复杂度稍高一些。当然，您可以通过一个在 JOINed 表和另一个JOINed表UNION ALL之间执行 a的 View 来抽象它。这将降低 SELECT 查询的复杂性，但对查询没有太大帮助。SELECTASELECTBINSERTUPDATE
    • 根据具体的查询以及它们的执行频率，在索引(D, E)确实有助于一个或多个常用查询的情况下，这可能是一种潜在的低效率，因为它们不能被索引在一起。

    规范化E为基类和A&之间的中间表B

    （请注意，我确实喜欢@MDCCL 的答案作为可行的替代方案，具体取决于具体情况。以下内容并不是对这种方法的严格批评，而是作为一种增加一些观点的手段——当然是我的——通过评估它与我已经提出的两个选项处于相同的上下文中。这将使我更容易澄清我所看到的完全标准化和当前部分标准化方法之间的相对差异。）

    专业人士

    • 数据模型是完全规范化的（这不可能有任何固有的错误，因为这是 RDBMS 的设计目的）
    • 降低了查询的复杂性，需要A和B，但不是C（即不需要两个通过 连接的查询UNION ALL）

    缺点

    • 占用的空间稍多（Bar表格重复了 ID，并且有一个新列，BarTypeCode）[可以忽略不计，但需要注意的事项]
    • 查询复杂性略有增加，因为需要额外JOIN的才能到达A或B
    • 增加了用于锁定的表面积，主要是在INSERT（DELETE可以通过将外键标记为隐式处理ON CASCADE DELETE），因为事务将在基类表上保持打开稍长的时间（即Foo）[可以忽略不计，但需要注意的事项]

    • 没有直接了解基类表中的实际类型——A或—— ；它只知道可以是或的类型： BFooBrAB

      意思是，如果您需要对一般基本信息进行查询，但需要按实体类型分类或过滤出一种或多种实体类型，则基类表没有足够的信息，在这种情况下，您需要桌子LEFT JOIN。Bar这也会降低索引FooTypeCode列的效率。

    • A与& Bvs交互没有一致的方法C：

      这意味着，如果每个实体都直接与基类表相关，从而只有一个 JOIN 来获得完整的实体，那么每个人都可以更快、更轻松地熟悉使用数据模型。查询/存储过程将有一种通用方法，这使它们可以更快地开发并且不太可能出现错误。一致的方法也使将来添加新的子类型变得更快、更容易。

    • 可能不太适应随时间变化的业务规则：

      E意思是，事情总是在变化，如果它对所有子类型都通用，那么向上移动到基类 Table是相当容易的。如果实体性质的变化使一个有价值的变化，那么将一个公共属性移出子类型也很容易。将一个子类型分解为两个子类型（只需创建另一个SubTypeID值）或将两个或多个子类型组合成一个是很容易的。反之，如果E后来成为所有子类型的共同属性呢？那么Bar表格的中间层就没有意义了，增加的复杂性也就不值得了。当然，不可能知道5年甚至10年后是否会发生这样的变化，所以Bar表不一定，甚至极有可能是一个坏主意（这就是我说“可能不太适应”的原因）。这些只是需要考虑的要点；这是一场双向的赌博。

    • 可能不适当的分组：

      意思是，仅仅因为E属性在实体类型之间共享A，B并不意味着应该A组合在一起。仅仅因为事物“看起来”相同（即相同的属性）并不意味着它们是相同的。B

    概括

    就像决定是否/何时进行非规范化一样，如何最好地处理这种特殊情况取决于考虑数据模型使用的以下方面并确保收益大于成本：

    • 每个 EntityType 将有多少行（假设增长高于平均水平，至少 5 年后）
    • 这些表（基本类型和子类型）在 5 年内将达到多少 GB？
    • 什么具体的数据类型是属性E
    • 是只有一个属性还是有几个甚至几个属性
    • 您需要哪些查询E以及执行频率
    • 您需要哪些不需要的查询E以及它们执行的频率

    我认为我倾向于默认保留E单独的子类型表，因为它至少是“更干净”的。我会考虑移动E到基本类型表 IF：大多数行不是EntityType 的C；行数至少数百万；并且我经常而不是不执行需要E的查询和/或将受益于索引的查询(D, E)要么非常频繁地执行和/或需要足够的系统资源，因此拥有索引会降低整体资源利用率，或者至少可以防止超过可接受水平或持续时间足够长的资源消耗激增导致过度阻塞和/或死锁增加。

    更新

    OP对此答案评论说：

    我的雇主改变了业务逻辑，完全删除了 E！

    这种变化特别重要，因为这正是我在上面的“规范化E到基类和A&之间的中间表B”部分（第 6 个要点）的“CONs”小节中所预测的可能发生的情况。具体问题是在发生此类更改时重构数据模型的难易程度（而且它们总是如此）。有些人会争辩说，任何数据模型都可以重构/更改，所以从理想开始。但是，虽然在技术层面上确实可以重构任何东西，但实际情况是规模问题。

    资源不是无限的，不仅仅是 CPU / Disk / RAM，还有开发资源：时间和金钱。企业不断为项目设定优先级，因为这些资源非常有限。并且很多时候（至少以我的经验），提高效率的项目（甚至是系统性能以及更快的开发/更少的错误）优先于增加功能的项目。虽然这让我们技术人员感到沮丧，因为我们了解重构项目的长期利益是什么，但这正是业务的本质，技术含量较低的业务人员更容易看到新功能和新功能之间的直接关系收入。这归结为：“我们稍后会回来解决这个问题”==“

    考虑到这一点，如果数据的大小足够小，以至于可以进行非常查询的更改，和/或您有足够长的维护窗口，不仅可以进行更改，还可以在出现问题时回滚错误，然后规范化E到基类表和A&B子类表之间的中间表可以工作（尽管这仍然使您无法直接了解特定类型（A或B) 在基类表中)。但是，如果您在这些表中有数亿行，并且引用这些表的代码数量惊人（在进行更改时必须测试代码），那么务实而不是理想主义通常是值得的。这就是我多年来不得不处理的环境：9.87 亿行和 615 GB 的基类表，分布在 18 台服务器上。这些表（基类表和子类表）的代码量如此之大，以至于由于开发量和需要分配的 QA 资源。

    因此，再次强调，“最佳”方法只能根据具体情况确定：您需要了解您的系统（即有多少数据以及表和代码之间的关系）、如何完成重构以及人员与你一起工作的人（你的团队和可能的管理层——你能得到他们对这样一个项目的支持吗？）。我已经提到并计划了 1 到 2 年的一些更改，并进行了多次冲刺/发布以实现其中的 85%。但是，如果您只有 < 100 万行并且没有很多代码与这些表相关联，那么您可能能够从更理想/“纯粹”的方面开始。

    请记住，无论您选择哪种方式，至少要注意该模型在未来 2 年内的运作方式（如果可能的话）。注意什么有效，什么导致了痛苦，即使这在当时看起来是最好的想法（这意味着你还需要让自己接受搞砸了——我们都这样做——这样你就可以诚实地评估痛点）。并注意为什么某些决定有效或无效，以便您可以做出下次更有可能“更好”的决定:-)。

    • 7

  3. 根据我对您的规范的解释，您想找到一种方法来实现两个不同的（但连接的）超类型 - 子类型结构。

    为了揭示实现上述任务的方法，我将在有争议的场景中添加两个称为和的经典假设实体类型，我将在下面详细介绍。FooBar

    商业规则

    以下是一些有助于我创建逻辑模型的语句：

    • A Foo is either one Bar or one C
    • A Foo is categorized by one FooType
    • A Bar is either one A or one C
    • A Bar is classified by one BarType

    逻辑模型

    然后，生成的 IDEF1X [1]逻辑模型如图 1所示（您也可以从 Dropbox 以 PDF 格式下载它）：

    图 1 - 假设的超类型-子类型关系数据模型

    Foo 和 Bar 添加

    我没有添加FooandBar让模型看起来更好，而是让它更具表现力。我认为它们很重要，原因如下：

    • 作为A并B共享名为 的属性E，此特征表明它们是不同（但相关）类型的概念、事件、人、测量等的子实体类型，我通过Bar超实体类型表示，反过来，的子实体类型Foo，它将D属性保存在层次结构的顶部。

    • 由于C只与所讨论的其余实体类型共享一个属性，即 ，D因此这方面暗示它是另一种概念、事件、人、度量等的子实体类型，因此我借助超级Foo实体类型。

    但是，这些只是假设，由于关系数据库旨在准确反映特定业务上下文的语义，因此您必须识别和分类您特定领域中所有感兴趣的事物，以便您可以准确地捕捉更多含义.

    设计阶段的重要因素

    意识到一个事实是非常有用的，抛开所有的术语，一个排他的超类型-子类型集群是一个普通的关系。让我们用以下方式描述这种情况：

    • 每个独占的超实体类型出现仅与一个子实体类型补码相关。

    因此，在这些情况下存在一对一 (1:1) 的对应关系（或基数）。

    正如您从前面的帖子中知道的那样，鉴别器属性（列，当实现时）在创建这种性质的关联时起着至关重要的作用，因为它指示与超类型连接的正确子类型实例。PRIMARY KEY 从 (i) 超型到 (ii) 亚型的迁移也具有重要意义。

    具体的 DDL 结构

    然后我写了一个基于上述逻辑模型的 DDL 结构：

    CREATE TABLE FooType -- Look-up table.
(
    FooTypeCode     CHAR(2)  NOT NULL,
    Description     CHAR(90) NOT NULL, 
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_FooType             PRIMARY KEY (FooTypeCode),
    CONSTRAINT AK_FooType_Description UNIQUE      (Description)
);

CREATE TABLE Foo -- Supertype
(
    FooId           INT      NOT NULL, -- This PK migrates (1) to ‘Bar’ as ‘BarId’, (2) to ‘A’ as ‘AId’, (3) to ‘B’ as ‘BId’, and (4) to ‘C’ as ‘CId’.
    FooTypeCode     CHAR(2)  NOT NULL, -- Discriminator column.
    D               INT      NOT NULL, -- Column that applies to ‘Bar’ (and therefore to ‘A’ and ‘B’) and ‘C’.
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Foo                 PRIMARY KEY (FooId),
    CONSTRAINT FK_from_Foo_to_FooType FOREIGN KEY (FooTypeCode)
        REFERENCES FooType (FooTypeCode)
);

CREATE TABLE BarType -- Look-up table.
(
    BarTypeCode CHAR(1)  NOT NULL,  
    Description CHAR(90) NOT NULL,  
    CONSTRAINT PK_BarType             PRIMARY KEY (BarTypeCode),
    CONSTRAINT AK_BarType_Description UNIQUE      (Description)
);

CREATE TABLE Bar -- Subtype of ‘Foo’.
(
    BarId       INT     NOT NULL, -- PK and FK.
    BarTypeCode CHAR(1) NOT NULL, -- Discriminator column. 
    E           INT     NOT NULL, -- Column that applies to ‘A’ and ‘B’.
    CONSTRAINT PK_Bar             PRIMARY KEY (BarId),
    CONSTRAINT FK_from_Bar_to_Foo FOREIGN KEY (BarId)
        REFERENCES Foo (FooId),
    CONSTRAINT FK_from_Bar_to_BarType FOREIGN KEY (BarTypeCode)
        REFERENCES BarType (BarTypeCode)    
);

CREATE TABLE A -- Subtype of ‘Bar’.
(
    AId INT NOT NULL, -- PK and FK.
    X   INT NOT NULL, -- Particular column.  
    CONSTRAINT PK_A             PRIMARY KEY (AId),
    CONSTRAINT FK_from_A_to_Bar FOREIGN KEY (AId)
        REFERENCES Bar (BarId)  
);

CREATE TABLE B -- (1) Subtype of ‘Bar’ and (2) supertype of ‘A’ and ‘B’.
(
    BId INT NOT NULL, -- PK and FK.
    Y   INT NOT NULL, -- Particular column.  
    CONSTRAINT PK_B             PRIMARY KEY (BId),
    CONSTRAINT FK_from_B_to_Bar FOREIGN KEY (BId)
        REFERENCES Bar (BarId)  
);

CREATE TABLE C -- Subtype of ‘Foo’.
(
    CId INT NOT NULL, -- PK and FK.
    Z   INT NOT NULL, -- Particular column.  
    CONSTRAINT PK_C             PRIMARY KEY (CId),
    CONSTRAINT FK_from_C_to_Foo FOREIGN KEY (FooId)
        REFERENCES Foo (FooId)  
);

    使用这种结构，您可以避免在基表（或关系）中存储 NULL 标记，这会给您的数据库带来歧义。

    完整性、一致性和其他考虑

    Once you are implementing your database, you must ensure that (a) each exclusive supertype row is always complemented by its corresponding subtype counterpart and, in turn, guarantee that (b) such subtype row is compatible with the value contained in the supertype discriminator column. Therefore, it is quite convenient to employ ACID TRANSACTIONS in order to make sure that these conditions are met in your database.

    You should not give up the logical soundness, self-expressivity and accuracy of your database, these are aspects that decidedly make your database more solid.

    The two previously posted answers already include pertinent points that are certainly worth taking into account when designing, creating and managing your database and its application program(s).

    Retrieving data by way of VIEW definitions

    You can set up some views that combine columns of the different supertype-subtype groups, so that you can retrieve the data at hand without, e.g., writing the necessary JOIN clauses every time. In this way, you can SELECT directly FROM the VIEW (a derived relation or table) of interest with ease.

    As you can see, “Ted” Codd was, undoubtedly, a genius. The tools he bequeathed are quite strong and elegant, and, of course, are well integrated with each other.

    Related resources

    If you want to analyze some extensive database which involves supertype-subtype relationships, you would find of value the extraordinary answers proposed by @PerformanceDBA to the following Stack Overflow questions:

    Note

    1. 信息建模集成定义( IDEF1X ) 是一种高度推荐的数据建模技术，于1993 年 12 月由美国国家标准与技术研究院 ( NIST )确立为标准。它完全基于(a) EF Codd 博士撰写的早期理论材料；( b)由PP Chen 博士开发的数据的实体关系视图；以及(c) Robert G. Brown 创建的逻辑数据库设计技术。值得注意的是，IDEF1X 是通过一阶逻辑形式化的。

    • 6

相关问题