传智播客－Tree in SQL（译文）（2）

 

Table 1 is denormalized in several ways. We are modeling both the personnel and the organizational chart in one table. But for the sake of saving space, pretend that the names are job titles and that we have another table that describes the personnel that hold those positions.
Table 1 在几个方面显得不够规范。我们把员工和组织图构建在一张表里。但是基于节省空间的原因，假设这张表（Table 1）里姓名代表职位名称而且我们有另一张表用来描述具有这些职位的人事信息。

Another problem with the adjacency list model is that the boss and employee columns are the same kind ( names of personnel) and therefore should be shown in only one column in a normalized table. To prove that this is not normalized, assume that "Chuck" changes his name to "Charles;" you have to change his name in both columns and several places. The defining characteristic of a normalized table is that you have one fact in one place at one time.
邻接列表模式的另一个问题是，上司（boss）和雇员（employee）栏位是同一类型（职员的名字）因此在一个规范的表中（这两者）应该仅用一个栏位表示。为了证明这样做不那么规范，假设“Chuck”改名为“Charles”，你不得不在很多地方（这里应该指的是在人事表中的多条记录）在这两个栏位都要更改他的名字。规范表的特性定义是你在某一个时刻某一个位置只有一个事实（描述）。

The final problem is that the adjacency list model does not model subordination. Authority flows downhill in a hierarchy, but if I fire Chuck, I disconnect all of his subordinates from Albert. There are times (such as water pipes) where this is true, but that situation is not expected in this case.
最后的问题是邻接列表模式没能构建下属（模型）。在层级结构中权力的流向是向下倾斜的（即越往下/后权力越小），但是如果我把Chuck解雇了，从Albert起就断开了与Chuck的所有下属的连接。很多时候这种事情（例如水管）都会发生，但是在这个案例中不应该出现这种情况。

To show a tree as nested sets, replace the nodes with ovals, then nest subordinate ovals inside each other. The root will be the largest oval and will contain every other node. The leaf nodes will be the innermost ovals with nothing else inside them and the nesting will show the hierarchical relationship. The rgt and lft columns (I cannot use the reserved words LEFT and RIGHT in SQL) are what show the nesting.
为了以嵌套集合形式表现树（结构），以“椭圆”取代这些节点，然后在每个椭圆里构建下属椭圆（模型）。其根部是最大的椭圆且可以包含所有其他的节点。叶子节点将是最靠里的椭圆，里面什么也没有，于是这种嵌套将能够表示层级结构关系。rgt和lft栏位（我不能使用SQL里的保留关键字LEFT和RIGHT）描述了这种嵌套（关系）。

If that mental model does not work, then imagine a little worm crawling counter-clockwise along the tree. Every time it gets to the left or right side of a node, the worm numbers it. The worm stops when it gets all the way around the tree and back to the top.
如果这种思考模式不能在（你的）脑海中正确构想，那么（你可以）想象（有）一个小蠕虫沿顺时钟方向爬行（遍历了）该树结构。每一次它到达一个节点的左边或右边，便计数一次。当这调蠕虫遍历了该树的所有路径且返回（树的）顶部时它便停止（行动）。

This model is a natural way to show a parts explosion, because a final assembly is made of physically nested assemblies that break down into separate parts.
这种模式以一种自然的方式表现了每一部分的扩张，因为最终的集合装配是由（可以）分解成独立部分的物理性内嵌装配组件组合而成。

At this point, the boss column is both redundant and denormalized, so it can be dropped. Also, note that the tree structure can be kept in one table and all the information about a node can be put in a second table, and you can join both on employee number for queries.
就这一点而言，上司（boss）栏位既多余也显得不够规范，因此可以取消该栏位。同样，注意到树结构可以在一张表里保存而所有关于每个节点的信息可以保存在第二张表里，你可以在查询时用雇员编号连接这两张表。

To convert the graph into a nested sets model think of a little worm crawling along the tree. The worm starts at the top -- the root -- and makes a complete trip around the tree. When it comes to a node, it puts a number in the cell on the side that it is visiting and increments its counter. Each node will get two numbers -- one for the right side and one for the left. (Computer Science majors will recognize this as a modified preorder tree traversal algorithm.) Finally, drop the unneeded "Personnel.boss" column, which represents the edges of a graph.
要将图表转换成内嵌集合模式，（可以在脑海中）假想一条小蠕虫沿着这棵（集合）树蜿蜒爬行（的情形）。这条蠕虫从顶部－－根开始，然后围绕这棵树爬行一周后结束路程。每当它到达一个节点，它就在该节点单元的一边放置一个数字并增加其计数。每个节点都将有两个数字－－一个在左边一个在右边。（计算机科学专业的人将会意识到这就像一个改良的preorder tree traversal 算法。）最后，放弃用来表示图边线（关于图理论的各个概念请参见作者写的《Trees and Hierarchies in SQL for Smarties》）的不必要的“Personnel.boss”栏位。