Hierarchical Data representation is quite simple but it's management and retrieving is quite complex. This article describe various techniques to store and retrieve hierarchical data.
Hierarchical Data Model is proven modeling technique to implement hierarchical business entities like (GL Hierarchy, Organization Hierarchy, Menu Hierarchy etc…). Hierarchical data can also use to retrieve role based menu from database. Managing data and traversing through Hierarchical data itself is tough job.
Using the code
Store and Manage Hierarchical Data
Ideally hierarchical data is representing by level (depth) and consider it should use Tree Data Structure Algorithm. Now it is important to design such table using Hierarchical Data Modeling and creating queries which will return hierarchical data in optimized manner.
Create a hierarchical table like below (Geographic Location Hierarchy).
Insert some records in this table. (Please find insert script in attached SQL file)
Now it is important to retrieve data of this table in hierarchical format. That means if we are requesting for particular Geographic Location (say ‘India’) then query should return all child-grand child records for India. To achieve this we will use CTE (Common Table Expression). Please refer http://msdn.microsoft.com/en-us/library/ms190766.aspx for other usage of CTE.
Create Table script for above table.
CREATE TABLE [GLHierarchy](
[GUID] [int] IDENTITY(1,1) NOT NULL,
[GUCD] [varchar](10) NOT NULL,
[GUDesc] [varchar](50) NOT NULL,
[ParentGUCD] [varchar](10) NULL
) ON [PRIMARY] Create Table script for above table.
Insert script for above table.
INSERT INTO [GLHierarchy] Values ('AP0','Asia-Pacific',null)
INSERT INTO [GLHierarchy] Values ('IND','India','AP0')
INSERT INTO [GLHierarchy] Values ('PHL','Phillipines','AP0')
INSERT INTO [GLHierarchy] Values ('CHN','China','AP0')
INSERT INTO [GLHierarchy] Values ('MAH','Maharashtra','IND')
INSERT INTO [GLHierarchy] Values ('KAR','Karnataka','IND')
INSERT INTO [GLHierarchy] Values ('GUJ','Gujarat','IND')
INSERT INTO [GLHierarchy] Values ('MNL','Manila','PHL')
INSERT INTO [GLHierarchy] Values ('BOM','Mumbai','MAH')
INSERT INTO [GLHierarchy] Values ('PUN','Pune','MAH')
INSERT INTO [GLHierarchy] Values ('BAN','Bangalore','KAR')
Consider that you have requirement where you want return Geographic Location tree by passing GLCd. To achieve this you need to write CTE query. CTE query are bit complex to understand so we will create some functions to simplify this query. We will create GLSubordinates function, which accept GLCd (GL Code).
if object_id( 'GLSubordinates', 'IF' ) is not null
drop function GLSubordinates
create function GLSubordinates( @GLCD varchar(10) )
returns table as return with GLSubnodes( distance, GLID, GLCD, GLDesc, ParentGLCD, NodeSequence )
AS ( select 0, h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD, convert( varchar(80), ltrim(str(h.GLID))) as NodeSequence
from [GLHierarchy] h
where h.GLCD = @GLCD
select distance+1, h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD, convert( varchar(80), sn.NodeSequence+'.'+ltrim(str(h.GLID)))
from [GLHierarchy] h inner join GLSubnodes sn on h.ParentGLCD = sn.GLCD)
select distance, GLID, GLCD, GLDesc, ParentGLCD, NodeSequence from GLSubnodes
Select records for 'IND' from subordinates.
select * from GLSubordinates('IND')
Above statement should return below result.
Above example, returns all the child-grandchild Geographic Location for GLCD = ‘IND’. NodeSequence is optional field, it is just showing depth of tree you can see 2, 2.5, 2.5.9, 2.5.10 etc…
CTE Queries performs quite efficiently. With index on GLID and GLCD with data up to 12 hierarchical levels for 24000 records this query is returning data less than a second.
By default recursion limit for CTE queries is 100 levels. And I guess it is enough level for normal hierarchical data, but you can also change it.
For more information, please refer http://msdn2.microsoft.com/en-us/library/ms181714.aspx.
You can use this function directly or in your queries. By adding Level check, you can customize this function to return tree up to specified level.
Retrieve Hierarchical Data
We can retrieve hierarchical data from last node to root node.
It can be possible that you want to know grand parent information of a child element. By implementing Superior function for hierarchical data you can achieve same functionality.
Consider a requirement where some condition is applicable to one City then it should applicable to all parents Nodes of that hierarchy.
To achieve this functionality we need to create GLSuperiors function. This function is also using CTE (Common Table Expression) query. GLSuperiors function is traverse through hierarchical information and returns the traversed hierarchy in opposite direction.
if object_id( 'GLSuperiors', 'IF' ) is not null
drop function GLSuperiors
create function GLSuperiors( @GUCD varchar(10))
returns table as return with GLSupnodes
( distance, GUID, GUCD, GUDesc, ParentGLCD ) as
(select 0, h.GUID, h.GUCD, h.GUDesc, h.ParentGUCD
from [GLHierarchy] h where h.GUCD = @GUCD )
(select distance-1, h.GUID, h.GUCD, h.GUDesc, h.ParentGUCD
from [GLHierarchy] h inner join GLSupnodes sn on h.GUCD = sn.ParentGLCD )
select distance, GUID, GUCD, GUDesc, ParentGLCD from GLSupnodes
Now select hierarchical data for 'BOM' city code.
SELECT * FROM GLSuperiors('BOM')
Above select statement returns all the parent-grandparent Geographic Location for GLCd = 'BOM'.
We are done with managing and retrieving hierarchical data. We can use same functionality to populate hierarchical menu, company hierarchy or genealogy etc...
Improve retrieving of Hierarchical Data in C# application.
Usually we are storing non-transactional hierarchical data in to the database, which are updating periodically and not taking part in transactions. We can use caching mechanism from Enterprise Library Application Block. We need to create parent-child data structure to store hierarchical data in our application and we just need to store root data for individual hierarchical data at some place (Cache).
public enum HierarchicalDataType
GULocation = 1,
OrganizationHierarchy = 2,
ProductHierarchy = 3,
ServiceCenterHierarchy = 4
public class HierarchicalData
Now you just need to populate a tree using your hierarchical data. This should be one time activity you can call from Application_Start and you have to store the root node in to the cache. For better accessibility you can store node information in key-value combination (HashTable), where key is hierarchical data CD (must be a unique) and value is the main HierarchicalData object.
Consider that we have one webpage where there is some dependent drop down for Country and user is selecting 'India' (CD: 'IND') then it should directly return all the Geographic Locations (State) with 'India' as parent.
This reduces database call, and giving performance to the non-transactional hierarchical data.
You can also maintain order, which can be useful to display in order. This is useful to create menus. By manipulate such functions, you can use them in AJAX, web services etc…
Points of Interest
This is part of my fun and learn activity. I may extend this article for further use.