在多个表中报告层次结构 [英] Reporting Hierarchy in Mutltiple Tables

问题描述

我正在看一个使用多个表来表示报告层次结构的应用程序的怪异性，但是这些表格是相同的。层次结构底部的表具有最多的记录，每个记录都有一个ParentID到其上面的汇总表，最终在顶部汇总表中最多只添加一个。

I am looking at quite a monstrosity of an application that uses several tables to represent a reporting hierarchy, but eacvh of these tables is identical. The table at the bottom of the hierarchy has the most records, each of which has a ParentID to the rollup table above it, eventually all adding up to only one total in the top rollup table.

我非常困扰于使用硬编码连接和表名称的if代码块的精神错乱，我正在努力地认识到不使用单个表的一些理智原因，每行中都有一个levelID ，而不是每个级别的一个表，对于所有这些级别，或至少在同一个表上的几个视图。后者是因为数据库被设计为在MSAccess中使用，它不允许别名的子查询AFAIK。

I am plagued to insanity by gargantuan 'if' blocks of code with hard-coded joins and table names, and I am trying hard to recognise some sane reason for not using a single table, with a levelID in each row, instead of one table for each level, for all these levels, or at least several views on the same table. The latter because the database was designed to be used in MSAccess, which doesn't allow aliased sub-queries AFAIK.

推荐答案

是这些表在其语义或者形式上都一样吗？如果这两种表格在语法上是相同的，那么单一的表格解决方案可能远远超过了，但是我不清楚你的情况是否确定。

Are the tables all the same in their semantics or just in their form? If the tables are identical in both form and semantics, then a single table solution is probably far superior, but I don't know enough about your case to say for sure.

在SQL表中表示层次结构可能是一个很大的挑战。幸运的是，报告层次结构通常足够小且足够稳定，以便各种技术可以工作。

Representing a hierarchy in an SQL table can be quite a challenge. Fortunately, reporting hierarchies are generally small enough and stable enough so that a variety of techniques will work.

最简单的技术是邻接列表模型。在这个模型中，有两列其中之一指的是另一列。我会把它们称为MyTable（ID，ParentID）。在一个实际的情况下，Mytable将有其他列。 ParentID参考ID在同一个表的不同行。这很容易实现，易于更新。做汇总可能很痛苦。

The simplest technique goes by the name "adjacency list" model. In this model, there are two columns one of which refers to the other. I'll call them MyTable(ID, ParentID). In a real case, Mytable will have other columns. ParentID references ID in a different row of the same table. This is easy to implement, and easy to update. It can be a pain to do rollups.

另一种技术是名为嵌套集。称它为MyTable（ID，lft，rgt，Level）。 （级别多余，经常省略）。这里我们有两列（lft和rgt），它们显示了行适合层次结构的位置，因为lgt和rgt嵌套在所讨论节点的所有祖先的lft和rgt中。这种技术很难更新。很容易做汇总，查找子树，祖先路径和许多其他类型的查询。

Another technique goes by the name "nested sets". Call it MyTable (ID, lft, rgt, Level). (Level is redundant, and often omitted). Here we have two columns (lft and rgt) that show where the row fits into the hierarchy, because lgt and rgt are nested inside the lft and rgt of all the ancestors of the node in question. This technique is hard to update. It's easy to do rollups, and to find subtrees, and ancestor paths, and lots of other types of queries.

第三种平铺层次结构的方法。在这种技术中，层次结构的每个级别都有一个自己的命名列，每行都将其整个祖先一直显示回层次结构的顶点。这里我们有MyTable（ID，部门，部门，团队，团队）。部门，部门，团队和团队都是层级结构。对于通过点访问数据的用户和点击下拉界面的用户来说，这是最容易的，因为如果列名选择得当，他们没有什么可以学习的。它需要每个级别的名称。它不能很好地适应不确定的层次。它有很多的冗余。一般来说，可以自动从一个存储层次结构列表形式或嵌套集合形式的表生成展开的平均化。

A third way to "flatten the hierarchy". In this technique, each level of the hierarchy has a named column of its own, and each row displays its entire ancestry all the way back to the apex of the hierarchy. Here we have MyTable (ID, Division, Department, Group, Team). Division, Department, Group and Team are all levels of the hierarchy. This is ultimately easy for users who access the data via a point and click drill down interface, because there's nothing for them to learn, if the column names are chosen well. It requires a name for each level. It does not adapt well to indefinite levels of hierarchy. It's got a lot of redundancy. In general flattened hiearachies are generated automatically from a table that stores hierarchies in adajency list form or nested set form.

任何一个都是层次结构中每个级别分离表的好选择。

Any one of these are a good alternative to separate tables for every level in the hierarchy.

这篇关于在多个表中报告层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！