Best way to organize this structure?

Question

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

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?

Answer 1

I suggest reading this Managing Hierarchical Data in MySQL

Answer 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}

Answer 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))