Mapping Hierarchical Relationships

Question

Post reply

Mapping Hierarchical Relationships

Xedni

SSCertifiable

Points: 6951
More actions
December 31, 2014 at 2:45 pm

#293437

Hey all,
I’m trying to build something which allows different user groups to inherit properties from other groups. For instance, a given user group (say 5) would inherit the universe definition of another user group (say 4). The catch is that this might in turn inherit its universe from another user group (say 3, and so on and so forth).
What I want is a way to give the system a user group and return the actual user group from which it inherits.
So extending my previous example above, suppose the following structure of inheritance:
5 <= 4 (5 inherits from 4)
4 <= 3
3 <= 2
2 <= 1
∴ 5 <= 1 (5 inherits from 1)
I feel like there’s a way to do this with an rCTE but the specifics are eluding me. Any suggestions? Here’s some sample code
if object_id('tempdb.dbo.#Inherit') is not null drop table #Inherit
create table #Inherit
(
UserGroupId int,
Property varchar(30) check (Property in ('Universe', 'EventInstances', 'Scores')),
InheritedUserGroupId int
primary key clustered (UserGroupId, Property)
)
-- Sample inheritence hierarchy
insert into #Inherit
(
UserGroupId,
Property,
InheritedUserGroupId
)
select 5, 'Universe', 4 union all
select 5, 'EventInstances', 4 union all
select 5, 'Scores', 4 union all
select 4, 'Universe', 1 union all
select 4, 'Scores', 2
--What I'd like to get as an output in one form or another
select
UserGroupID,
Property,
InheritedUserGroupID = case when UserGroupID = 5 and Property = 'EventInstances' then 4
when UserGroupID = 5 and Property = 'Universe' then 1
when UserGroupID = 5 and Property = 'Scores' then 2
else InheritedUserGroupId
end
from #Inherit
Executive Junior Cowboy Developer, Esq.[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

I was waiting for Jeff Moden to answer, but since he hasn't yet, you might want to read his article:

http://www.sqlservercentral.com/articles/T-SQL/94570/

or

http://blogs.msdn.com/b/mvpawardprogram/archive/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets.aspx

Or maybe one by Dwain Camps

https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

pietlinden (12/31/2014)

I was waiting for Jeff Moden to answer, but since he hasn't yet, you might want to read his article:

http://www.sqlservercentral.com/articles/T-SQL/94570/

or

http://blogs.msdn.com/b/mvpawardprogram/archive/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets.aspx

Or maybe one by Dwain Camps

https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/%5B/quote%5D

Thanks Pieter.

@JeeTee,

What I'm trying to understand is the following diagram from your original post...

5 <= 4 (5 inherits from 4)

4 <= 3

3 <= 2

2 <= 1

? 5 <= 1 (5 inherits from 1)

According to that diagram, 5 inherits from 4, 4 inherits from 3, 3 inherits from 2, and 2 inherits from 1. Through that hierarchical relationship, 5 inherits from everyone... including 1. Why would there be a need to establish the edge between 5 and 1?

I'm also trying to figure out in the example why there is no user group ID row for inherited user groups 1 and 2.

There seems to be some missing data in the example.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Xedni SSCertifiable Points: 6951 More actions · Answer 3

thanks for the replies. I'm heading out for the evening so I haven't had a chance to read the articles yet but I will.

Jeff, to your question perhaps the data I provided was not realistic. in reality it's a ragged hierarchy of user groups inheriting different properties from different user groups. no user group necessarily inherits from any other user group nor is there a parent to all user groups. so you can't know ahead of time that everything inherits from one user group or another hence the need to at run time: determine which user group inherits from which other user group(s).

the point of the 5 to 4, 4 to 3 etc was to illustrate that a user group could inherit a property from another user group which, itself, was inherited.

Executive Junior Cowboy Developer, Esq.[/url]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Ah. Understood.

The first article the PietLinden pointed you to has a near twin right here on SSC. It's located at the following URL:

http://www.sqlservercentral.com/articles/Hierarchy/94040/

That article shows how to convert an Adjacency List (even a really big one) into Nested Sets. It doesn't, however, show you how to use Nested Sets, which I think are definitely the way to go here. If you don't know what Nested Sets are or how to use them, please post back and I'll see what I can do to help in that area.

That, notwithstanding, Nested Sets might be serious overkill for something so small. With that in mind, you can do the type of thing you're talking about with rCTEs (Recursive CTEs). A small example of what I'm talking about can be found at the following article.

http://www.sqlservercentral.com/articles/T-SQL/72503/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)