在关系数据库中存储树结构的已知方法有哪些? [英] What are the known ways to store a tree structure in a relational DB?
问题描述
有给你的父母一个FK"方法，即每条记录都指向它的父记录.
这是一个难以阅读的动作，但很容易维护.
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.
使用父 ID 的朴素方法:
优点:
易于实施
Easy to implement
很容易将一个大的子树移动到另一个父级
Easy to move a big subtree to another parent
插入物便宜
可在 SQL 中直接访问的所需字段
Needed Fields directly accessible in SQL
缺点:
检索整棵树是递归的，因此成本很高
Retrieving a whole tree is recursive and therefore expensive
查找所有父对象也很昂贵(SQL 不知道递归......)
Finding all parents is expensive too ( SQL doesn't know recursions... )
修改的预序树遍历(保存起点和终点):
优点:
检索一整棵树既简单又便宜
Retrieving a whole tree is easy and cheap
找到所有的父母很便宜
可在 SQL 中直接访问的所需字段
Needed Fields directly accessible in SQL
奖励:您也在其父节点中保存了子节点的顺序
Bonus: you're saving the order of childnodes within its parentnode too
缺点:
- 插入/更新可能非常昂贵，因为您可能需要更新很多节点
在每个节点中保存路径:
优点:
找到所有的父母很便宜
Finding all parents is cheap
检索一整棵树的成本很低
Retrieving a whole tree is cheap
插入很便宜
缺点:
移动一整棵树的成本很高
Moving a whole tree is expensive
根据您保存路径的方式，您将无法直接在 SQL 中使用它，因此您始终需要获取 &解析它，如果你想改变它.
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.
收尾表
优点:
易于实施
Easy to implement
找到所有的父母很便宜
插入很便宜
检索整棵树很便宜
缺点:
需要一个额外的表
Needs an additional table
与其他方法相比占用大量空间
Takes up a lot of space compared to other approaches
移动子树的开销很大
我更喜欢最后两个中的一个，具体取决于数据更改的频率.
I'd prefer one of the last two, depending on how often the data changes.
另见:http://media.pragprog.com/titles/bksqla/树木.pdf
这篇关于在关系数据库中存储树结构的已知方法有哪些?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
