如何在SQL中表示数据树? [英] How to represent a data tree in SQL?

问题描述

我正在写一个由Tree和TreeNode组合而成的数据树结构.树将包含根和数据上的顶级操作. 我正在使用UI库以Windows窗体显示树，可以在其中将树绑定到TreeView.

I'm writing a data tree structure that is combined from a Tree and a TreeNode. Tree will contain the root and the top level actions on the data. I'm using a UI library to present the tree in a windows form where I can bind the tree to the TreeView.

我将需要将此树和节点保存在数据库中. 保存树并获得以下功能的最佳方法是什么?

I will need to save this tree and nodes in the DB. What will be the best way to save the tree and to get the following features:

1. 直观的实现.
2. 轻松绑定.从树上轻松移动到数据库结构，然后再返回(如果有的话)

我有两个想法.第一种是将数据序列化成表格中的一个衬板. 第二种是保存在表中，但是当移到数据实体时，我将在已更改的节点上松开表上的行状态.

I had 2 ideas. The first is to serialize the data into a one liner in a table. The second is to save in tables but then, when moving to data entities I will loose the row states on the table on changed nodes.

有什么想法吗?

推荐答案

我已将此幻灯片共享标记为SQL-Antipatterns，其中讨论了几种替代方法:

I've bookmarked this slidshare about SQL-Antipatterns, which discusses several alternatives: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back?src=embed

从那里开始的建议是使用封闭表(在幻灯片中对此进行了说明).

The recommendation from there is to use a Closure Table (it's explained in the slides).

以下是摘要(幻灯片77):

Here is the summary (slide 77):

                  | Query Child | Query Subtree | Modify Tree | Ref. Integrity
Adjacency List    |    Easy     |     Hard      |    Easy     |      Yes
Path Enumeration  |    Easy     |     Easy      |    Hard     |      No
Nested Sets       |    Hard     |     Easy      |    Hard     |      No
Closure Table     |    Easy     |     Easy      |    Easy     |      Yes

这篇关于如何在SQL中表示数据树?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！