在关系数据库中存储树结构的已知方法是什么？ [英] What are the known ways to store a tree structure in a relational DB?
问题描述
将FK放入您的父母方法，即每个记录指向它的父母。
哪一个读取操作很困难，但很容易维护。
然后有一个目录结构密钥方法：
0001.0000.0000.0000主分支1
0001.0001.0000.0000主分支1个孩子b $ b etc
哪个超级容易阅读，但很难维护。
其他的方法和它们的缺点/优点是什么？
一如既往：没有最好的解决方案。每个解决方案使不同的东西更容易或更难。对您而言，正确的解决方案取决于您将采取哪些措施。
具有父代码的朴素方法：
优点：
-
易于实施
-
容易将一个大子树移动到另一个父母
-
插入是便宜的
-
需要的字段可以直接在SQL中访问
缺点：
-
检索整个树是递归的，因此是昂贵的
-
查找所有父母也很贵（SQL doesn不知道递归...）
修改的预订树遍历（保存开始和结束-point）：
优点：
-
检索整棵树很简单便宜
-
查找所有家长都很便宜
-
需要可直接在SQL中访问的字段
-
奖励：您保存子目录的顺序在其父节点中也是
缺点：
- 插入/更新可能非常昂贵，因为您可能需要更新很多节点。
在每个节点中保存路径
优点：
-
查找所有的父母都很便宜
-
检索整棵树很便宜
- p>插入便宜
缺点：
-
移动整棵树很贵
-
根据您保存路径的方式，您将无法正常工作直接在SQL中，所以你总是需要获取&解析它，如果你想改变它。
我最喜欢最后两个之一，取决于
另请参见： http://media.pragprog.com/titles/bksqla/trees.pdf
There is the "put a FK to your parent" method, i.e. each records points to it's parent.
Which is a hard for read actions but very easy to maintain.
And then there is a "directory structure key" method:
0001.0000.0000.0000 main branch 1
0001.0001.0000.0000 child of main branch one
etc
Which is super easy to read, but hard to maintain.
What are the other ways and their cons/pros?
As always: there is no best solution. Each solution makes different things easier or harder. The right solution for you depends on which operation you will do most.
Naive Approach with parent-id:
Pros:
easy to implement
easy to move a big subtree to another parent
insert is cheap
Needed Fields directly accessible in SQL
Cons:
retrieving a whole tree is recursive and therefore expensive
finding all parents is expensive too ( SQL doesn't know recursions... )
Modified Preorder Tree Traversal ( saving a start- & end-point) :
Pros:
Retrieving a whole tree is easy and cheap
Finding all parents is cheap
Needed Fields directly accessible in SQL
Bonus: you're saving the order of childnodes within its parentnode too
Cons:
- Inserting / Updating can be very expensive, as you'll maybe have to update a lot of nodes
Saving a path in each Node:
Pros:
Finding all parents is cheap
Retrieving a whole tree is cheap
Inserting is cheap
Cons:
Moving a whole tree is expensive
Depending on the way you save the path, you won't be able to work with it directly in SQL, so you'll always need to fetch & parse it, if you want to change it.
I'd prefer one of the last two, depending on how often the data changes.
See also: http://media.pragprog.com/titles/bksqla/trees.pdf
这篇关于在关系数据库中存储树结构的已知方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
