如何将层次信息存储到数据库中？
我有以下信息，应通过使用 Web 表单上的几个相关选择字段来检索：
用户将能够添加新类别。
Food
- Fruits
- Tropical
- Pineapples
- Pineapples - Brazil
- Pineapples - Hawaii
- Coconuts
- Continental
- Orange
- Fish
....
该数据应该来自数据库。
我意识到为这里提出的每个类别创建一个表可能不是一个好的模式，所以我想问，是否有任何标准方法来处理这个问题？
我也知道这个架构示例： 管理 MySQL 中的分层数据
还有其他（也许更直观的方式） ）来存储此类信息？
I have the following information that should be retrieved by using several dependent select fields on a web form:
Users will be able to add new categories.
Food
- Fruits
- Tropical
- Pineapples
- Pineapples - Brazil
- Pineapples - Hawaii
- Coconuts
- Continental
- Orange
- Fish
....
This data should come from a database.
I realize that creating a table for each category here presented is not a good schema perhaps, so I would to ask, if is there any standard way to deal with this?
I'm also aware of this schema example:
Managing Hierarchical Data in MySQL
Is there any other (perhaps more intuitive way) to store this type of information ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您提供的链接描述了存储此类信息的两种标准方法：
The link you provided describes the two standard ways for storing this type of information:
您的问题没有提出的一个问题是所有水果是否都具有相同的属性。
如果所有水果都具有相同的属性，那么告诉您查看您提供的链接并阅读有关邻接表和嵌套集的答案是正确的。
如果新的水果可以具有新的属性，那么可以添加新水果的用户也可以添加新的属性。这很容易就会变得一团糟。如果两个用户发明了相同的属性，但给它起了不同的名称，这可能会出现问题。如果两个用户发明了不同的属性，但给它们提供了相同的名称，那就是另一个问题。
您可能会说，从概念上讲，每个用户都有自己的数据库，并且无法进行组合来自不同用户的数据的有意义的查询。问题是，数据库的使命几乎总是包括迟早将来自不同用户的所有数据汇集在一起。
这就是您面临几乎不可能的数据管理问题的地方。
One issue your question didn't raise is whether all fruits have the same attributes or not.
If all fruits have the same attributes, then the answer that tells you to look at the link you provided and read about adjacency lists and nested sets is correct.
If new fruits can have new attributes, then a user that can add a new fruit can also add a new attribute. This can turn into a mess, real easily. If two users invent the same attribute, but give it a different name, that might be a problem. If two users invent different attributes, but give them the same name, that's another problem.
You might just as well say that, conceptually, each user has their own database, and no meaningful queries can be made that combine data from different users. Problem is, the mission of the database almost always includes, sooner or later, bringing together all the data from the different users.
That's where you face a nearly impossible data management issue.
Kawu 给了你答案......递归关系（表将与其自身相关）又名猪耳关系。
您的示例显示了一个有多个孩子的父级，但您没有说明一个项目是否可以属于多个父级。橙子可以在“热带”和“柑橘”中吗？
每行都有一个
id和一个parent_id，其中parent_id指向另一行的id。以下是使用此类关系来提供无限父子关系的模式的一些示例：
Kawu gave you the answer.... a recursive relation (the table will be be related to itself) aka Pig's Ear relation.
You example shows a parent with several children, but you didn't say if an item can belong to more that one parent. Can an orange be in 'Tropical' and in 'Citrus'?
Each row has an
idand aparent_idwith theparent_idpointing to theidof another row.Here are some examples of schemas using this type of relation to provide unlimited parent-child relations: