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%
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.