Home mysql Storing a tree in a relational database

Storing a tree in a relational database




Hello everyone! Guys, I ran into the problem of storing a tree in a database … the way I worked with databases very little, I ask for your help!
The goal is: there are product categories, each product category has subcategories, and so on, for example:

Shoes --- Men
      | ----- Summer / Winter
          | ---- Leather / not leather / not leather at all
             | --Chinese / Non-Chinese / Japanese

etc. Ie, the number of levels in each branch is not known (maybe 10, maybe 100)

Please help design a database to store this kind of trees

Thank you very much in advance!

Answer 1, authority 100%

Well, if you follow the structure suggested by me, it will look like this:

Id Parent_id Name
 1 0 Shoes
 2 1 Male
 3 1 Women
 4 1 Children
 5 4 Summer / Winter
 6 5 Leather / not leather / not leather at all
 7 6 Chinese / non-Chinese / Japanese

Answer 2, authority 75%

A classic example where the view model works well out of the box Nested set . Examples and benchmarks for different DBMS can be found, for example, here.

Alternatively (if the category tree is not too large) this tree can be serialized to BLOB and processed on the client side. That is, each record of the Clothes type stores some id , which allows you to restore all categories at any time from the deserialized tree.

Thus, you can move the code of the following type to the client side

SELECT Child.Node, Child.LEFT, Child.RIGHT
FROM Tree AS Child, Tree AS Parent
    Child.Depth = Parent.Depth + 1
    AND Child.LEFT & gt; Parent.LEFT
    AND Child.RIGHT & lt; Parent.RIGHT
    AND Parent.LEFT = 1 - Given Parent Node Left Index

Answer 3, authority 75%

There are many methods, choose your taste:

It all depends on the intended properties of the tree and its use.

Answer 4, authority 50%

Nested set is one of the most convenient data presentation models, if you need to use nested lists, create catalogs.

It is understandable, the main thing is to read a small manual on how everything is done there and how it works, the rest is small – try, make and start using it.

I use just such a data representation in my engine. This is convenient, saves time for fetching data, you can easily select separate branches, etc.

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions