How to save a tree in mysql?

Hy!!

I want to save a category to my cooking recipes.

Like: Spaghetti Bolognese

meal-->noodle_dishes-->italian-->spaghetti_bolognese 

How to save such a inheritance?

THX

If you don't mind doing some housework around the management of your category tree, then you can use a scheme whereby the recipe goes into the leaf (lowest) level cateogry and all of the other category relationships are flattened out using bounding numbers. You would still have the recursive relationship for managing the tree but you wouldn't use it to retrieve/query for recipies.

Here is what it would look like:

Category
- Category ID
- Name
- (anything else you want to know about categories)
- Parent Category ID (FK to self / NULL for top level)
- Lower Bound
- Upper Bound

Recipe
- Recipe ID
- Category ID (FK - points to the lowest level / most specific category)
- (anything else you want to know about a recipe)

The trick is the Lower Bound and Upper Bound. You populate the category table like this:

Category: 
  Desserts ID=1 Parent=null {Lower=1, Upper=4}
  Cakes ID=2 Parent=1 {2, 4}
  Chocolate Cakes ID=3 Parent=2 {3, 3}
  Fruit Cakes ID=4 Parent=2 {4, 4}
  Appetizers ID=5 Parent=null {Lower=5, Upper=...}
...

So when you query for recipies that are deserts, for example, you find all recipies that have a Category ID between the lower bound and upper bound of the Cakes category (i.e. between 2 and 4).

In order to make this work, you need to have procedural code that will go back and recalculate all of your lower and upper bounds in your categories table whenever you make a change to the categories hierarchy. Since this is something that should happen rarely by comparison to changes to recipies. If you want to be elegant, you can find ways to limit the recalculation to just the impacted areas.

A warning note, for simplicity of explaining the concept I've set the lower and upper limits based on the category ID. Obviously you can't use an incremental ID for this purpose since your categories have to be sorted. Therefore your categories table needs a second candidate key which can be recalculated when you do your recursive limit calculation at hierarchy change time. So at query time you are using the real (incrementing) category ID to find the leaf category and then using the lower and upper bounds from that category record to filter other categories and do a join out to recipies that fit your criteria.

Simplest would be for the "Category" table to have a foreign key on itself, called "parent category" or something similar. Top level categories (i.e. meal) would have NULL in this field.

I think the best solution is to not treat them as a tree, and instead go the tag route. So you would have a recipe table and a recipe tag table.

You could do it as one-to-many relationship, example:

    category
--------------
    id
    cat_name
    cat_parent

where cat_parent references to id of parent category

However, be aware that MySQL does not support recursive queries, so using such design, you cannot for example do deep subcategory search. I've read it is possible using different design, I just cannot remember the source.

More detailed article on MySQL hierarhical data

So assuming recipe might have a name, a category, a list of ingredients-- and perhaps an author or other metadata -- and you want to place all your recipes in a tree.

The dead-simple way would be to add another column to the recipes table -- parent_recipe_id -- and then just assume that those rows with a parent ID are 'roots' and should be presented as basic categories.

(Kris' suggestion of a separate 'category' or recipe group table would be more explicit about the tree structure.)