我正在为用作更大系统基础的组层次结构进行数据库设计。每个组可以包含其他组，也可以包含"设备"作为叶对象(设备下方没有任何内容)。

使用的数据库是MS SQL2005. (尽管在MS SQL 2000中工作将是一个额外的收获；不幸的是，目前需要MS SQL 2008的解决方案并不可行)。

有不同类型的组，并且这些组需要在运行时由用户动态定义。例如，组类型可能是"客户"，"帐户"，"城市"或者"建筑物"，"楼层"，并且每种类型都将具有由用户定义的不同属性集。也将应用业务规则，例如，"地板"只能包含在"建筑"组下面，并且这些规则也可以在运行时定义。

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

使用修改后的顺序树遍历技术存储组的好处是速度快，但是缺点是如果外部用户/应用程序修改数据库相当复杂且脆弱，则有可能完全损坏。我们还实现了一个ORM层，并且这种方法似乎在大多数ORM库中使用关系都会变得复杂。

使用公用表表达式和"标准" id / parentid组关系似乎是避免运行多个递归查询的有效方法。这种方法有什么缺点吗？

至于属性，最好的存储方式是什么？与小组相关的狭长表吗？是否应将通用属性(如"名称")存储在组表中，而不是存储在属性表中(很多时候，名称将是显示所需的全部内容)？

使用这种方法是否会出现性能问题(假设在合理的硬件(例如四核Xeon 2 Ghz，4GB内存)上，平均有2000个组的平均值很高，每个组平均6个属性，并且平均有10个并发用户。 ，打折其他任何流程)？

随意提出与我在此处概述的方案完全不同的方案。我只是想举例说明我所关注的问题。

解决方案

我建议我们实际上构造一种最容易维护的方式("标准"父/子设置)，并至少在其上运行一些基本基准测试。

我们会惊讶于数据库引擎如何使用正确的索引，特别是如果数据集可以容纳到内存中时。

假设每组6个属性，2000个组和30个字节/属性，那么我们说的是360KB *预期项目/每组-图400KB。如果我们希望每组有1000个项目，则只查看400MB的数据-可以毫无问题地放入内存中，并且当所有数据都在内存中时数据库可以快速进行联接。

公用表表达式将使我们获得具有父子关系的组的列表。这是针对不同应用程序使用CTE的存储过程的示例。这是相当有效的，但请注意以下警告：

• 如果零件在层次结构中出现不止一次，则将在每个位置报告该零件。我们可能需要对结果进行后处理。
• CTE有点笨拙，并且在查询中筛选结果的范围有限-CTE在select语句中可能不会出现多次。

Oracle的CONNECT BY在某种程度上更加灵活，因为它对查询结构的限制不如CTE那样多，但是如果我们使用的是SQL Server，这将不是一个选择。

如果我们需要对中间结果进行任何巧妙的处理，请编写一个使用CTE的存储过程，以将原始查询获取到临时表中并从中进行处理。 SELECT INTO将最大程度地减少由此引起的流量。结果表将在高速缓存中，因此对其进行的操作将相当快。

一些可能的物理优化可能会有所帮助：

• 父级上的聚集索引，因此，为父级导出子节点使用的I / O更少。
• 大量的RAM和(取决于BOM表的大小)具有更多RAM的64位服务器，以便可以在核心中缓存主BOM表。在32位O / S上，/ 3G引导开关是朋友，对数据库服务器没有任何不利影响
• DBCC PINTABLE可以帮助强制数据库管理器将表保留在高速缓存中。

父-属性类型-属性编码表不能很好地与CTE配合使用，因为如果包括属性表，行数将出现组合爆炸式增长。这将排除查询中按属性过滤的任何业务逻辑。
直接将属性存储在BOM表条目中会更好。

预订树遍历非常方便。我们可以通过使用触发器保持遍历数为最新来增强鲁棒性。

我使用的类似技术是保留一个单独的(ancestor_id，descendant_id)表，其中列出了所有祖先和后代。这几乎与预定遍历数一样好。

使用单独的表很方便，因为即使它引入了额外的联接，也确实消除了单独表的复杂性。

修改后的预购本质上是Joe Celko的Nested Sets方法。他的书《树和层次结构...》涵盖了邻接表和NS，并分别描述了它们的优缺点。通过适当的索引，邻接表的CTE可获得最平衡的性能。如果我们打算读大部分文章，那么NS会更快。

我们似乎要描述的是物料清单处理器。虽然不是M $，但Graeme Birchall拥有一本免费的DB2书籍，其中有一章使用CTE进行层次结构处理(语法实际上是相同的，即IIRC，因为ANSI语法采用了DB2，然后M $采用了DB2)：http：// mysite .verizon.net / Graeme_Birchall / cookbook / DB2V95CK.PDF