我们在设计数据库的时候，会不会突破常规，找到最适合自己需求的设计方案呢？举个例子：在常用的邻接表设计中会增加一个parent_id字段，比如地区表（国家、省、市、区）：CREATETABLEArea([id][int]NOTNULL,[name][nvarchar](50)NULL,[parent_id][int]NULL,[type][int]NULL);name：地区名称，parent_id为父ID，省份父ID为国家，父ID为城市是省，等等。type是区域的类别：1：国家，2：省，3：城市，4：地区。当层次结构比较确定的时候，这样设计表是没有问题的，调用起来也很方便。但是，使用这种邻接表设计方法并不能满足所有要求。当我们不确定层次结构时，假设我有如下评论结构：使用邻接表来记录这条评论的数据（评论表）：你有没有注意到，所以设计表，如果要查询所有的后代一个节点的，很难实现，可以使用关联查询获取评论和他的后代：SELECTc1.*,c2.*FROMcommentsc1LEFTOUTERJOINcommentsc2ONc2.parent_id=c1.comment_id;但是这个查询只能获取两层数据。这种树的特点是可以扩展到任意深度，你需要有相应的方法来获取它的深度数据。例如，可能需要统计评论分支的数量，或者计算一个机械设备的总成本。在某些情况下，在项目中使用邻接表是恰到好处的。邻接表设计的优点是可以快速获取给定节点的直接父子节点，也便于插入新节点。如果这样的需求都是你的项目对分层数据的操作，那么使用邻接表就可以很好地工作。遇到上面的树模型，有几种方案可以考虑：路径枚举、嵌套集合、闭包表。这些解决方案通常看起来比邻接表复杂得多，但它们确实使使用邻接表的某些复杂或低效的操作变得更简单。如果你的项目确实需要提供这些操作，那么这些设计将是邻接表更好的选择。1.路径枚举在comments表中，我们使用varchar类型的path字段来代替原来的parent_id字段。该路径字段存储的内容是从当前节点的最顶层祖先到自身的顺序，就像UNIX路径一样，你甚至可以使用'/'作为路径分隔符。您可以通过比较每个节点的路径来查询节点的祖先。例如：要查找评论#7，路径是1/4/5/7的祖先，您可以这样做：SELECT*FROMcommentsAScWHERE'1/4/5/7'LIKEc.path||'%';该查询语句将匹配路径为1/4/5/%、1/4/%和1/%的节点，这些节点是注释#7的祖先。也可以通过交换LIKE关键字两侧的参数来查询给定节点的所有后代。例如查询评论#4，所有路径path为'1/4'的后代，可以使用如下语句：SELECT*FROMcomemntsAScWHEREc.pathLIKE'1/4'||'%';这条查询语句中能查到的所有背景路径分别是：1/4/5、1/4/5/6、1/4/5/7。一旦你可以很容易地得到一个子树或者从一个后代节点到一个祖先节点的路径，你就可以很容易地实现更多的查询，比如查询一个子树中所有节点的值之和。插入节点也可以像使用邻接表一样简单。您需要做的就是复制要插入的节点的父路径，并将新节点的ID附加到路径的末尾。路径枚举也有一些缺点，比如数据库不能保证路径的格式总是正确的或者路径中的节点确实存在。依赖应用程序的逻辑代码来维护路径的字符串，验证字符串的正确性代价高昂。不管varchar的长度设置多少，还是有长度限制的，所以不支持树结构扩展。2.NestedSetsnestedset解决方案是存储后代节点的相关信息，而不是节点的直系祖先。我们通过用两个数字对每个节点进行编码来表示此信息，我们可以称之为nsleft和nsright。每个节点通过以下方式确定nsleft和nsright的值：nsleft的值小于该节点所有后代的ID，nsright的值大于该节点所有后代的ID。这些数字与comment_id的值之间没有关联。确定这三个值（nsleft,comment_id,nsright）的一个简单方法是对树进行深度优先遍历，随着深度的增加递增赋nsleft的值，并赋nsright的值当你返回时递增。获取数据如下：一旦你将这些数字分配给每个节点，你就可以使用它们来查找指定节点的祖先和后代。例如，要搜索评论#4及其所有后代，您可以搜索评论#4的nsleft和nsright范围之间的节点ID，例如：SELECTc2.*FROMcommentsASc1JOINcommentsASc2ONc2.nsleftBETWEENc1.nsleftANDc1.nsrightWHEREc1.comment_id=4;例如搜索comments#6及其所有祖先，可以搜索在nsleft和nsright节点范围之间的#6的ID，例如：SELECTc2.*FROMcommentsASc1JOINcommentsASc2ONc1.nsleftBETWEENc2.nsleftANDc2.nsrightWHEREc1.comment_id=6;使用嵌套集设计的主要优点是，当你要删除一个非叶子节点时，它的后代会自动替换被删除的节点，成为它的直系祖先节点的直系后代。也就是说，已经自动减少了一层。然而，某些在邻接表设计中看似简单的查询，例如获取节点的直接父节点或直接后代，在嵌套集设计中变得更加复杂。在嵌套集合中，如果我们需要查询节点的直接父节点，我们会这样做，例如找到注释#6的直接父节点：SELECTparent.*FROMcommentsAScJOINcommentsASparentONc.nsleftBETWEENparent.nsleftANDparent.nsrightLEFTOUTERJOINcommentsASin_betweenONc.nsleftBETWEENin_between.nsleftANDin_between.nsrightANDinparent_betweenBetweenBetween。nsleftBETWEENin_between.nsleftANDin.nsrightANDinparent_betweenBetweennsrightWHEREc.comment_id=6ANDin_between.comment_idISNULL;简而言之，它有点复杂。使用嵌套集合来操作树，例如插入和移动节点，比其他设计复杂得多。插入新节点时，需要重新计算新插入节点的相邻兄弟节点、祖先节点及其祖先节点的兄弟节点，保证它们的左右值都大于新节点的左值。同时，如果新节点是非叶子节点，你还要检查它的后代。如果简单快速的查询是整个程序中最重要的部分，那么嵌套集合是最好的选择，这比操作单个节点要方便快捷得多。但是在嵌套集合中插入和移动节点比较复杂，因为需要重新分配左右值。如果您的应用程序需要频繁插入和删除节点，那么嵌套集可能不适合。3.ClosuretableClosuretable是一种简单而优雅的分层存储解决方案。它记录了树中所有节点之间的关系，而不仅仅是那些直接的父子节点。在设计评论系统时，我们额外创建了一个名为tree_paths的表，其中包含两列，每一列都指向评论中的一个外键。我们不再使用comments表来存储树的结构，而是在treepaths表中存储树中具有（祖先-后代）关系的任何节点对，即使这两者之间没有直接的父子关系节点；同时，我们还添加了一条指向节点本身的线。通过treepaths表检索祖先和后代比使用嵌套集更直接。例如，要获取评论#4的后代，只需在treepaths表中搜索其祖先为评论#4的行。获得后代也是如此。插入一个新的叶子节点，比如comment#6的子节点，首先要插入self-to-self关系，然后在treepaths表中搜索comment#6的后代节点，添加"本节点的“祖先”与新插入的节点“后代”关系（新节点ID应为8）：INSERTINTTOtreepaths(ancestor,descendant)SELECTt.ancestor,8FROMtreepathsAStWHEREt.descendant=6UNIONALLSELECT8,8;删除一个叶子节点，如comment#7,treepaths表中的所有后代都应该被删除Therowofcomment#7:DELETEFROMtreepathsWHEREdescendant=7;要删除一个完整的子树，比如comment#4及其所有后代，删除treepaths表中所有带有后代#4的行，以及那些评论#4行的后代。闭包表的设计比嵌套集更直接。两者都可以快速查询给定节点的祖先和后代，但闭包表可以更简单地维护层次信息。这两种设计都比使用邻接表或路径枚举来查询给定节点的直接后代和祖先更方便。但是，您可以优化闭包表以使其更容易查询直接父节点或子节点：将path_length字段添加到treepaths表。一个节点的自引用path_length为0，其直接子节点的path_length为1，下一级为2，依此类推。这样查询就方便多了。摘要：您应该使用哪种设计？每种设计都有自己的优点和缺点。如何选择设计取决于应用程序的哪些操作最需要性能优化。分层数据设计比较1.邻接表是最方便的设计，很多程序员都知道。2、如果你使用的数据库支持WITH或者CONNECTBYPRIOR递归查询，可以让邻接表的查询更加高效。3.枚举路径可以直观的展示祖先和后代之间的路径，但同时由于不能保证引用完整性，这种设计非常脆弱。枚举路径也使数据存储冗余。4.嵌套集是一个聪明的解决方案，但也许太聪明了，而且它不能确保参照完整性。***在对查询性能要求很高，其他要求一般的场合使用。5.闭包表是最通用的设计，上面的方案是唯一允许一个节点属于多棵树的方案。它需要额外的表来存储关系，并使用空间换时间的方案来减少运行过程中冗余计算带来的消耗。这几种设计方案只是我们日常设计的一部分，在开发中肯定会遇到更多的选择。选择哪一种需要切合实际，根据自己项目的需要，结合方案的优劣，选择最合适的。我遇到过一些开发人员，为了敷衍，在设计数据库表的时候，只考虑眼前的任务能不能完成，不太关注未来扩展的问题，也不考虑查询是否消耗性能.可能前期数据量不大的时候看不出影响，但是如果数据量稍微大一点就已经很明显了（比如：可以用outerjoin查询，但是一定要用subquery).我认为设计数据库是一项非常有趣且具有挑战性的工作。有时能体现出你的视野有多开阔，有时却能让你睡不着觉。总之，痛并快乐着。
