数据库表设计，没有最好只有最适合（邻接表、路径枚举、嵌套集、闭包表） - 小小情意_H5之家

我们在设计数据库的时候，是否会突破常规，找到最适合自己需求的设计方案，下面来举个例子：

常用的邻接表设计，都会添加一个 parent_id 字段，比如区域表（国、省、市、区）：

CREATE TABLE Area ( , (50) NULL, , );

name:地域的名称， parent_id 是父ID，省的父ID是国，市的父ID 为省，以此类推。

type 是区域的阶级： 1：国，2：省，3：市，4：区

在层级比较确定的情况下，这么设计表格没有什么问题，调用起来也很方便。

但是使用这种邻接表设计方式，并不能满足所有的需求，当我们不确定层级的情况下，假设我有下面一个评论结构：

用邻接表记录这个评论的数据（comments 表）：

comment_id parent_id author comment

1 0 小明我不大认同这个观点

2 1 小张我也不认同

3 2 小红我同意楼上

4 1 小全你为什么不认同呢

5 4 小明我以前遇到过这情况

6 5 小张那也不代表你所说是对的

7 5 小新这个视情况而定吧

大家有没发现，这么设计表，如果要查询一个节点的所有后代，是很难实现的，你可以使用关联查询来获取一条评论和他的后代：

comments c2 ON c2.parent_id = c1.comment_id;

然而这个查询只能获取两层的数据。这种树的特性就是可以任意深地拓展，你需要有相应的方法来获取它的深度数据。比如，可能需要计算一个评论分支的数量，或者计算一个机械设备的所有的总开销。

某些情况下，在项目中使用邻接表正好适用。邻接表设计的优势在于能快速的获取一个给定节点的直接父子节点，它也很容易插入新节点。如果这样的需求就是你的项目对于分层数据的全部操作，那使用邻接表就可以很好的工作了。

遇到上述的树模型，有几种方案是可以考虑下的：路径枚举、嵌套集以及闭包表。这些解决方案通常看上去比邻接表复杂很多，但它们的确使得某些使用邻接表比较复杂或很低效的操作变得更简单。如果你的项目确实需要提供这些操作，那么这些设计会是邻接表更好的选择。

一、路径枚举

在comments 表中，我们使用类型varchar 的path 字段来替代原来的parent_id 字段。这个path 字段所存储的内容为当前节点的最顶层祖先到它的自己的序列，就像UNIX的路径一样，你甚至可以使用 'http://www.cnblogs.com/' 作为路径的分隔符。

comment_id path author comment

1 1 小明我不大认同这个观点

2 1/2 小张我也不认同

3 1/2/3 小红我同意楼上

4 1/4 小全你为什么不认同呢

5 1/4/5 小明我以前遇到过这情况

6 1/4/5/6 小张那也不代表你所说是对的

7 1/4/5/7 小新这个视情况而定吧

你可以通过比较每个节点的路径来查询一个节点祖先。比如：要找到评论#7，路径是 1/4/5/7一的祖先，可以这么做：

comments c.path ;

这句话查询语句会匹配到路径为 1/4/5/%，1/4/% 以及 1/% 的节点，而这些节点就是评论#7的祖先。

同时还可以通过将LIKE 关键字两边的参数互换，来查询一个给定节点的所有后代。比如查询评论#4，路径path为 ‘1/4’ 的所有后代，可以使用如下语句：

comemnts ;

这句查询语句所有能找到的后台路径分别是：1/4/5、1/4/5/6、1/4/5/7。

一旦你可以很简单地获取一棵子树或者从子孙节点到祖先节点的路径，你就可以很简单地实现更多的查询，如查询一颗子树所有节点上值的总和。

插入一个节点也可以像使用邻接表一样地简单。你所需要做的只是复制一份要插入节点的父亲节点路径，并将这个新节点的ID追加到路径末尾即可。

路径枚举也存在一些缺点，比如数据库不能确保路径的格式总是正确或者路径中的节点确实存在。依赖于应用程序的逻辑代码来维护路径的字符串，并且验证字符串的正确性开销很大。无论将varchar 的长度设定为多大，依旧存在长度的限制，因而并不能够支持树结构无限扩展。

二、嵌套集

嵌套集解决方案是存储子孙节点的相关信息，而不是节点的直接祖先。我们使用两个数字来编码每个节点，从而表示这一信息，可以将这两个数字称为nsleft 和 nsright。

每个节点通过如下的方式确定nsleft 和nsright 的值：nsleft的数值小于该节点所有后代ID，同时nsright 的值大于该节点的所有后代的ID。这些数字和comment_id 的值并没有任何关联。

确定这三个值（nsleft，comment_id，nsright）的简单方法是对树进行一次深度优先遍历，在逐层深入的过程中依次递增地分配nsleft的值，并在返回时依次递增地分配nsright的值。得到数据如下:

comment_id nsleft nsright author comment

1 1 14 小明我不大认同这个观点

2 2 5 小张我也不认同

3 3 4 小红我同意楼上

4 6 13 小全你为什么不认同呢

5 7 12 小明我以前遇到过这情况

6 8 9 小张那也不代表你所说是对的

7 10 11 小新这个视情况而定吧