代码之家 › 专栏 › 技术社区 › gregmac

层次组的数据库架构

database-design database sql-server

gregmac · 技术社区 · 16 年前

我正在研究一个数据库层次结构的数据库设计,它是一个更大系统的基础。每个组可以包含其他组,也可以将“devices”作为叶对象(设备下没有任何内容)。

正在使用的数据库是MS SQL 2005。(尽管在MS SQL 2000中工作是一个额外的好处;但目前,一个需要MS SQL 2008的解决方案是不可行的)。

有不同类型的组,这些组需要是动态的,并且在运行时由用户定义。例如,组类型可以是“customer”、“account”、“city”或“building”、“floor”,并且每种类型都将具有一组不同的属性,这些属性由用户定义。还将应用业务规则-例如,一个“楼层”只能包含在一个“建筑”组下,而且这些规则在运行时是可定义的。

许多应用程序功能都来自于基于这些组运行报告,因此需要有一种相对快速的方法来获取包含在某个组(以及所有子组)中的所有设备的列表。

存储组使用 modified pre-order tree traversal 技术的优点是速度快,但缺点是它相当复杂和脆弱——如果外部用户/应用程序修改数据库,就有可能完全崩溃。我们也在实现一个ORM层,而且这个方法在大多数ORM库中使用关系似乎很复杂。

使用 common table expressions 而“标准”的id/parentid组关系似乎是避免运行多个递归查询的有效方法。这种方法有什么缺点吗?

至于属性,存储它们的最佳方法是什么?一张又长又窄的桌子,与小组有关?像“name”这样的公共属性应该存储在groups表中而不是attributes表中吗(很多时候,该名称将是所有需要显示的内容)?

使用这种方法是否会出现性能问题(假设在一个合理的硬件上,每个组平均有6个属性,平均有10个并发用户,例如四核Xeon 2 GHz、4GB RAM,对任何其他进程进行折扣),平均为2000组?

请随意提出一个完全不同于我在这里概述的模式。我只是想说明我关心的问题。

4 回复 | 直到 16 年前

Jonathan Rupp 16 年前

我建议您实际构建最容易维护的方式(“标准”父/子设置),并在上面运行一些基本基准。

您会惊讶于数据库引擎如何使用正确的索引,特别是如果您的数据集可以放入内存中。

假设每个组有6个属性,2000个组和30个字节/属性,您所说的是360kb*预期的项目/组——图400kb。如果您希望拥有1000个项目/组,那么您只需要查看400MB的数据——这些数据可以在内存中毫无问题地容纳,而且数据库是 快速的 当所有数据都在内存中时在联接处。

Community Justin Hirsch 7 年前

公共表表达式将让您得到具有父子关系的组的列表。 Here 是将CTE用于其他应用程序的存储过程示例。它相当有效,但要注意以下注意事项:

如果一个部件在层次结构中出现多次,它将在每个位置报告。您可能需要对结果进行后期处理。
CTE有些迟钝,提供有限的范围来过滤查询中的结果-CTE在SELECT语句中可能不会出现多次。

Oracle的Connect By比CTE更灵活,因为它对查询结构的限制并没有CTE那么多,但是如果您使用的是SQL Server,这将不是一个选项。

如果您需要对中间结果做一些巧妙的工作,那么就编写一个存储过程,该存储过程使用CTE将原始查询获取到一个临时表中,并从中对其进行处理。选择“进入”将最小化由此产生的流量。结果表将在缓存中,因此对它的操作将相当快速。

一些可能的物理优化可以帮助:

父级上的聚集索引,因此为一个父级使用较少的I/O。
大量RAM和(取决于BOM表的大小)64位具有更多RAM的服务器,以便主物料清单表可以缓存在核心中。在32位O/S上,/3g引导开关是您的朋友,对于数据库服务器来说没有真正的缺点。
二氯甲烷 Pintable可以帮助强制数据库管理器将表保存在缓存中。

父属性类型属性编码表不能很好地处理CTE,因为如果包含属性表,则在行计数中会出现组合爆炸。这将排除查询中过滤属性的任何业务逻辑。最好直接将属性存储在BOM表条目上。

AJ. 16 年前

预排序树遍历非常方便。通过使用触发器使遍历数保持最新,可以使其保持健壮。

我使用的一种类似的技术是保存一个单独的表(祖先_id,后代_id),其中列出了所有的祖先和后代。这几乎和预先排序的遍历数一样好。

使用单独的表很方便,因为即使它引入了额外的联接,也会将复杂性消除到单独的表中。

BuggyFunBunny 16 年前

修改后的预订单本质上是JoeCelko的嵌套集方法。他的书“树和等级……”涵盖了邻接列表和N,并描述了各自的优缺点。通过适当的索引,邻接表的CTE得到了最平衡的性能。如果你主要是为了阅读,那么ns会更快。

你所描述的似乎是一个物料清单处理器。虽然不是百万美元,但Graeme Birchall有一本免费的DB2书籍,其中有一章是关于使用CTE进行层次结构处理的(语法实际上是相同的,IIRC,因为ANSI语法采用了DB2,然后采用了M美元): http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V95CK.PDF