树形结构 数据库表设计 单纯的树(递归关系数据)

2023-12-08 06:58

本文主要是介绍树形结构 数据库表设计 单纯的树(递归关系数据),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

转载:http://www.cnblogs.com/kissdodog/p/3297894.html

相信有过开发经验的朋友都曾碰到过这样一个需求。假设你正在为一个新闻网站开发一个评论功能,读者可以评论原文甚至相互回复。

  这个需求并不简单,相互回复会导致无限多的分支,无限多的祖先-后代关系。这是一种典型的递归关系数据。

  对于这个问题,以下给出几个解决方案,各位客观可斟酌后选择。

一、邻接表:依赖父节点

  邻接表的方案如下(仅仅说明问题):

复制代码

  CREATE TABLE Comments(CommentId  int  PK,ParentId   int,    --记录父节点ArticleId  int,CommentBody nvarchar(500),FOREIGN KEY (ParentId)  REFERENCES Comments(CommentId)   --自连接,主键外键都在自己表内FOREIGN KEY (ArticleId) REFERENCES Articles(ArticleId))

复制代码

  由于偷懒,所以采用了书本中的图了,Bugs就是Articles:

  

  这种设计方式就叫做邻接表。这可能是存储分层结构数据中最普通的方案了。

  下面给出一些数据来显示一下评论表中的分层结构数据。示例表:

  

  图片说明存储结构:

  

  邻接表的优缺分析

  对于以上邻接表,很多程序员已经将其当成默认的解决方案了,但即便是这样,但它在从前还是有存在的问题的。

  分析1:查询一个节点的所有后代(求子树)怎么查呢?

  我们先看看以前查询两层的数据的SQL语句:

  SELECT c1.*,c2.*FROM Comments c1 LEFT OUTER JOIN Comments2 c2ON c2.ParentId = c1.CommentId

  显然,每需要查多一层,就需要联结多一次表。SQL查询的联结次数是有限的,因此不能无限深的获取所有的后代。而且,这种这样联结,执行Count()这样的聚合函数也相当困难。

  说了是以前了,现在什么时代了,在SQLServer 2005之后,一个公用表表达式就搞定了,顺带解决的还有聚合函数的问题(聚合函数如Count()也能够简单实用),例如查询评论4的所有子节点:

复制代码

WITH COMMENT_CTE(CommentId,ParentId,CommentBody,tLevel)
AS
(--基本语句SELECT CommentId,ParentId,CommentBody,0 AS tLevel FROM CommentWHERE ParentId = 4UNION ALL  --递归语句SELECT c.CommentId,c.ParentId,c.CommentBody,ce.tLevel + 1 FROM Comment AS c INNER JOIN COMMENT_CTE AS ce    --递归查询ON c.ParentId = ce.CommentId
)
SELECT * FROM COMMENT_CTE

复制代码

  显示结果如下:

  

  那么查询祖先节点树又如何查呢?例如查节点6的所有祖先节点:

复制代码

WITH COMMENT_CTE(CommentId,ParentId,CommentBody,tLevel)
AS
(--基本语句SELECT CommentId,ParentId,CommentBody,0 AS tLevel FROM CommentWHERE CommentId = 6UNION ALLSELECT c.CommentId,c.ParentId,c.CommentBody,ce.tLevel - 1  FROM Comment AS c INNER JOIN COMMENT_CTE AS ce  --递归查询ON ce.ParentId = c.CommentIdwhere ce.CommentId <> ce.ParentId
)
SELECT * FROM COMMENT_CTE ORDER BY CommentId ASC

复制代码

  结果如下:

  

  再者,由于公用表表达式能够控制递归的深度,因此,你可以简单获得任意层级的子树。

  OPTION(MAXRECURSION 2)

  看来哥是为邻接表平反来的。

   分析2:当然,邻接表也有其优点的,例如要添加一条记录是非常方便的。

  INSERT INTO Comment(ArticleId,ParentId)...    --仅仅需要提供父节点Id就能够添加了。

   分析3:修改一个节点位置或一个子树的位置也是很简单.

UPDATE Comment SET ParentId = 10 WHERE CommentId = 6  --仅仅修改一个节点的ParentId,其后面的子代节点自动合理。

  分析4:删除子树

  想象一下,如果你删除了一个中间节点,那么该节点的子节点怎么办(它们的父节点是谁),因此如果你要删除一个中间节点,那么不得不查找到所有的后代,先将其删除,然后才能删除该中间节点。

  当然这也能通过一个ON DELETE CASCADE级联删除的外键约束来自动完成这个过程。

   分析5:删除中间节点,并提升子节点

  面对提升子节点,我们要先修改该中间节点的直接子节点的ParentId,然后才能删除该节点:

  SELECT ParentId FROM Comments WHERE CommentId = 6;    --搜索要删除节点的父节点,假设返回4UPDATE Comments SET ParentId = 4 WHERE ParentId = 6;  --修改该中间节点的子节点的ParentId为要删除中间节点的ParentIdDELETE FROM Comments WHERE CommentId = 6;          --终于可以删除该中间节点了

  由上面的分析可以看到,邻接表基本上已经是很强大的了。

