What are 'best-practices' for saving Composite patterns in a Relational Database?
在关系数据库中保存复合模式的“最佳实践”是什么?
We have been using Modified Preorder Tree Traversal. This is very quick to build the whole tree, but very slow to insert or delete new nodes (all left and right values need to be adjusted). Also querying the children of a node is not easy and very slow.
我们一直在使用改进的前序树遍历。这对于构建整个树非常快速，但是插入或删除新节点非常缓慢(需要调整左值和右值)。同时查询节点的子节点并不容易，而且很慢。
Another thing we noticed is that you really have to make sure the tree doesn't get messy. You need transaction locks, otherwise the left and right values can get corrupt, and fixing a corrupt left right tree is not an easy job.
我们注意到的另一件事是你必须确保树不会变得凌乱。您需要事务锁，否则左和右的值会被破坏，修复损坏的左右树不是一件容易的工作。
It does work very good however, the Modified Preorder Tree Traversal, but I was wondering if there are better alternatives.
但是它确实很好用，修改过的Preorder树遍历，但是我想知道是否有更好的选择。
2 个解决方案
#1
6
While finding all descendents of a row with MPTT is fast, finding all children can be slow. However you should be able to fix that by adding a parent_id field to your table that records (yes, redundantly) the parent of the row. Then the search becomes:
虽然使用MPTT查找一行的所有后代的速度很快，但是查找所有的子元素的速度可能很慢。但是，您应该能够通过将parent_id字段添加到记录(是的，冗余)表的父字段的表中来解决这个问题。然后搜索变成了:
SELECT *
FROM tbl
WHERE parent_id = z
Yes, parent_id contains redundant information, potentially denormalizing your table -- but since any insert/update/delete already requires global changes, keeping parent_id up-to-date isn't much extra to pay. You could alternatively use a level field that records the vertical level of the row, although that is in fact more likely to change under certain types of transformations (e.g. moving a subtree to a different point in the tree).
是的，parent_id包含冗余信息，可能会对表进行非规范化——但由于任何插入/更新/删除都已经需要全局更改，所以让parent_id保持最新并不需要付出太多代价。您也可以使用一个level字段来记录行的垂直级别，尽管在某些类型的转换(例如，将一个子树移动到树中的另一个点)下，这实际上更可能发生变化。
The plain old link-to-parent representation (i.e. just having parent_id and no left_pos or right_pos), is of course faster for insert/update-heavy workloads, but the only queries it can answer efficiently are "Find the parent of X" and "Find the children of X." Most workloads involve much more reading than writing, so usually MPTT is faster overall -- but perhaps in your case you need to consider moving ("back") to link-to-parent?
简单的旧的链接到父节点的表示(即只有parent_id，没有left_pos或right_pos)对于插入/更新密集型的工作负载来说当然更快，但它能有效回答的唯一查询是“查找X的父节点”和“查找X的子节点”。大多数工作负载涉及的读操作要比写操作多得多，所以通常MPTT总体上速度更快——但在您的情况下，您可能需要考虑将(“回到”)连接到父节点?
#2
0
The best way to store hierakial data in a database I have heard is to use a string attribute where the content is the list of parents separated by, say colons.
在我听说过的数据库中存储象形数据的最好方法是使用string属性，其中的内容是用冒号分隔的父类列表。
#1
6
While finding all descendents of a row with MPTT is fast, finding all children can be slow. However you should be able to fix that by adding a parent_id field to your table that records (yes, redundantly) the parent of the row. Then the search becomes:
虽然使用MPTT查找一行的所有后代的速度很快，但是查找所有的子元素的速度可能很慢。但是，您应该能够通过将parent_id字段添加到记录(是的，冗余)表的父字段的表中来解决这个问题。然后搜索变成了:
SELECT *
FROM tbl
WHERE parent_id = z
Yes, parent_id contains redundant information, potentially denormalizing your table -- but since any insert/update/delete already requires global changes, keeping parent_id up-to-date isn't much extra to pay. You could alternatively use a level field that records the vertical level of the row, although that is in fact more likely to change under certain types of transformations (e.g. moving a subtree to a different point in the tree).
是的，parent_id包含冗余信息，可能会对表进行非规范化——但由于任何插入/更新/删除都已经需要全局更改，所以让parent_id保持最新并不需要付出太多代价。您也可以使用一个level字段来记录行的垂直级别，尽管在某些类型的转换(例如，将一个子树移动到树中的另一个点)下，这实际上更可能发生变化。
The plain old link-to-parent representation (i.e. just having parent_id and no left_pos or right_pos), is of course faster for insert/update-heavy workloads, but the only queries it can answer efficiently are "Find the parent of X" and "Find the children of X." Most workloads involve much more reading than writing, so usually MPTT is faster overall -- but perhaps in your case you need to consider moving ("back") to link-to-parent?
简单的旧的链接到父节点的表示(即只有parent_id，没有left_pos或right_pos)对于插入/更新密集型的工作负载来说当然更快，但它能有效回答的唯一查询是“查找X的父节点”和“查找X的子节点”。大多数工作负载涉及的读操作要比写操作多得多，所以通常MPTT总体上速度更快——但在您的情况下，您可能需要考虑将(“回到”)连接到父节点?
#2
0
The best way to store hierakial data in a database I have heard is to use a string attribute where the content is the list of parents separated by, say colons.
在我听说过的数据库中存储象形数据的最好方法是使用string属性，其中的内容是用冒号分隔的父类列表。