在关系数据库中存储树结构的已知方法有哪些?

有给你的父母一个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

相关文章