Whether you're building your own forums, posting messages on your site or writing your own CMS, you'll encounter situations where you want to store hierarchical data in a database. Also, unless you use a database like XML, the tables in the relational database are not hierarchical, they are just a flat list. So you have to find a way to transform a hierarchical database.
The storage tree structure is a common problem, and he has several solutions. There are two main methods: Adjacency list model and improved first order traversal tree algorithm
In this article, we'll explore these two ways to save hierarchical data. I'll give an example of an online grocery store tree. The Food store organizes food by category, color and variety. Tree is as follows:
This article contains examples of code to demonstrate how to save and retrieve data. I chose PHP to write examples, because I often use this language, and many people are using it or know it. You can easily translate them into your own language.
Adjacency list models (the adjacency list model)
The first-and most graceful-method we try is called "adjacency list Model" or "recursive method". It's a very elegant approach because you just need an easy way to iterate through your tree. In our grocery store, the table of adjacency list is as follows:
As you can see, save a "parent" node for each node. We can see that "Pear" is a child of "Green", and the latter is a child node of "Fruit", and so on. Root node, "Food", then his parent node has no value. For simplicity, I used only the "title" value to identify each node. Of course, in the actual database, you want to use the ID of the number.