二、路径枚举

  路径枚举的设计是指通过将所有祖先的信息联合成一个字符串,并保存为每个节点的一个属性。

  路径枚举是一个由连续的直接层级关系组成的完整路径。如"/home/account/login",其中home是account的直接父亲,这也就意味着home是login的祖先。

  还是有刚才新闻评论的例子,我们用路径枚举的方式来代替邻接表的设计:

复制代码

  CREATE TABLE Comments(CommentId  int  PK,Path      varchar(100),    --仅仅改变了该字段和删除了外键ArticleId  int,CommentBody nvarchar(500),FOREIGN KEY (ArticleId) REFERENCES Articles(ArticleId))

复制代码

   简略说明问题的数据表如下:

  CommentId  Path    CommentBody

  1       1/        这个Bug的成因是什么

  2       1/2/     我觉得是一个空指针

  3       1/2/3     不是,我查过了

  4       1/4/     我们需要查无效的输入

  5       1/4/5/    是的,那是个问题

  6       1/4/6/    好,查一下吧。

  7       1/4/6/7/   解决了

  路径枚举的优点:

  对于以上表,假设我们需要查询某个节点的全部祖先,SQL语句可以这样写(假设查询7的所有祖先节点):

SELECT * FROM Comment AS c
WHERE '1/4/6/7/' LIKE c.path + '%'

  结果如下:

  

  假设我们要查询某个节点的全部后代,假设为4的后代:

SELECT * FROM Comment AS c
WHERE c.Path LIKE '1/4/%'

  结果如下:

  

  一旦我们可以很简单地获取一个子树或者从子孙节点到祖先节点的路径,就可以很简单地实现更多查询,比如计算一个字数所有节点的数量(COUNT聚合函数)

  

   插入一个节点也可以像和使用邻接表一样地简单。可以插入一个叶子节点而不用修改任何其他的行。你所需要做的只是复制一份要插入节点的逻辑上的父亲节点路径,并将这个新节点的Id追加到路径末尾就可以了。如果这个Id是插入时由数据库生成的,你可能需要先插入这条记录,然后获取这条记录的Id,并更新它的路径。

  路径枚举的缺点:

  1、数据库不能确保路径的格式总是正确或者路径中的节点确实存在(中间节点被删除的情况,没外键约束)。

  2、要依赖高级程序来维护路径中的字符串,并且验证字符串的正确性的开销很大。

  3、VARCHAR的长度很难确定。无论VARCHAR的长度设为多大,都存在不能够无限扩展的情况。

  路径枚举的设计方式能够很方便地根据节点的层级排序,因为路径中分隔两边的节点间的距离永远是1,因此通过比较字符串长度就能知道层级的深浅。

三、嵌套集

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

  还是以上面的新闻-评论作为例子,对于嵌套集的方式表可以设计为:

复制代码

  CREATE TABLE Comments(CommentId  int  PK,nsleft    int,  --之前的一个父节点nsright   int,  --变成了两个ArticleId  int,CommentBody nvarchar(500),FOREIGN KEY (ArticleId) REFERENCES Articles(ArticleId))

复制代码

  nsleft值的确定:nsleft的数值小于该节点所有后代的Id。

  nsright值的确定:nsright的值大于该节点所有后代的Id。

  当然,以上两个数字和CommentId的值并没有任何关联,确定值的方式是对树进行一次深度优先遍历,在逐层入神的过程中依次递增地分配nsleft的值,并在返回时依次递增地分配nsright的值。

  采用书中的图来说明一下情况:

  

  一旦你为每个节点分配了这些数字,就可以使用它们来找到给定节点的祖先和后代。

  嵌套集的优点:

  我觉得是唯一的优点了,查询祖先树和子树方便。

  例如,通过搜索那些节点的ConmentId在评论4的nsleft与nsright之间就可以获得其及其所有后代:

  SELECT c2.* FROM Comments AS c1JOIN Comments AS c2  ON cs.neleft BETWEEN c1.nsleft AND c1.nsrightWHERE c1.CommentId = 1;

  结果如下:

  

  通过搜索评论6的Id在哪些节点的nsleft和nsright范围之间,就可以获取评论6及其所有祖先:

  SELECT c2.* FROM Comment AS c1JOIN Comment AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsrightWHERE c1.CommentId = 6;

  

  这种嵌套集的设计还有一个优点,就是当你想要删除一个非叶子节点时,它的后代会自动地代替被删除的节点,称为其直接祖先节点的直接后代。

  嵌套集设计并不必须保存分层关系。因此当删除一个节点造成数值不连续时,并不会对树的结构产生任何影响。

  嵌套集缺点:

  1、查询直接父亲。

  在嵌套集的设计中,这个需求的实现的思路是,给定节点c1的直接父亲是这个节点的一个祖先,且这两个节点之间不应该有任何其他的节点,因此,你可以用一个递归的外联结来查询一个节点,它就是c1的祖先,也同时是另一个节点Y的后代,随后我们使y=x就查询,直到查询返回空,即不存在这样的节点,此时y便是c1的直接父亲节点。

  比如,要找到评论6的直接父节点:老实说,SQL语句又长又臭,行肯定是行,但我真的写不动了。

  2、对树进行操作,比如插入和移动节点。

  当插入一个节点时,你需要重新计算新插入节点的相邻兄弟节点、祖先节点和它祖先节点的兄弟,来确保它们的左右值都比这个新节点的左值大。同时,如果这个新节点是一个非叶子节点,你还要检查它的子孙节点。

  够了,够了。就凭查直接父节点都困难,这个东西就很冷门了。我确定我不会使用这种设计了。

