组织这种结构的最佳方式？

I have a database of foods, which I would like to divide into a tree structure of categories, subcategories and sub-subcategories. For example,

我有一个食物数据库,我想将其划分为类别,子类别和子子类别的树结构。例如,

fruits -> apples -> fuji, or fruits -> apples -> cortland

水果 - >苹果 - >富士,或水果 - >苹果 - > cortland

I would like each parent page to show its immediate children (fruits page shows apples, oranges and all other fruit; apples page shows fuji, cortland and all other apples).

我希望每个父页面显示其直接的孩子(水果页面显示苹果,橙子和所有其他水果;苹果页面显示富士,科特兰和所有其他苹果)。

Also, I would like each node to know all its parent nodes. (fuji knows that its parent is apple, whose parent is fruit)

此外,我希望每个节点都知道其所有父节点。 (富士知道它的父母是苹果,其父母是水果)

What is the best way to store this tree-like relationship using MySQL?

使用MySQL存储这种树状关系的最佳方法是什么?

3 个解决方案

#1

I suggest reading Managing Hierarchical Data in MySQL.

我建议阅读MySQL中的管理分层数据。

#2

You could try something like:

你可以尝试类似的东西:

Table category:

id INT AUTO INCREMENT PRIMARY KEY
parent_id INT
name VARCHAR(40)

Then when you want to display all the sub-categories of fruit you can do:

然后,当您想要显示水果的所有子类别时,您可以执行以下操作:

SELECT C.* FROM category C WHERE C.parent_id = {$currentCategory}

#3

CREATE TABLE Food (
    Id INT NOT NULL AUTO_INCREMENT,
    Name CHAR(50) NOT NULL,
    CategoryId INT,
    PRIMARY_KEY(Id),
    FOREIGN KEY (CategoryId) REFERENCES FoodCategory (Id))
CREATE TABLE FoodCategory (
    Id INT NOT NULL AUTO_INCREMENT,
    Name CHAR(50) NOT NULL,
    ParentCategoryId INT,
    PRIMARY_KEY(Id),
    FOREIGN KEY (ParentCategoryId) REFERENCES FoodCategory (Id))

#1