design for an Organization's Hierarchy

design for an Organization's Hierarchy

Post by Jami » Thu, 18 Dec 2003 00:04:53



Does anyone have any db design or stored procedure
examples for reporting an organization's hierarchy (or
chain of command) ?

I have customer table that has a customer_id field and a
reports_to field. I need to generate a report sorted by
top level customer #1, followed by everyone reporting to
them, top level customer #2, followed by everyone
reporting to them, etc.

Can recursion be used w/ SQL Server 7.0 to do this ?  I
think it only supports 32 levels of nesting - currently my
organizational data has at most 19 levels.

 
 
 

design for an Organization's Hierarchy

Post by Anith Se » Thu, 18 Dec 2003 05:21:42


There are different approaches in t-SQL which you can use. Check out some of
the following links & see if they help :
http://www.intelligententerprise.com/001020/celko.shtml
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8826
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
http://www.sqlteam.com/item.asp?ItemID=8866
http://www.dbazine.com/tropashko4.html

--
- Anith
( Please reply to newsgroups only )

 
 
 

design for an Organization's Hierarchy

Post by Joe Celk » Thu, 18 Dec 2003 18:12:03


Look up "nested sets model" for one answer.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

design for an Organization's Hierarchy

Post by Rober » Fri, 19 Dec 2003 18:22:06


CREATE            PROCEDURE dbo.rpt_AgentProdHierarchy

AS

SET NOCOUNT ON

CREATE TABLE #tempAgentHier
(       PK              int IDENTITY (1, 1) NOT NULL,
        AgentID         int,
        HierarchyID     int,
        HierLinkID              int,
        Traversed       int,
        HierLevel       smallint,
        FullName        varchar(71),
        ContractReceived                datetime)

DECLARE










--Get first (top Level) Agent

INSERT INTO #tempAgentHier
(AgentID,HierarchyID,HierLinkID, HierLevel,Traversed,
FullName,ContractReceived)
SELECT
        Hierarchy.AgentID,
        Hierarchy.HierarchyID,
        Hierarchy.HierLinkID ,


        CASE WHEN Agents.IsAgency=1
                Then Agents.AgencyName
                ELSE Agents.FullName
        END,
        Agents.UserDefinedDate2

FROM GBSDATA.dbo.Agents Agents
        INNER JOIN dbo.Hierarchy
                ON dbo.Hierarchy.AgentID=Agents.AgentID






                INSERT INTO #tempAgentHier
                        (AgentID,HierarchyID,HierLinkID,
HierLevel,FullName,ContractReceived)
                        SELECT
                                Hierarchy.AgentID,
                                Hierarchy.HierarchyID,
                                Hierarchy.HierLinkID ,

                                CASE WHEN
Agents.IsAgency=1
                                        Then
Agents.AgencyName
                                        ELSE
Agents.FullName
                                END,
                                Agents.UserDefinedDate2

                        FROM GBSDATA.dbo.Agents Agents
                                INNER JOIN dbo.Hierarchy
                                        ON
dbo.Hierarchy.AgentID=Agents.AgentID
                                WHERE

                                ORDER BY CASE WHEN
Agents.IsAgency=1
                                        Then
Agents.AgencyName
                                        ELSE
Agents.FullName
                                END desc


#tempAgentHier
                                        WHERE PK =
(SELECT MAX(PK) FROM #tempAgentHier WHERE Traversed IS


        BEGIN

        END

        ELSE
        BEGIN

        END



SELECT
        #tempAgentHier.* ,

        FROM #tempAgentHier
                INNER JOIN GBSDATA.dbo.Agents Agents
                        ON
Agents.AgentID=#tempAgentHier.AgentID

Order By Traversed
DROP TABLE #tempAgentHier

Quote:>-----Original Message-----
>Does anyone have any db design or stored procedure
>examples for reporting an organization's hierarchy (or
>chain of command) ?

>I have customer table that has a customer_id field and a
>reports_to field. I need to generate a report sorted by
>top level customer #1, followed by everyone reporting to
>them, top level customer #2, followed by everyone
>reporting to them, etc.

>Can recursion be used w/ SQL Server 7.0 to do this ?  I
>think it only supports 32 levels of nesting - currently
my
>organizational data has at most 19 levels.
>.

 
 
 

design for an Organization's Hierarchy

Post by Rober » Fri, 19 Dec 2003 18:22:40


CREATE            PROCEDURE dbo.rpt_AgentProdHierarchy

AS

SET NOCOUNT ON

