在关系数据库中建模层次结构/目录
我想在 mysql 表中对层次结构/目录进行建模，如下所示。您可以在下面看到我正在考虑的表格架构。 然而，我所说的目录将由 100.000 个元素组成，深度约为 5-10 个级别。此外，我们将有一个标签池，目录的每个元素都可以链接到一个或多个标签。所以我想知道是否有更好的方法。我读到有些人决定设计不规范的表来影响高性能，我也在评估这个案例。
ps：有些人使用多路树在编程语言级别对此进行建模，因此如何在数据库中结束的问题仍然存在。
hierarchy:
A
| -> 1
|->1
|->2
| -> 2
| -> 3
B
| -> 1
| -> 2
table:
___________________________
| id |element | father |
|---------------------------|
| 000 | A | null |
| 001 | 1 | 000 |
| 002 | 1 | 001 |
| 003 | 2 | 001 |
| 004 | 2 | 000 |
| 005 | 3 | 000 |
| 006 | B | null |
| 001 | 1 | 006 |
| 002 | 2 | 006 |
-----------------------------
I would like to model a hierarchy/directory like what you see below, in a mysql table. You can see below the table schema I was thinking.
However, the directory Im talking about would be comprised of 100.000 elements and the depth would be ~5-10 levels. Moreover, we will have a tags pool and each element of the directory may be linked to one or more tags. So I was wondering if there is better approach. I was reading that some people decide to design tables that are not canonical for the shake of high performance and I am evaluating this case too.
ps: some people use Multi-way Trees to model this in the programming language level so the question how this ends up in the database remains.
hierarchy:
A
| -> 1
|->1
|->2
| -> 2
| -> 3
B
| -> 1
| -> 2
table:
___________________________
| id |element | father |
|---------------------------|
| 000 | A | null |
| 001 | 1 | 000 |
| 002 | 1 | 001 |
| 003 | 2 | 001 |
| 004 | 2 | 000 |
| 005 | 3 | 000 |
| 006 | B | null |
| 001 | 1 | 006 |
| 002 | 2 | 006 |
-----------------------------
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
一个非常快的层次树是一个嵌套集或 Celko 树，它有点像二叉树，或者当你有 MySQL 存储引擎时的哈夫曼树。缺点是删除和插入成本高昂。其他 RDBMS 也支持递归查询。一般来说，我没有看到很多嵌套集。创建和维护似乎也很复杂。当嵌套集合太复杂并且RDBMS不支持递归查询时，还有物化路径。
A very fast hierachical tree is a nested set or a Celko-tree, it's a bit like a binary tree, or a huffman tree when you have a MySQL storage engine. Disadvantage is expensive deletion and insertion. Other RDBMS supports also recursive queries. In general I didn't see many nested sets. It seems to be complicated too create and maintain. When the nested set is too complicated and the RDBMS doesn't support recursive queries there is also materialized path.