为了使这个问题对未来的读者有用,我将使用通用数据模型来说明我面临的问题.
我们的数据模型由3个实体组成,它们应标记为A,B和C.为了简单起见,它们的所有属性都是int类型.
实体A具有以下属性：D,E和X;
实体B具有以下属性：D,E和Y;
实体C具有以下属性：D和Z;
由于所有实体共享公共属性D,因此我决定应用类型/子类型设计.
重要提示：实体是互斥的！这意味着实体是A或B或C.
问题：
题：
如果可能的话,我想使用上述特性来进一步优化我的设计.
说实话,我不知道怎么做,也不知道从哪里开始尝试,因此这篇文章.
解决方法@H_403_28@
只要这个问题是
Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?的延续,这本身就是
Don’t know how to transform variable entity into relational table的延续,我会问：你究竟想要优化什么？存储？对象模型？查询复杂性？查询性能？在优化一个方面与另一个方面时存在权衡,因为您无法同时优化所有方面.
我完全同意Remus’s points关于：
>每种方法都有利弊(即永远存在的“依赖”因素),以及
>首要任务是数据模型的效率(低效的数据模型无法通过干净和/或高效的应用程序代码进行纠正)
也就是说,您所面临的选择是在以下之间,按照最不规范化的顺序排列到大多数规范化：
>将属性E推广到基类表
>将其保存在多个子类型表中
>将E完全归一化到与C相同的新的中间子类表,A和B将直接成为(@MDCCL’s answer)的子类
让我们看看每个选项：
将属性E移动到基本类型表
投票站
>降低了需要E而不是X,Y或Z的查询的查询复杂性.
>由于没有JOIN,对于需要E而不是X,Y或Z(尤其是聚合查询)的查询,效率可能更高.
>可以在(D,E)上创建索引(如果是这样,可能是(D,E)上的过滤索引,其中EntityType<> C,如果允许这样的条件)
CON外
>无法将E标记为NOT NULL
>在基类型表上需要额外的CHECK CONSTRAINT以确保当EntityType = C时E IS为NULL(尽管这不是一个大问题)
>当EntityType = C时,需要向数据模型的用户介绍为什么E必须为NULL,甚至应该完全忽略.
>当E是固定长度类型时效率略低,并且大部分行用于C的EntityType(即不使用E因此它为NULL),并且不使用列上的SPARSE选项或数据压缩聚集指数
>对于不需要E的查询,效率可能较低,因为基类型表中存在E将增加每行的大小,从而减少可以适合数据页的行数.但这高度依赖于E的确切数据类型,FILLFACTOR,基本类型表中有多少行等.
在每个子类型表中保留属性E.
投票站
>更清洁的数据模型(即不必担心教育其他人为什么不应该使用基类型表中的列E,因为“它确实不存在”)
>可能更接近于物体模型
>如果这是实体的必需属性,则可以将列标记为NOT NULL
>在基类型表上不需要额外的CHECK CONSTRAINT,以确保当EntityType = C时E IS为NULL(尽管这不是一个巨大的收益)
CON外
>需要JOIN到子类型表以获取此属性
>由于JOIN,在需要E时可能效率稍低,这取决于你拥有的A B行数,而不是有多少行.
>对于仅将实体A和B(而非C)作为相同“类型”处理的操作稍微更困难/复杂.当然,您可以通过一个View来实现这一点,该视图在A的JOINed表的SELECT和B的JOINed表的另一个SELECT之间执行UNION ALL.这将降低SELECT查询的复杂性,但对INSERT和UPDATE查询没有那么有用.
>根据特定查询及其执行频率,如果(D,E)上的索引确实有助于一个或多个常用查询,则可能会导致效率低下,因为它们无法一起编入索引.
将E归一化到基类和A& A之间的中间表.乙
(请注意,我确实喜欢@MDCCL’s answer作为一种可行的替代方案,具体取决于具体情况.以下并不是对该方法的严格批评,而是作为增加一些观点的手段 – 当然,我的评估方法是与我已经提出的两个选项相同的背景.这将使我更容易澄清我所看到的完全归一化与当前部分归一化方法之间的相对差异.)
投票站
>数据模型是完全标准化的(这不是任何固有的错误,因为它是RDBMS的设计目的)
>减少了需要A和B但不是C的查询的查询复杂度(即不需要通过UNION ALL加入两个查询)
CON外
>稍微占用更多空间(Bar表复制了ID,并且有一个新列,BarTypeCode)[可忽略不计,但需要注意的事项]
>查询复杂性略有增加,因为需要额外的JOIN才能到达A或B.
>增加锁定的表面区域,主要是在INSERT上(DELETE可以通过将外键标记为ON CASCADE DELETE来隐式处理),因为事务将在基类表(即Foo)上保持打开稍长一些[可忽略不计,但是要意识到]
>没有直接了解实际类型 – A或B – 在基类表中,Foo;它只知道可以是A或B的Br型：
这意味着,如果您需要对一般基本信息进行查询但需要按实体类型进行分类或过滤掉一个或多个实体类型,那么基类表没有足够的信息,在这种情况下您需要LEFT JOIN Bar表.这也会降低索引FooTypeCode列的有效性.
>没有一致的方法来与A& amp; B vs C：
这意味着,如果每个实体直接与基类表相关联,使得只有一个JOIN可以获得完整的实体,那么每个人都可以更快速,更轻松地建立熟悉数据模型的工作.查询/存储过程将采用通用方法,这使得它们更快地开发并且不太可能存在错误.一致的方法还使得将来更快更容易地添加新的子类型.
>可能不太适应随时间变化的业务规则：
意思是,事情总是会发生变化,如果它变得普遍适用于所有子类型,那么将E移动到基类表是相当容易的.如果实体性质的变化使得这种变化值得改变,那么将公共属性移动到子类型也很容易.将子类型分解为两个子类型(只需创建另一个SubTypeID值)或将两个或多个子类型合并为一个子类型就很容易了.相反,如果E后来成为所有子类型的共同属性怎么办？然后Bar表的中间层将毫无意义,增加的复杂性将是不值得的.当然,不可能知道这种变化是否会在5年甚至10年内发生,所以Bar表不一定,甚至不太可能是一个坏主意(这就是我说“可能不太适应”的原因) ).这些只是要考虑的要点;这是两个方向的赌博.
>可能不适当的分组：
这意味着,仅仅因为实体类型A和B之间共享E属性并不意味着A和B应该组合在一起.仅仅因为事物“看起来”相同(即相同的属性)并不意味着它们是相同的.
就像决定是否/何时进行非规范化一样,如何最好地处理这种特定情况取决于考虑数据模型使用的以下几个方面,并确保收益大于成本：
>每个EntityType将拥有多少行(假设高于平均增长率,至少看看未来5年)
>这些表(基类型和子类型)中的每一个在5年内会有多少GB？
>具体的数据类型是属性E.
>它只是一个属性,还是有几个甚至几个属性
>您需要哪些查询需要E以及执行的频率
>您将需要哪些不需要E的查询以及执行的频率
我认为我倾向于默认将E保留在单独的子类型表中,因为它至少是“更干净”.我会考虑将E移动到基类型表IF：大多数行不是用于C的EntityType;行数至少为数百万;并且我经常执行的查询需要E和/或从(D,E)上的索引受益的查询要么非常频繁地执行和/或需要足够的系统资源,以使索引减少整体资源利用率,或至少防止资源消耗的激增超过可接受的水平或持续足够长的时间以导致过度阻塞和/或死锁增加.
UPDATE
O.P. commented on this answer：
My employers changed the business logic,removing E altogether!
这种变化特别重要,因为它正是我所预测的可能发生在上面(第6个要点)的“在基类和A& B之间的中间表的归一化E”部分的“CONs”子部分中.具体问题是当这种变化发生时(并且它们总是如此)重构数据模型是多么容易/困难.有些人会争辩说任何数据模型都可以重构/改变,所以从理想开始.但是,虽然在技术层面上确实可以重构任何东西,但情况的实际情况是规模问题.
资源不是无限的,不仅仅是cpu /磁盘/ RAM,还有开发资源：时间和金钱.企业不断设定项目的优先级,因为这些资源非常有限.并且经常(至少在我的经验中),提高效率的项目(甚至系统性能以及更快的开发/更少的错误)优先于增加功能的项目.虽然这对我们技术人员来说是令人沮丧的,因为我们了解重构项目的长期利益是什么,但技术性较差的业务人员更容易看到新功能与新功能之间的直接关系,这只是业务的本质.收入.这可以归结为：“我们将在稍后回来解决这个问题”==“这个问题可能会在接下来的5 – 10年内出现,因为我们几乎总会有更重要的事情需要解决(具有讽刺意味的是,如由于我们尚未修复它而不断出现的支持案例)“.
考虑到这一点,如果数据的大小足够小,以便可以进行非常查询的更改,和/或您有一个足够长的维护窗口,不仅可以进行更改,还可以在发生变化时回滚错误,然后将E标准化为基类表和A& A之间的中间表. B子类表可以工作(尽管这仍然使您不必直接了解基类表中的特定类型(A或B)).但是,如果这些表中有数亿行,并且有大量代码引用这些表(在进行更改时必须进行测试的代码),那么通常比理想主义更实用.这是我多年来不得不面对的环境：9.87亿行&基类表中的615 GB,分布在18个服务器上.这么多代码击中了这些表(基类和子类表),存在很多阻力 – 主要来自管理层,但有时来自团队的其他成员 – 由于开发量的大小而进行任何更改需要分配的QA资源.
因此,再次,“最佳”方法只能逐个确定：您需要了解您的系统(即数据有多少,表格和代码如何相关),如何完成重构,以及人员您与之合作(您的团队和可能的管理层 – 您是否可以获得他们对此类项目的支持？).有一些变化,我一直在提及并计划1 – 2年,并采取多次冲刺/发布,以实现其中85％的实施.但如果你只有< 100万行,而不是很多代码绑定到这些表,那么你可能会开始更理想/“纯粹”的事情. 请记住,无论您选择哪种方式,至少应该注意该模型在未来两年的工作方式(如果可能的话).注意什么起作用,什么引起疼痛,即使它看起来像当时最好的想法(这意味着你也需要让自己接受搞砸 – 我们都这样做 – 这样你就可以诚实地评估痛点).并注意为什么某些决定有效或没有,以便您可以做出下次更有可能“更好”的决策:-).
我完全同意Remus’s points关于：
>每种方法都有利弊(即永远存在的“依赖”因素),以及
>首要任务是数据模型的效率(低效的数据模型无法通过干净和/或高效的应用程序代码进行纠正)
也就是说,您所面临的选择是在以下之间,按照最不规范化的顺序排列到大多数规范化：
>将属性E推广到基类表
>将其保存在多个子类型表中
>将E完全归一化到与C相同的新的中间子类表,A和B将直接成为(@MDCCL’s answer)的子类
让我们看看每个选项：
将属性E移动到基本类型表
投票站
>降低了需要E而不是X,Y或Z的查询的查询复杂性.
>由于没有JOIN,对于需要E而不是X,Y或Z(尤其是聚合查询)的查询,效率可能更高.
>可以在(D,E)上创建索引(如果是这样,可能是(D,E)上的过滤索引,其中EntityType<> C,如果允许这样的条件)
CON外
>无法将E标记为NOT NULL
>在基类型表上需要额外的CHECK CONSTRAINT以确保当EntityType = C时E IS为NULL(尽管这不是一个大问题)
>当EntityType = C时,需要向数据模型的用户介绍为什么E必须为NULL,甚至应该完全忽略.
>当E是固定长度类型时效率略低,并且大部分行用于C的EntityType(即不使用E因此它为NULL),并且不使用列上的SPARSE选项或数据压缩聚集指数
>对于不需要E的查询,效率可能较低,因为基类型表中存在E将增加每行的大小,从而减少可以适合数据页的行数.但这高度依赖于E的确切数据类型,FILLFACTOR,基本类型表中有多少行等.
在每个子类型表中保留属性E.
投票站
>更清洁的数据模型(即不必担心教育其他人为什么不应该使用基类型表中的列E,因为“它确实不存在”)
>可能更接近于物体模型
>如果这是实体的必需属性,则可以将列标记为NOT NULL
>在基类型表上不需要额外的CHECK CONSTRAINT,以确保当EntityType = C时E IS为NULL(尽管这不是一个巨大的收益)
CON外
>需要JOIN到子类型表以获取此属性
>由于JOIN,在需要E时可能效率稍低,这取决于你拥有的A B行数,而不是有多少行.
>对于仅将实体A和B(而非C)作为相同“类型”处理的操作稍微更困难/复杂.当然,您可以通过一个View来实现这一点,该视图在A的JOINed表的SELECT和B的JOINed表的另一个SELECT之间执行UNION ALL.这将降低SELECT查询的复杂性,但对INSERT和UPDATE查询没有那么有用.
>根据特定查询及其执行频率,如果(D,E)上的索引确实有助于一个或多个常用查询,则可能会导致效率低下,因为它们无法一起编入索引.
将E归一化到基类和A& A之间的中间表.乙
(请注意,我确实喜欢@MDCCL’s answer作为一种可行的替代方案,具体取决于具体情况.以下并不是对该方法的严格批评,而是作为增加一些观点的手段 – 当然,我的评估方法是与我已经提出的两个选项相同的背景.这将使我更容易澄清我所看到的完全归一化与当前部分归一化方法之间的相对差异.)
投票站
>数据模型是完全标准化的(这不是任何固有的错误,因为它是RDBMS的设计目的)
>减少了需要A和B但不是C的查询的查询复杂度(即不需要通过UNION ALL加入两个查询)
CON外
>稍微占用更多空间(Bar表复制了ID,并且有一个新列,BarTypeCode)[可忽略不计,但需要注意的事项]
>查询复杂性略有增加,因为需要额外的JOIN才能到达A或B.
>增加锁定的表面区域,主要是在INSERT上(DELETE可以通过将外键标记为ON CASCADE DELETE来隐式处理),因为事务将在基类表(即Foo)上保持打开稍长一些[可忽略不计,但是要意识到]
>没有直接了解实际类型 – A或B – 在基类表中,Foo;它只知道可以是A或B的Br型：
这意味着,如果您需要对一般基本信息进行查询但需要按实体类型进行分类或过滤掉一个或多个实体类型,那么基类表没有足够的信息,在这种情况下您需要LEFT JOIN Bar表.这也会降低索引FooTypeCode列的有效性.
>没有一致的方法来与A& amp; B vs C：
这意味着,如果每个实体直接与基类表相关联,使得只有一个JOIN可以获得完整的实体,那么每个人都可以更快速,更轻松地建立熟悉数据模型的工作.查询/存储过程将采用通用方法,这使得它们更快地开发并且不太可能存在错误.一致的方法还使得将来更快更容易地添加新的子类型.
>可能不太适应随时间变化的业务规则：
意思是,事情总是会发生变化,如果它变得普遍适用于所有子类型,那么将E移动到基类表是相当容易的.如果实体性质的变化使得这种变化值得改变,那么将公共属性移动到子类型也很容易.将子类型分解为两个子类型(只需创建另一个SubTypeID值)或将两个或多个子类型合并为一个子类型就很容易了.相反,如果E后来成为所有子类型的共同属性怎么办？然后Bar表的中间层将毫无意义,增加的复杂性将是不值得的.当然,不可能知道这种变化是否会在5年甚至10年内发生,所以Bar表不一定,甚至不太可能是一个坏主意(这就是我说“可能不太适应”的原因) ).这些只是要考虑的要点;这是两个方向的赌博.
>可能不适当的分组：
这意味着,仅仅因为实体类型A和B之间共享E属性并不意味着A和B应该组合在一起.仅仅因为事物“看起来”相同(即相同的属性)并不意味着它们是相同的.
就像决定是否/何时进行非规范化一样,如何最好地处理这种特定情况取决于考虑数据模型使用的以下几个方面,并确保收益大于成本：
>每个EntityType将拥有多少行(假设高于平均增长率,至少看看未来5年)
>这些表(基类型和子类型)中的每一个在5年内会有多少GB？
>具体的数据类型是属性E.
>它只是一个属性,还是有几个甚至几个属性
>您需要哪些查询需要E以及执行的频率
>您将需要哪些不需要E的查询以及执行的频率
我认为我倾向于默认将E保留在单独的子类型表中,因为它至少是“更干净”.我会考虑将E移动到基类型表IF：大多数行不是用于C的EntityType;行数至少为数百万;并且我经常执行的查询需要E和/或从(D,E)上的索引受益的查询要么非常频繁地执行和/或需要足够的系统资源,以使索引减少整体资源利用率,或至少防止资源消耗的激增超过可接受的水平或持续足够长的时间以导致过度阻塞和/或死锁增加.
UPDATE
O.P. commented on this answer：
My employers changed the business logic,removing E altogether!
这种变化特别重要,因为它正是我所预测的可能发生在上面(第6个要点)的“在基类和A& B之间的中间表的归一化E”部分的“CONs”子部分中.具体问题是当这种变化发生时(并且它们总是如此)重构数据模型是多么容易/困难.有些人会争辩说任何数据模型都可以重构/改变,所以从理想开始.但是,虽然在技术层面上确实可以重构任何东西,但情况的实际情况是规模问题.
资源不是无限的,不仅仅是cpu /磁盘/ RAM,还有开发资源：时间和金钱.企业不断设定项目的优先级,因为这些资源非常有限.并且经常(至少在我的经验中),提高效率的项目(甚至系统性能以及更快的开发/更少的错误)优先于增加功能的项目.虽然这对我们技术人员来说是令人沮丧的,因为我们了解重构项目的长期利益是什么,但技术性较差的业务人员更容易看到新功能与新功能之间的直接关系,这只是业务的本质.收入.这可以归结为：“我们将在稍后回来解决这个问题”==“这个问题可能会在接下来的5 – 10年内出现,因为我们几乎总会有更重要的事情需要解决(具有讽刺意味的是,如由于我们尚未修复它而不断出现的支持案例)“.
考虑到这一点,如果数据的大小足够小,以便可以进行非常查询的更改,和/或您有一个足够长的维护窗口,不仅可以进行更改,还可以在发生变化时回滚错误,然后将E标准化为基类表和A& A之间的中间表. B子类表可以工作(尽管这仍然使您不必直接了解基类表中的特定类型(A或B)).但是,如果这些表中有数亿行,并且有大量代码引用这些表(在进行更改时必须进行测试的代码),那么通常比理想主义更实用.这是我多年来不得不面对的环境：9.87亿行&基类表中的615 GB,分布在18个服务器上.这么多代码击中了这些表(基类和子类表),存在很多阻力 – 主要来自管理层,但有时来自团队的其他成员 – 由于开发量的大小而进行任何更改需要分配的QA资源.
因此,再次,“最佳”方法只能逐个确定：您需要了解您的系统(即数据有多少,表格和代码如何相关),如何完成重构,以及人员您与之合作(您的团队和可能的管理层 – 您是否可以获得他们对此类项目的支持？).有一些变化,我一直在提及并计划1 – 2年,并采取多次冲刺/发布,以实现其中85％的实施.但如果你只有< 100万行,而不是很多代码绑定到这些表,那么你可能会开始更理想/“纯粹”的事情. 请记住,无论您选择哪种方式,至少应该注意该模型在未来两年的工作方式(如果可能的话).注意什么起作用,什么引起疼痛,即使它看起来像当时最好的想法(这意味着你也需要让自己接受搞砸 – 我们都这样做 – 这样你就可以诚实地评估痛点).并注意为什么某些决定有效或没有,以便您可以做出下次更有可能“更好”的决策:-).