四、闭包表

  闭包表是解决分层存储一个简单而又优雅的解决方案,它记录了表中所有的节点关系,并不仅仅是直接的父子关系。
  在闭包表的设计中,额外创建了一张TreePaths的表(空间换取时间),它包含两列,每一列都是一个指向Comments中的CommentId的外键。

CREATE TABLE Comments(CommentId int PK,ArticleId int,CommentBody int,FOREIGN KEY(ArticleId) REFERENCES Articles(Id)
)

  父子关系表:

复制代码

CREATE TABLE TreePaths(ancestor    int,descendant int,PRIMARY KEY(ancestor,descendant),    --复合主键FOREIGN KEY (ancestor) REFERENCES Comments(CommentId),FOREIGN KEY (descendant) REFERENCES Comments(CommentId)
)

复制代码

  在这种设计中,Comments表将不再存储树结构,而是将书中的祖先-后代关系存储为TreePaths的一行,即使这两个节点之间不是直接的父子关系;同时还增加一行指向节点自己,理解不了?就是TreePaths表存储了所有祖先-后代的关系的记录。如下图:

  

  Comment表:

  

  TreePaths表:

  

  优点:

  1、查询所有后代节点(查子树):

SELECT c.* FROM Comment AS cINNER JOIN TreePaths t on c.CommentId = t.descendantWHERE t.ancestor = 4

  结果如下:

  

  2、查询评论6的所有祖先(查祖先树):

SELECT c.* FROM Comment AS cINNER JOIN TreePaths t on c.CommentId = t.ancestorWHERE t.descendant = 6

  显示结果如下:

  

   3、插入新节点:

  要插入一个新的叶子节点,应首先插入一条自己到自己的关系,然后搜索TreePaths表中后代是评论5的节点,增加该节点与要插入的新节点的"祖先-后代"关系。

  比如下面为插入评论5的一个子节点的TreePaths表语句:

INSERT INTO TreePaths(ancestor,descendant)SELECT t.ancestor,8FROM TreePaths AS tWHERE t.descendant = 5UNION ALLSELECT 8,8

  执行以后:

  

  至于Comment表那就简单得不说了。

  4、删除叶子节点:

  比如删除叶子节点7,应删除所有TreePaths表中后代为7的行:

  DELETE FROM TreePaths WHERE descendant = 7

  5、删除子树:

  要删除一颗完整的子树,比如评论4和它的所有后代,可删除所有在TreePaths表中的后代为4的行,以及那些以评论4的后代为后代的行:

  DELETE FROM TreePathsWHERE descendant IN(SELECT descendant FROM TreePaths WHERE ancestor = 4)

  另外,移动节点,先断开与原祖先的关系,然后与新节点建立关系的SQL语句都不难写。

  另外,闭包表还可以优化,如增加一个path_length字段,自我引用为0,直接子节点为1,再一下层为2,一次类推,查询直接自子节点就变得很简单。

总结

  其实,在以往的工作中,曾见过不同类型的设计,邻接表,路径枚举,邻接表路径枚举一起来的都见过。

  每种设计都各有优劣,如果选择设计依赖于应用程序中哪种操作最需要性能上的优化。 

  下面给出一个表格,来展示各种设计的难易程度:

