CODEWITHSUNDEEP
phpmysqldatabase-design
user114060
user114060

Reputation:

Best way to organize this structure?

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?

Upvotes: 0

Views: 273

Answers (3)

jitter
jitter

Reputation: 54605

I suggest reading Managing Hierarchical Data in MySQL.

Upvotes: 4

Jacob
Jacob

Reputation: 78850

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

Upvotes: 0

Kevin Dungs
Kevin Dungs

Reputation: 1577

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}

Upvotes: 0

Related Questions