Query for updating a node in hierarchial tree structure

Be it on the Human Resources side, where we had to represent the organisational structure, or on the safety side, where we had injured parts of the body or underlying causes of incidents; a lot of the data was naturally tree-like (and we rendered it as such in the client).Lots of requests back and forth to the database were not an option due to performance constraints of the network (think high latency, low bandwidth, third world), so we needed to query this hierarchical data in a single efficient hit prior to rendering it in whatever manner was needed.

query for updating a node in hierarchial tree structure-21

Let’s go ahead and create some additional offices and populate them with sales data.

Results: Now that we have populated the table we want to be able to easily query and display the hierarchy.

We also used this hierarchical information extensively in our security model to determine which users could see information about other users based on their relative position(s) in the organisation.

This required relatively complex comparisons of trees to be performed quickly.

The deletion of a node is based off of the lft and rght values of the entity.

In this company sales are measured by territories, which are represented by offices.

The hierarchyid data type enables the creation of a hierarchical data in a relation database with significantly less maintenance overhead.

of the advantages of hierarchyid data type: method is called on a hierarchyid data type and takes two parameters.

The two parameters represent the ordinal position of the node.

Tags: , ,