CREATE TABLE #tempAgentHier
(       PK              int IDENTITY (1, 1) NOT NULL,
        AgentID         int,
        HierarchyID     int,
        HierLinkID              int,
        Traversed       int,
        HierLevel       smallint,
        FullName        varchar(71),
        ContractReceived                datetime)

DECLARE










--Get first (top Level) Agent

INSERT INTO #tempAgentHier
(AgentID,HierarchyID,HierLinkID, HierLevel,Traversed,
FullName,ContractReceived)
SELECT
        Hierarchy.AgentID,
        Hierarchy.HierarchyID,
        Hierarchy.HierLinkID ,


        CASE WHEN Agents.IsAgency=1
                Then Agents.AgencyName
                ELSE Agents.FullName
        END,
        Agents.UserDefinedDate2

FROM GBSDATA.dbo.Agents Agents
        INNER JOIN dbo.Hierarchy
                ON dbo.Hierarchy.AgentID=Agents.AgentID






                INSERT INTO #tempAgentHier
                        (AgentID,HierarchyID,HierLinkID,
HierLevel,FullName,ContractReceived)
                        SELECT
                                Hierarchy.AgentID,
                                Hierarchy.HierarchyID,
                                Hierarchy.HierLinkID ,

                                CASE WHEN
Agents.IsAgency=1
                                        Then
Agents.AgencyName
                                        ELSE
Agents.FullName
                                END,
                                Agents.UserDefinedDate2

                        FROM GBSDATA.dbo.Agents Agents
                                INNER JOIN dbo.Hierarchy
                                        ON
dbo.Hierarchy.AgentID=Agents.AgentID
                                WHERE

                                ORDER BY CASE WHEN
Agents.IsAgency=1
                                        Then
Agents.AgencyName
                                        ELSE
Agents.FullName
                                END desc


#tempAgentHier
                                        WHERE PK =
(SELECT MAX(PK) FROM #tempAgentHier WHERE Traversed IS


        BEGIN

        END

        ELSE
        BEGIN

        END



SELECT
        #tempAgentHier.* ,

        FROM #tempAgentHier
                INNER JOIN GBSDATA.dbo.Agents Agents
                        ON
Agents.AgentID=#tempAgentHier.AgentID

Order By Traversed
DROP TABLE #tempAgentHier

Quote:>-----Original Message-----
>Does anyone have any db design or stored procedure
>examples for reporting an organization's hierarchy (or
>chain of command) ?

>I have customer table that has a customer_id field and a
>reports_to field. I need to generate a report sorted by
>top level customer #1, followed by everyone reporting to
>them, top level customer #2, followed by everyone
>reporting to them, etc.

>Can recursion be used w/ SQL Server 7.0 to do this ?  I
>think it only supports 32 levels of nesting - currently
my
>organizational data has at most 19 levels.
>.

 
 
 

1. pro's and con's of the nested subsets model for hierarchies

What is the downside of using the nested subset model for storing
frequently accessed and updated hierarchies.

I have created a test hierarchy with over 4000 rows in the hierarchy
spread over 7 levels stored in a table defined by this SQL

create table nestedSets
(
[id] bigint identity (1,1) primary key,
[parentid] bigint references nestedSets([id]),
[desc] varchar(100) not null,
[level] int not null ,
[leftExtent] bigint,
[rightExtent] bigint
)

Obviously the [parentId] and [level] columns are superfluous and only
there for display during development.

Reads on this structure are fast and simple, avoiding the need for any
tree walking. But to insert a row using the SQL below





    = (SELECT rightextent
         FROM nestedsets

UPDATE nestedsets

                  THEN leftextent + 2
                  ELSE leftextent END,

                  THEN rightextent + 2
                  ELSE rightextent END

INSERT INTO nestedsets (parentid,[desc],[level], leftextent,
rightextent)


can result in potentially 99% of the table being updated and similarly
for deletes.

Surely, this will lock the table destroying the system's concurrency?

Doesn't this make the nested subsets model unsuitable for OLAP
environments where the hierarchy is not mostly static?

Or could the use of optimizer hints on the update, delete and select
statements solve this?

What are people's thoughts on this? How have other people used the
model in an OLAP environment.

Thanks,

Michael

2. Update query help

3. db's with data organization control

4. Addins

5. Advice needed on Hierarchy Design

6. onstat -g sql

7. Table Design: Hierarchy

8. Design issue: nested menu options / hierarchy

9. query design for XML from hierarchy table

10. Designing a changing Hierarchy