数据库表设计-邻接表、路径枚举、嵌套集、闭包表

SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL。

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

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

CREATE TABLE Area ([id] [int]  NOT NULL,[name] [nvarchar]  (50) NULL,[parent_id] [int]  NULL,[type] [int]  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	小新	这个视情况而定吧

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

SELECT c1.*, c2.* FROM comments c1 LEFT OUTER JOIN comments c2 ON c2.parent_id = c1.comment_id;

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

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

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

一、路径枚举

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

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一的祖先，可以这么做：

SELECT * FROM comments AS c WHERE '1/4/5/7' LIKE c.path   关键词：数据库表设计-邻接表、路径枚举、嵌套集、闭包表