设计表数量查询子查询树插入删除引用完整性
邻接表1简单简单简单简单
枚举路径1简单简单简单简单
嵌套集1困难简单困难困难
闭包表2简单简单简单简单

  1、邻接表是最方便的设计,并且很多软件开发者都了解它。并且在递归查询的帮助下,使得邻接表的查询更加高效。

  2、枚举路径能够很直观地展示出祖先到后代之间的路径,但由于不能确保引用完整性,使得这个设计比较脆弱。枚举路径也使得数据的存储变得冗余。

  3、嵌套集是一个聪明的解决方案,但不能确保引用完整性,并且只能使用于查询性能要求较高,而其他要求一般的场合使用它。

  4、闭包表是最通用的设计,并且最灵活,易扩展,并且一个节点能属于多棵树,能减少冗余的计算时间。但它要求一张额外的表来存储关系,是一个空间换取时间的方案。

 

这篇关于树形结构 数据库表设计 单纯的树(递归关系数据)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/468945

相关文章

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明(认真看哦) AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证,则会调用 #attemptAuthentica

不懂推荐算法也能设计推荐系统

本文以商业化应用推荐为例,告诉我们不懂推荐算法的产品,也能从产品侧出发, 设计出一款不错的推荐系统。 相信很多新手产品,看到算法二字,多是懵圈的。 什么排序算法、最短路径等都是相对传统的算法(注:传统是指科班出身的产品都会接触过)。但对于推荐算法,多数产品对着网上搜到的资源,都会无从下手。特别当某些推荐算法 和 “AI”扯上关系后,更是加大了理解的难度。 但,不了解推荐算法,就无法做推荐系

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

hdu1011(背包树形DP)

没有完全理解这题, m个人,攻打一个map,map的入口是1,在攻打某个结点之前要先攻打其他一个结点 dp[i][j]表示m个人攻打以第i个结点为根节点的子树得到的最优解 状态转移dp[i][ j ] = max(dp[i][j], dp[i][k]+dp[t][j-k]),其中t是i结点的子节点 代码如下: #include<iostream>#include<algorithm

usaco 1.3 Mixing Milk (结构体排序 qsort) and hdu 2020(sort)

到了这题学会了结构体排序 于是回去修改了 1.2 milking cows 的算法~ 结构体排序核心: 1.结构体定义 struct Milk{int price;int milks;}milk[5000]; 2.自定义的比较函数,若返回值为正,qsort 函数判定a>b ;为负,a<b;为0,a==b; int milkcmp(const void *va,c

怎么让1台电脑共享给7人同时流畅设计

在当今的创意设计与数字内容生产领域,图形工作站以其强大的计算能力、专业的图形处理能力和稳定的系统性能,成为了众多设计师、动画师、视频编辑师等创意工作者的必备工具。 设计团队面临资源有限,比如只有一台高性能电脑时,如何高效地让七人同时流畅地进行设计工作,便成为了一个亟待解决的问题。 一、硬件升级与配置 1.高性能处理器(CPU):选择多核、高线程的处理器,例如Intel的至强系列或AMD的Ry

自定义类型:结构体(续)

目录 一. 结构体的内存对齐 1.1 为什么存在内存对齐? 1.2 修改默认对齐数 二. 结构体传参 三. 结构体实现位段 一. 结构体的内存对齐 在前面的文章里我们已经讲过一部分的内存对齐的知识,并举出了两个例子,我们再举出两个例子继续说明: struct S3{double a;int b;char c;};int mian(){printf("%zd\n",s

基于51单片机的自动转向修复系统的设计与实现

文章目录 前言资料获取设计介绍功能介绍设计清单具体实现截图参考文献设计获取 前言 💗博主介绍:✌全网粉丝10W+,CSDN特邀作者、博客专家、CSDN新星计划导师,一名热衷于单片机技术探索与分享的博主、专注于 精通51/STM32/MSP430/AVR等单片机设计 主要对象是咱们电子相关专业的大学生,希望您们都共创辉煌!✌💗 👇🏻 精彩专栏 推荐订阅👇🏻 单片机

深入理解数据库的 4NF:多值依赖与消除数据异常

在数据库设计中, "范式" 是一个常常被提到的重要概念。许多初学者在学习数据库设计时,经常听到第一范式(1NF)、第二范式(2NF)、第三范式(3NF)以及 BCNF(Boyce-Codd范式)。这些范式都旨在通过消除数据冗余和异常来优化数据库结构。然而,当我们谈到 4NF(第四范式)时,事情变得更加复杂。本文将带你深入了解 多值依赖 和 4NF,帮助你在数据库设计中消除更高级别的异常。 什么是

SprinBoot+Vue网络商城海鲜市场的设计与实现

目录 1 项目介绍2 项目截图3 核心代码3.1 Controller3.2 Service3.3 Dao3.4 application.yml3.5 SpringbootApplication3.5 Vue 4 数据库表设计5 文档参考6 计算机毕设选题推荐7 源码获取 1 项目介绍 博主个人介绍:CSDN认证博客专家,CSDN平台Java领域优质创作者,全网30w+