What is the best way to setup hierarchy in sql server 2005?

Question

Hi all,

I am trying to setup an hierarchial structure for company. I was using SQL Server 2008 and used hierarchy id to set it up. But now I need to move back to SQL Server 2005...and "just do it"... Anyways I thought of setting it up this simple way -

Id | ParentId | CompanyName | Desc

where ParentId is a int field which will store id of the parent. I guess the root will have its ParentId as zero. Are there better ways to setup the hierarchy structure?

I really don't have that complex requirements for hierarchy... I want to know just what would make I guess traversing though the hierarchy easier and working with it more efficient.

Answer 1

Joe Celko's method of using nested sets where your table has a "left" and "right" column referring to the hierarchy is how I have usually seen it done

Joe Celko will probably explain it better than I can Nested sets

Answer 2

The "simple way" is fine and works well with CTEs (Common Table Expression). However, as suggested by Kev, there are other ways which have their pros and cons.

So in the end it depends on your exact requirements, and how much insert vs. hierarchical queries will be done on the data because the performance of the different approaches can vary a lot in this regard.

Answer 3

Unfortunately, as far as I know the way you are setting it up is the correct way. You can't traverse the links as easily now because you lose GetAncestor and GetDescendant. A decent replacement is to use CTEs to replace GetAncestor and GetDescendant, and use them recursively.

Here is an example (using a menu hierarchy):

WITH MenuCTE(MenuKey, ParentMenuKey, MenuName) AS
(
-- Anchor Query
SELECT MenuKey, ParentMenuKey, MenuName FROM Menu WHERE MenuKey = 1
UNION ALL
-- Recursive Query
SELECT m.MenuKey, m.ParentMenuKey, m.MenuName FROM Menu m
INNER JOIN MenuCTE r ON m.ParentMenuKey = r.MenuKey
)
SELECT MenuKey, ParentMenuKey, MenuName FROM MenuCTE

This article should help (example is from here):

http://www.infoq.com/news/2007/10/CTE