int (id) 或基于文本的层次结构？
我有一个关于关系数据库和层次结构的最佳实践的问题。我的问题是，使用基于文本或 int (id) 的层次结构是否更明智？
我有一个层次结构，但它不是基于 id 的层次结构。层次结构基于文本 例如“level1”、“level2”而不是id1、id2
我使用mysql 和solr 来管理我的数据。
目前我保留了文本层次结构，因此如果我愿意的话可以使用它。但创建一个 id 层次结构似乎更健康（我也这样做了）。 Int (id) 层次结构看起来也更快，并且不太容易出现非标准化数据（我从来不需要修剪（） ids 等）
对此的任何想法都非常感激。了解其他人认为最佳实践是什么会很有趣。
干杯
柯
I have a question on best practice for relational databases and hierarchies. My question is, is it more sane to use text or int (id) based hierarchies?
I have a hierarchy, but it is not an id based one. The hierarchy is based on text
e.g. 'level1', 'level2' as opposed to id1, id2
I use mysql and also solr for managing my data.
At the moment I have kept the text hierarchy, so could use that if I want to. But it seems healthier allround to create an id hierarchy (which I have also done). Int (id) hierarchies also seem faster and are not really prone to un-normalized data (I never have to trim() ids etc.)
Any thoughts on this are much appreciated. It would be interesting to find out what others feel is best practice.
Cheers
Ke
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果我需要获取子树，我倾向于使用两者。
行有 2 个 int 列，由 id 和 ParentId 组成。这构成了树结构。
此外，我还有一个文本级别，代表父行的索引。例如，idName="44.21.31" 的行的 id 为 31，父级 id 为 21，其祖父级的 id 为 44。这样您就可以获取子树，`where idName like "44.21.%"将获取 id 为 21 的行的每个子项、孙子项等。
但这确实破坏了正常形式，现在存在有关实体 id 的冗余信息 - 但它可能是值得的，特别是对于不支持此操作的数据库系统否则支持等级结构。
I tend to use both, if I need to fetch subtrees.
Rows have 2 int columns consisting of an id and a parentId. This makes up the tree structure.
In addition I also have a textual level, representing the indexes of the parent line. e.g. a row with idName="44.21.31" would have an id of 31, a parent id of 21 and its grand parent would have an id of 44. This way you can fetch subtrees, `where idName like "44.21.%" would fetch every child, grand child and so on of the row with id 21.
That does break normal form though, there's now redundant information about the id of an entity - but it can be worth it, especially for db systems that doesn't otherwise suport hierarcical structures.
我更喜欢使用整数 - 它们更快、更小，并且不需要像基于文本的那样进行更改，因为它们不包含语义含义。
I prefer to use ints - they are faster and smaller and do not need to be changed like a text based one might, since they contain no sematic meaning.
如果使用基于 id 的层次结构，将会有更好的性能。但您可以在数据库中使用两列，为您保留两个代码（IDCode 和 Code）。代码如下：
子代码 = IDCode + 父代码
请注意 IDCode 是唯一的。
这个解决方案不好，因为您需要一些操作来处理这项工作。
if you use a id based hierarchy, will have better performance. but you can use two columns in your db that reserve two codes for you (IDCode and Code). this code made like this :
Child Code = IDCode + Parent Code
make attention that IDCode is unique.
this solution isn't good because you need some operations to process this work.