数据库设计--自联结模式（转）

原创：胖子刘（转载请注明作者和出处，谢谢）

（二）自联结模式

自联结模式，也可以看作是“主从模式”的一种特殊情况（或者说是“变形”），它在一张表内实现了“一对多关系”，并且可以根据业务需要实现“有限层”或者“无限层”的主从嵌套。

这种模式用得最多的情况就是实现“树形结构”数据的存储，比如各大网站上常见的细分类别、应用系统的组织结构、Web系统的菜单树等都能用到这种模式。

自联结模式有很多变体，且每种变体的优缺点同样鲜明。由于本连载的重点在于对跨行业通用数据库模型设计进行分析，所以对每种具体模式的细节方面的设计技巧不能作详细论述，请大家原谅。这里仅举两个例子说明：

1. 简单自联结

简单自联结，就是在一个表里设置当前类ID、父类ID，同时规定最顶层类的父类ID为一个固定值（比如0），在生成树的时候使用递归算法，记录的前后顺序通过“排序号”字段来确定。

这个表用来存储菜单树很方便。首先会有一个主菜单，主菜单下有子菜单，子菜单下面又有孙菜单……菜单的数量不确定、层级不确定，用户可以在任意菜单下增加新的子菜单，或者删除某个子菜单及其下的所有孙菜单……这种设计方式很多人都会用到，短小精悍、维护方便、且完全满足用户需求，而且树的层次不限，扩展起来非常容易。这些都是它的优点。

它的缺点就是树结构的生成由于使用了递归算法，必然要对该表进行多次读取（读取的次数 = 表内的记录数 – 最深层级的记录数），多次读取就来了比较低的运行效率，当表里的记录很多的时候，这个缺点可以称得上是致命的。

于是就有了下面的这种设计模式。

2. 扩展自联结

扩展自联结，与简单自联结的最大区别就是通过附加冗余字段来避免递归运算，所要实现的主要目标就是一次读取就能生成整个树，一次提高树的生成效率。

但是，鱼与熊掌不可兼得，凡事都有两面性。

生成树的效率提高了，增删改表内记录的算法就会相应复杂，并且树的层数也变为有限的了。

所以在此类设计的时候，大家还是要认真分析业务需求，看看实际业务的重点在什么地方，然后再作具体设计。比如一些门户网站在首页显示产品类别是业务重点，那么我们在设计的时候就要尽可能的提高生成树的效率，采取扩展自联结模式；相反，一些基于Web的业务系统，要求对菜单树的增删改维护操作尽量简单，由于菜单的数目不多，所以菜单树的生成效率不是瓶颈，那么我们设计的时候就可以采取简单自联结模式。

关于附加冗余字段实现扩展自联结的方法很多，网上也有很多这方面的帖子，大家可以到Google上搜一下。

在这里仅举一个例子如下：

这个设计与前面的设计最大的区别就是排序字段，前面的简单自联结用了一个整数型的字段来实现排序，这里用了一个Varchar20型的字段“层级代码”来实现大排序。这个字段的取值两位一组，代表一层，假定最深为5层，初始值为0000000000。

按照这样的设计，表内的数据记录可能就是这样的：

ID TypeName ParentID TypeLevel

1 根类别 0 000000

2 类别1 1 010000

3 类别1.1 2 010100

4 类别1.2 2 010200

5 类别2 1 020000

6 类别2.1 5 020100

7 类别3 1 030000

8 类别3.1 7 030100

9 类别3.2 7 030200

10 类别1.1.1 3 010101

……

现在按TypeLevel字段进行排序，执行如下SQL语句：SELECT * FROM TMP_Type ORDER BY TypeLevel

列出记录集如下：

ID TypeName ParentID TypeLevel

1 总类别 0 000000