0

I am saving data with unknown number of branches in JSON form for now and children can have unknown number of children.

example1 - {employee_1:{employee_2:{employee_3:{}}}}
example2 - {employee_3:{employee_2:employee_1:{}}, employee_4:{employee_3:{}}}

example hierarchical structure

Now to fetch this data, i have to first fetch this hierarchy saved as json and then fetch the id's stored in this json

is there any way to fetch this in one query?
or is there better approach to store and retrieve this?

i am using MySQL

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
atul1039
  • 143
  • 4
  • 13
  • 1
    Traditionally with MySQL, you'd make an Employee table with the columns, "id" and "parent_id". Then you'd just save all the employee records in this one table. Since you have recorded the parent id for each, then you can recreate the structure on the front end whenever you need to with recursive functions. – kloddant Feb 22 '22 at 16:32
  • @kloddant in example1 employee2 parent is employee1, but we can set employee1 parent employee2, employees can have multiple parents. Thanks for the reply – atul1039 Feb 22 '22 at 18:04
  • Ah, in that case, you would have a Many-To-Many relation instead of a Many-To-One relation. That can be handled with an intermediary table. So you have two tables: employee and employee_relation. The employee_relation table contains the parent_id and the child_id, and the employee table just has the employee ids but no parent ids because those are all stored in the relation table. – kloddant Feb 22 '22 at 19:24

1 Answers1

0

Employee Table

id name
1 Alice
2 Bob
3 Emily
4 Brian

Example 1 Employee Relation Table

id parent_id child_id
1 2 3
2 1 2

Example 2 Employee Relation Table

id parent_id child_id
1 4 3
2 2 1
3 3 4
4 3 2
kloddant
  • 1,026
  • 12
  • 19
  • i have 50+ employee so cannot make 50+ tables and if it is 1 table then it will make a loop when fetching, like id 1 have child id2 and id2 have id1 as child. this is what i understand – atul1039 Feb 23 '22 at 08:23
  • Ah sorry, misunderstood again. In that case, I can't think of a better way than your JSON method. The disadvantage of the JSON method is that you lose the ability to index different columns, so you won't be able to search for descendant employees, only root employees. But, I can't think of a way around this at the moment. – kloddant Feb 23 '22 at 14:08