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
Womens
Children
|
| ----- 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
WHERE
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.