精华内容
下载资源
问答
  • 树形结构 数据库表设计

    千次阅读 2016-09-12 10:40:48
    相信有过开发经验的朋友都曾碰到过这样一个需求。假设你正在为一个新闻网站开发一个评论功能,读者可以评论原文甚至相互回复。 ...这是一种典型的递归关系数据。... 邻接的方案如下(仅仅说明问题):  CREAT

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

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

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

    一、邻接表:依赖父节点

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

    复制代码
      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 c2
      ON 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 Comment
        WHERE ParentId = 4
        UNION 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 Comment
        WHERE CommentId = 6
        UNION ALL
        SELECT c.CommentId,c.ParentId,c.CommentBody,ce.tLevel - 1  FROM Comment AS c 
        INNER JOIN COMMENT_CTE AS ce  --递归查询
        ON ce.ParentId = c.CommentId
        where 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;    --搜索要删除节点的父节点,假设返回4
      UPDATE Comments SET ParentId = 4 WHERE ParentId = 6;  --修改该中间节点的子节点的ParentId为要删除中间节点的ParentId
      DELETE 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 c1
       JOIN Comments AS c2  ON cs.neleft BETWEEN c1.nsleft AND c1.nsright
      WHERE c1.CommentId = 1;

      结果如下:

      

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

      SELECT c2.* FROM Comment AS c1
      JOIN Comment AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright
      WHERE 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 c
        INNER JOIN TreePaths t on c.CommentId = t.descendant
        WHERE t.ancestor = 4

      结果如下:

      

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

    SELECT c.* FROM Comment AS c
        INNER JOIN TreePaths t on c.CommentId = t.ancestor
        WHERE t.descendant = 6

      显示结果如下:

      

       3、插入新节点:

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

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

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

      执行以后:

      

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

      4、删除叶子节点:

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

      DELETE FROM TreePaths WHERE descendant = 7

      5、删除子树:

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

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

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

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

    总结

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

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

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

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

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

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

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

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

            关注微信公众号每天学习一点程序员的职业经



    展开全文
  • 数据库表设计

    千次阅读 2018-09-28 15:43:11
    数据库表设计 2016年11月11日 22:00:25 jasonhui512 阅读数:4611 标签: 数据库 设计 参考: http://blog.csdn.net/famousdt/article/details/6921622 http://blog.csdn.net/truong/article/details/29825913 ...

    数据库表设计

    2016年11月11日 22:00:25 jasonhui512 阅读数:4611 标签: 数据库 设计

    参考:

    http://blog.csdn.net/famousdt/article/details/6921622

    http://blog.csdn.net/truong/article/details/29825913

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

    http://www.csdn.net/article/2012-04-11/2804419

    范式:英文名称是 Normal Form,它是英国人 E.F.Codd(关系数据库的老祖宗)在上个世纪70年代提出关系数据库模型后总结出来的,范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的规则和指导方法。目前有迹可寻的共有8种范式,依次是:1NF,2NF,3NF,BCNF,4NF,5NF,DKNF,6NF。通常所用到的只是前三个范式,即:第一范式(1NF),第二范式(2NF),第三范式(3NF)。下面就简单介绍下这三个范式。 
    ◆ 第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列。 
    考虑这样一个表:【联系人】(姓名,性别,电话) 
    如果在实际场景中,一个联系人有家庭电话和公司电话,那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列(电话)拆分,即:【联系人】(姓名,性别,家庭电话,公司电话)。1NF 很好辨别,但是 2NF 和 3NF 就容易搞混淆。 
    ◆ 第二范式(2NF):首先是 1NF,另外包含两部分内容,一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。 
    考虑一个订单明细表:【OrderDetail】(OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)。 
    因为我们知道在一个订单中可以订购多种产品,所以单单一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)。显而易见 Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID),而 UnitPrice,ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。 
    可以把【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和【Product】(ProductID,UnitPrice,ProductName)来消除原订单表中UnitPrice,ProductName多次重复的情况。 
    ◆ 第三范式(3NF):首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。 
    考虑一个订单表【Order】(OrderID,OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity)主键是(OrderID)。 
    其中 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键(OrderID),所以符合 2NF。不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF。 
    通过拆分【Order】为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF。 
    第二范式(2NF)和第三范式(3NF)的概念很容易混淆,区分它们的关键点在于,2NF:非主键列是否完全依赖于主键,还是依赖于主键的一部分;3NF:非主键列是直接依赖于主键,还是直接依赖于非主键列。

     

    3、不同类型的数据应该分开管理,例如,财务数据库,业务数据库等。
    4、由于存储过程在不同的数据库中,支持方式不一样,因此不建议过多使用和使用复杂的存储过程。为数据库服务器降低压力,不要让数据库处理过多的业务逻辑,将业务逻辑处理放到应用程序中。

     

    2、  数据不要物理删除,应该加一个标志位,以防用户后悔时,能够恢复。还有就是比如人员登记加入某系统,采用登记卡的方式,一段时间后该人员退出了该系统,那么此时我们将该人员对应的记录标记为不可用,那么这个人就从系统中移除了,一段时间后这个人又回来了,那么我们直接修改标记字段为可用,这个人就从新加入系统,避免了其它信息的录入。

    6、  增加备注字段,虽然我们考虑了很多用户需要输入信息的需求,但是无论何时我们都不可能考虑全,因此可以定义一个备注字段,允许用户将其它的信息填写在这里。无论表设计的再神奇,那么还是加一个备注字段。

     (1) 在数据库物理设计时,降低范式,增加冗余, 少用触发器, 多用存储过程。
       (2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条),复杂计算要先在数据库外面,以

    文件系统方式用C++语言计算处理完成之后,最后才入库追加到表中去。这是电信计费系统设计的经验。
       (3) 发现某个表的记录太多,例如超过一千万条,则要对该表进行水平分割。水平分割的做法是,

    以该表主键PK的某个值为界线,将该表的记录水平分割为两个表。若发现某个表的字段太多,例如超过

    八十个,则垂直分割该表,将原来的一个表分解为两个表。

     

     

     

    逻辑数据库设计 - 单纯的树(递归关系数据)

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

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

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

    一、邻接表:依赖父节点

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

    复制代码

      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 c2
      ON 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 Comment
        WHERE ParentId = 4
        UNION 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 Comment
        WHERE CommentId = 6
        UNION ALL
        SELECT c.CommentId,c.ParentId,c.CommentBody,ce.tLevel - 1  FROM Comment AS c 
        INNER JOIN COMMENT_CTE AS ce  --递归查询
        ON ce.ParentId = c.CommentId
        where 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;    --搜索要删除节点的父节点,假设返回4
      UPDATE Comments SET ParentId = 4 WHERE ParentId = 6;  --修改该中间节点的子节点的ParentId为要删除中间节点的ParentId
      DELETE 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 c1
       JOIN Comments AS c2  ON cs.neleft BETWEEN c1.nsleft AND c1.nsright
      WHERE c1.CommentId = 1;

      结果如下:

      

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

      SELECT c2.* FROM Comment AS c1
      JOIN Comment AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright
      WHERE 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 c
        INNER JOIN TreePaths t on c.CommentId = t.descendant
        WHERE t.ancestor = 4

      结果如下:

      

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

    SELECT c.* FROM Comment AS c
        INNER JOIN TreePaths t on c.CommentId = t.ancestor
        WHERE t.descendant = 6

      显示结果如下:

      

       3、插入新节点:

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

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

    复制代码

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

    复制代码

      执行以后:

      

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

      4、删除叶子节点:

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

      DELETE FROM TreePaths WHERE descendant = 7

      5、删除子树:

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

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

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

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

    总结

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

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

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

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

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

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

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

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

    展开全文
  • (3):购物车,跟踪用户的购物情况 (4):结算中心:处理用户帐单,购物处理 (5):反馈互动区,可以留言 (6):会员信息模块,可以注册 数据需求分析 数据库表设计定为8个表实现: ccdot_user{用户信息表}: ----

    (1):产品展示,按照分类展示全部产品,和对应的相关信息。

    (2):增加产品的展示相关度,诸如最新产品报道,网站的新闻,促销信息。

    (3):购物车,跟踪用户的购物情况

    (4):结算中心:处理用户帐单,购物处理

    (5):反馈互动区,可以留言

    (6):会员信息模块,可以注册

    数据需求分析

    数据库表设计定为8个表实现:

    ccdot_user{用户信息表}:

    ------szd_userid[PK]

    ------szd_username[用户ID]

    ------szd_password[用户密码]

    ------szd_name[用户真是信息]

    ------szd_question[丢失密码问题]

    ------szd_answer[用户回答答案,用于找密码]

    ------szd_sex[性别]

    ------szd_phone[电话]

    ------szd_email[电子邮件]

    ------szd_address[地址]

    ------szd_post[邮编]

    ------szd_province[省份]

    ------szd_city[城市]

    ------szd_mark[标记]

    ccdot_aclass{产品大类}

    ------szd_aclassid[PK]

    ------szd_aclassname[名称]

    ccdot_bclass{产品小类}

    ------szd_bclassid[pk]

    ------szd_aclassid[关联大类szd_aclassid]

    ------szd_bclassname[名称]

    ccdot_poduct{产品信息}

    ------szd_productid[pk]

    ------szd_bclassid[关联小类的szd_bclassid]

    ------szd_pname[名称]

    ------szd_pdetail[产品信息]

    ------szd_pprice[一般价格]

    ------szd_pmemderprice[会员价]

    ------szd_pfewprice[差价]

    ------szd_ppicture[图片]

    ------szd_ptime[添加时间]

    ------szd_stock[产品库存]

    ------szd_phit[产品点击次数]

    ------szd_pdetail1[其他产品描述]

    ccdot_news{新闻}

    ------szd_newsid[PK]

    ------szd_title[标题]

    ------szd_content[内容]

    ------szd_time[添加时间]

    ------szd_type[类型]

    ccdot_guest{评论}

    ------szd_gid[PK]

    ------szd_gname[昵称]

    ------szd_gcontent[留言内容]

    ------szd_productid[关联产品的主键]

    ------szd_gtime[留言时间]

    ------szd_gip[留言者ip]

    ccdot_orderlist{订单列表}

    ------szd_orderlistid[PK]

    ------szd_orderid[关联详细订单的主键]

    ------szd_productid[关联产品的主键]

    ------szd_quantity[所定数目]

    ------szd_unitcost[单价]

    ------szd_productname[产品名称]

    ccdot_orders{订单信息表}

    ------szd_orderid[PK]

    ------szd_userid[关联用户主键]

    ------szd_orderdate[订单日期]

    ------szd_linkman[联系人]

    ------szd_email[联系人email]

    ------szd_phone[联系电话]

    ------szd_postalcode[送货处邮编]

    ------szd_address[送货地址]

    ------szd_result[处理结果]

    ------szd_remark[备注]

    ------szd_songhuoqixian[送货期限]

    ------szd_songhuofangshi[发货方式]

    ------szd_fukuanfangshi[付款方式]

    ------szd_error[意外说明]

    展开全文
  • 这种结构不完全是树形结构,基本上只有上下两级,一级评论,二级回复。(例如有时候我们会看到一条对评论的回复,但是下面引用的内容显示该评论已删除),这种情况不,对于一级评论,删除的时候,下面的回复是级联...

    问题现象

    一个新闻下面可以有评论,其他人可以对评论进行回复,还可以对回复进行回复。例如
    但是对回复的回复一般无论回复多少层,都是并列显示的,只不过引用被回复的内容。

    这种结构不完全是树形结构,基本上只有上下两级,一级评论,二级回复。(例如有时候我们会看到一条对评论的回复,但是下面引用的内容显示该评论已删除),这种情况不,对于一级评论,删除的时候,下面的回复是级联删除的,但是对于二级评论,只删除当前这条。

    如果需要回复的结构真像盖楼一样,一层一层的树形结构,那就需要闭包表建立完整的祖先和子孙的关系。不过当前常见的产品并没有这么做,例如网易云音乐的回复,抖音的回复功能等。

    举个例子

    新闻标题:XXXX
        A: 这个新闻说的好。
            B: 我觉得不好。
            A: 呵呵。
                @B: 我觉得不好。
            B: 呵呵哒。
                @A: 呵呵。
            C: 那是你有问题。
                @B: 我觉得不好。
        D: 我也觉得说的好。
            E: +1
    

    回复表comments, 按时间先后顺序保存入表:

    ID NEWS_ID USER_ID COMMENT
    1 1 A 这个新闻说的好。
    2 1 B 我觉得不好。
    3 1 D 我也觉得说的好。
    4 1 A 呵呵。
    5 1 B 呵呵哒。
    6 1 C 那是你有问题。
    7 1 E +1

    回复关系闭包表relations:

    ID COMMENT_ID REPLY_COMMENT_ID ANCESTOR_COMMENT_ID NEWS_ID
    1 1 1
    2 2 1 1 1
    3 3 1
    4 4 2 1 1
    5 5 4 1 1
    6 6 2 1 1
    7 7 3 3 1

    查询过程

    1. 查找所有一级评论
    select c.id, c.user_id, c.comment from comments 
    right join relations r on c.id = r.comment_id 
    where news_id = 1 and r.ANCESTOR_COMMENT_ID is null
    
    1. 所有下级评论
    select r.ANCESTOR_COMMENT_ID, c.user_id, c.comment from comments 
    right join relations r on c.id = r.comment_id
    where r.ANCESTOR_COMMENT_ID in (...) order by create_time desc  
    
    1. 设置一级评论的下级评论
    展开全文
  • 帝国cms数据库表结构

    千次阅读 2013-01-31 13:47:08
    帝国网站管理系统V6.6版-数据字典 http://www.phome.net/doc/manual/extend/html/dbdoc/index.html 表名 解释  ...phome_ecms_infoclass_news 新闻采集规则记录  phome_ecms_infotmp_n
  • 新闻发布系统数据库设计

    千次阅读 多人点赞 2018-11-01 15:51:11
    新闻发布系统数据库设计数据库名与表名表与表表结构与创建表设计索引设计视图设计触发器 数据库名与表名 create database webnews; use webnews; 表与表 表结构与创建表 #user create table user( userID INT ...
  • 评论(评价)数据库表设计

    千次阅读 2019-04-16 15:19:48
    本文主要介绍评论功能的数据库设计。 评论功能最主要的是发表评论和回复评论(删除功能在后台)。 评论功能的拓展功能体现有以下几方面: (1)单篇文章的评论数量和信息展示; (2)从时间维度,按照时间倒叙的方式...
  • 【MySQL】新闻发布系统数据库设计

    千次阅读 多人点赞 2019-09-03 23:20:56
    新闻发布系统数据库设计 1.系统概述 本文介绍的是一个小型新闻发布系统,管理员可以通过该系统发布新闻信息,管理新闻信息。 一个典型的新闻发布系统网站至 少应该包含新闻信息管理、新闻信息显示和新闻信息查询三种...
  • 假设你正在为一个新闻网站开发一个评论功能,读者可以评论原文甚至相互回复。  这个需求并不简单,相互回复会导致无限多的分支,无限多的祖先-后代关系。这是一种典型的递归关系数据。  对于这个问题,以下给出...
  • PHPCms 数据库设计结构文档

    千次阅读 2009-02-16 21:29:00
    第一个_admin (管理员)userid 用户名idusername 用户名grade 用户级别purviewids modules 模块channelids 频道ID catids 栏目IDspecialids 专题IDdisabled 禁用(0为否,1为是) 第二个 _ads (广告...
  •  MYISAM.(很多人说她的级锁定会带来好多问题,其实只要设计好对应的以及写好对应的SQL查询就没有那么大的问题。) B. INNODB. (如果要用到事务,选择她不会错。至于多数人讲的MASTER/SLAVE结构上用...
  • 数据库设计——评论回复功能

    万次阅读 多人点赞 2017-05-03 02:43:48
    本文主要介绍评论功能的数据库设计。评论功能最主要的是发表评论和回复评论(删除功能在后台)。评论功能的拓展功能体现有以下几方面: (1)单篇文章的评论数量和信息展示; (2)从时间维度,按照时间倒
  • 数据库设计

    千次阅读 2006-03-27 18:51:00
    对于大多数稍具规模的网站来说,数据库表是运作的生命中枢。用户帐户、新闻动态、内容、统计数据都保存到关系数据库管理系统(Relational Database Management System,RDBMS)。 建立数据信息模型(Data Model,...
  • 设计数据库时应该考虑的因素

    千次阅读 2013-03-14 01:48:41
     老田:一个宗旨“尽量少的表,每个表中尽量少的列,合理的表结构”。  小天:说的好抽象,具体点,比如设计表的时候可以有什么辅助工具;什么时候用什么数据类型;谁跟谁之间有了关系,什么样的关系;还有你可以...
  • 关于论坛数据库设计

    千次阅读 2016-04-21 17:29:25
    关于论坛数据库的设计 文章分类:数据库 一个简单的论坛系统 1:包含下列信息: 2:每天论坛访问量300万...请给出数据库表结构设计,并结合范式简要说明设计思路。 一. 发帖主题和回复信息存放在一张表,并在这个表
  • 数据库角色权限设计

    千次阅读 2015-06-19 11:28:36
     在权限系统中,功能(权限)是最小的单位,比如起草新闻、编辑新闻、审核新闻、删除新闻等,而角色是一功能的集合,比如新闻编辑这个角色,他可能有起草新闻、编辑新闻等功能集合,而责任编辑他可能就有更多的...
  • mongodb数据库设计实践

    千次阅读 2015-11-15 16:23:50
    我们公司要开发一款企业博客软件,采用mongodb这种存储海量数据的数据库。...数据库设计的时候,只有三个角色,就是人,公司,新闻,因此一开始设计的时候,就只有user,com,news三个,加上日志新闻举报等附属
  • 做一个评论的功能,如何设计一个表结构呢?

    万次阅读 多人点赞 2019-01-08 16:22:38
    一问一答模式 (1)需求分析 大部分APP采用简单的评论设计即可,即是一问一答模式,...(2)数据库设计  这种场景下一般评论较少,评论不活跃,可以不区分评论和回复,统一看成评论。区别是,有些评论是直接评论...
  • 创建新闻网站数据库step1>启动Mysql数据库step2>创建新闻网站数据库step3>显示数据库列表step4>使用刚创建的数据库step5>创建栏目信息step6>创建文章信息Task2> 向文章信息添加数据...
  • Android数据库高手秘籍(四)——使用LitePal建立关联

    万次阅读 多人点赞 2014-09-25 09:00:55
    只不过表与表之间的关联关系要比对象之间的关联关系复杂一些,也更加难懂,但是作为数据库的基本功,还是应该了解清楚的,那么我们就先来学习一下数据库表关联的基础知识。 表与表之间的关联关系一共有三种类型,一...
  • 数据库系统原理设计--论坛系统

    千次阅读 2016-04-09 16:08:49
    开始使用计算机数据库来管理。现如今网络盛行,BBS 论坛已成为人们生活  中的一种信息交流渠道,它通过在计算机上运行服务软件,允许用户使用终端  程序通过电话调制解调器拨号或者 Internet 来进行连接,执行...
  • 一:一开始是打算在新闻表中用一个字段来记录这条新闻的点赞总数,后面想到假设有很多人对这条新闻进行点赞和取消点赞的操作的话,没做一个操作都要进行一次数据库的读写,会比较消耗性能,所以后面转变了一下思...
  • 一、关系型数据库设计范式 范式:Normal Format,符合某一种级别的关系模式的集合,表示一个关系内部各属性之间的联系的合理化程度 范式是离散数学里的概念 范式目标是在满足组织和存储的前提下使数据...
  • MySQL数据库面试题(2020最新版)

    万次阅读 多人点赞 2020-03-10 17:20:40
    数据库三大范式是什么mysql有关权限的都有哪几个MySQL的binlog有有几种录入格式?分别有什么区别?数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别?InnoDB引擎的4...
  • 博客、 CMS(网易新闻、 腾讯新闻) 之的系统, 核心就是文章, 一切的一切都围绕着文章进行, 所以设计一个好的文章分类和标签的数据库关系模型, 对后续编码及维护将会起到至关重要的作用。 一. ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 26,658
精华内容 10,663
关键字:

新闻类数据库表结构设计