More Efficient Way for Handling Hierarchical Structure in Relational DatabaseΒΆ
Section author: momo <mobeiheart@gmail.com>
When developing an employee management system, a common and typical problem is finding all subordinates of somebody, direct and indirect.It can be done using recursive way:
def expand_subordinates(employee):
direct_subordinates = Employee.objects.filter(superior=employee)
indirect_subordinates = []
for subordinate in direct_subordinates:
direct, indirect = expand_subordinates(subordinate)
indirect_subordinates.extend(direct)
indirect_subordinates.extend(indirect)
return direct_subordinates, indirect_subordinates
It must be slow in relational database, the higher level the employee is in, the slower the query is.
But there are efficient way of handling this hierarchical data structure, the well-known Django mptt and others like sqlamp or sqlalchemy nested set example.They add additional two fields to implement modified preorder tree traversal.