Retrieve Hierarchical Data from Database

Introduction

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. 

Background  

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 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 queries */
 
INSERT INTO [GLHierarchy] Values ('AP0','Asia-Pacific',null)

-- Level 2 Records -- 
INSERT INTO [GLHierarchy] Values ('IND','India','AP0')
INSERT INTO [GLHierarchy] Values ('PHL','Phillipines','AP0')
INSERT INTO [GLHierarchy] Values ('CHN','China','AP0')

-- Level 3 Records -- 
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')
 
-- Level 4 Records --
INSERT INTO [GLHierarchy] Values ('BOM','Mumbai','MAH')
INSERT INTO [GLHierarchy] Values ('PUN','Pune','MAH')
INSERT INTO [GLHierarchy] Values ('BAN','Bangalore','KAR')  

----- create GLSubordinates function using CTE
if object_id( 'GLSubordinates', 'IF' ) is not null 
	drop function GLSubordinates
GO
 
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 
union all 
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
 
GO  

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. 

----- create the Superiors function using CTE  
if object_id( 'GLSuperiors', 'IF' ) is not null 
	drop function GLSuperiors
GO 

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 )
            union all  
           (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 
GO 

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). 

Implementing  HierarchicalDataType enum. 

 public enum HierarchicalDataType
   {
     GULocation = 1,
     OrganizationHierarchy = 2,
     ProductHierarchy = 3,
     ServiceCenterHierarchy = 4
   }

 public class HierarchicalData
{
    HierarchicalDataType dataType;
    HierarchicalData parent;
    string hierardhicalDataCD;
    string hierardhicalDataDesc;
    int order;
    IList<HierarchicalData> child;
 }