Click here to Skip to main content
15,861,168 members
Articles / Database Development / SQL Server
Article

Retrieve Hierarchical Data from Database

Rate me:
Please Sign up or sign in to vote.
4.69/5 (9 votes)
30 May 2012CPOL4 min read 40.5K   19   7
Retrieve hierarchical data from database in effective way!

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 a hierarchical table like below (Geographic Location Hierarchy).<o:p>  

 

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.  

SQL
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 [PRIMARYCreate Table script for above table.  

 

Insert script for above table. 

SQL
 /* 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')  

GLSubordinates 

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

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

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

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

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

C#
 public enum HierarchicalDataType
   {
     GULocation = 1,
     OrganizationHierarchy = 2,
     ProductHierarchy = 3,
     ServiceCenterHierarchy = 4
   }
Implementing  HierarchicalData class.  
SQL
 public class HierarchicalData
{
    HierarchicalDataType dataType;
    HierarchicalData parent;
    string hierardhicalDataCD;
    string hierardhicalDataDesc;
    int order;
    IList<HierarchicalData> child;
 } 

 

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. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
United States United States
Solution Architect with 19 years of experience in IT

Comments and Discussions

 
SuggestionTypo Pin
Gabor Boli20-Jul-15 6:34
Gabor Boli20-Jul-15 6:34 
QuestionGood Pin
Altaf N Patel10-Aug-14 22:36
Altaf N Patel10-Aug-14 22:36 
GeneralMy vote of 5 Pin
RaisKazi8-Jun-12 13:42
RaisKazi8-Jun-12 13:42 
GeneralRe: My vote of 5 Pin
Joshi, Rushikesh8-Jun-12 16:59
professionalJoshi, Rushikesh8-Jun-12 16:59 
GeneralMy vote of 5 Pin
umlcat8-Jun-12 11:06
umlcat8-Jun-12 11:06 
GeneralRe: My vote of 5 Pin
Joshi, Rushikesh8-Jun-12 11:36
professionalJoshi, Rushikesh8-Jun-12 11:36 
GeneralMy vote of 5 Pin
DishR7-Jun-12 17:18
DishR7-Jun-12 17:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.