Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Hierarchical Dilemma

Author  Topic 

Sintwar
Starting Member

11 Posts
Posted - 2011-07-06 : 09:41:22
Hello all

I am trying to figure out the best way to structure my data in a way that will allow me to have something like a rank based hierarchy.

What I have now looks like this

memberID Name Superior Rank
============================================
1 Bob 0 5
2 Frank 1 4
3 Joe 1 4
4 Tom 2 3
5 Josh 2 3
6 Karen 3 2
7 Kim 3 2
8 John 4 1
etc.


What I need to do is retrieve the 3 people directly above someone.
So if I wanted to retrieve Johns superiors, I would need data on Tom, Frank and Bob.

I also need that structure to remain even if someone is removed.

So for example, if Frank were removed, I would need to retrieve information on Tom and Bob, with everyone still maintaining their current rank and placement in the hierarchy (and allowing Franks position to be replaced by someone else eventually)

Would also need to retrieve the data going down, with and without empty positions.

How would you structure and retrieve that data?

There will be a limit to 2 people under each person, and a max rank of 10. There would ultimately be multiple hierarchies.

Thanks for any help you can offer!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2011-07-06 : 09:59:06
This doesn't really sound like a hierarchy.
Looks like you have a superior rank for a user and anyone with that rank or higher is considered superior.
Not sure about your use of the word "directly" what does that mean?

Don't see how removing Frank would affect anything else - just adding someone else with rank 4 would add them to John's superiors.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Sintwar
Starting Member

11 Posts
Posted - 2011-07-06 : 10:28:59
Alright so John who's superior is Tom, Tom's superior is Frank, and Franks superior is Bob.

Even though Bob is not John's "direct" superior, he is directly above John in the chain of command.

I need to be able to know who all is directly above John, and are up to 3 ranks above him.

So for example, even though Joe out ranks John, he is not Johns "superior" because he is in a separate chain from John.

But, imagine if Frank were promoted to rank 5 for example. He would no longer be under Bob, since Bob is rank 5, and can only have rank 4's directly beneath him. So Frank is removed from that particular hierarchy "position", leaving an empty position between Tom and Bob.

I need to be able to still know that John is under Tom and Bob, even with Frank gone, and I need John to be able to see who is directly above him up the chain, even if there are missing links. Eventually Frank' position will be filled by another rank 4 (we'll call him Stan)
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-07-06 : 10:35:18
How about separating the concept a little bit. If you consider only the structure of the positions, then you could represent this as a reqular hierarchy. You could see filled and unfilled positions, because it always exists.

You would then link people to a position, to get a person focused hierarchy.

Corey

I Has Returned!!
Go to Top of Page

Sintwar
Starting Member

11 Posts
Posted - 2011-07-06 : 10:42:06
Yeah, I had though about that, I just don't know how to "grow" it organically or dynamically.

In my mind, I would need to generate a million pre-existing hierarchies, all of their positions as physical records, and fill the positions (records) as people come in and advance.

Is there a more dynamic way to accomplish this?
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-07-06 : 11:03:12
quote:
Originally posted by Sintwar

In my mind, I would need to generate a million pre-existing hierarchies, all of their positions as physical records, and fill the positions (records) as people come in and advance.


Why?

Why can't you just create a table like you have shown already, that isn't focused on the person?

Corey

I Has Returned!!
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-07-06 : 11:21:57
Question - why is Frank Rank 1, but his superior is Rank 3? Does that mean there is a supposed to be a position in between?

Anyway... here is a sample:


Declare @Positions table (
	PositionId int identity(1,1),
	SuperiorId int,
	PositionRank int,
	Position varchar(25),
	Primary Key (PositionId)
)

Declare @Person table (
	PersonId int identity(1,1),
	PositionId int,
	PersonRank int,
	Name varchar(25)
)

Insert Into @Person Select 1, 5, 'Bob'
Insert Into @Person Select 2, 4, 'Frank'
Insert Into @Person Select 3, 4, 'Joe'
Insert Into @Person Select 4, 3, 'Tom'
Insert Into @Person Select 5, 3, 'Josh'
Insert Into @Person Select 6, 2, 'Karen'
Insert Into @Person Select 7, 2, 'Kim'
Insert Into @Person Select 8, 1, 'John'


Insert Into @Positions Select 0, 5, 'Owner'
Insert Into @Positions Select 1, 4, 'Sales VP'
Insert Into @Positions Select 1, 4, 'Operations VP'
Insert Into @Positions Select 2, 3, 'Domestic Sales'
Insert Into @Positions Select 2, 3, 'Foreign Sales'
Insert Into @Positions Select 3, 2, 'Production Ops'
Insert Into @Positions Select 3, 2, 'Service Ops'
Insert Into @Positions Select 4, 1, 'Peon'



Select * From @Positions
Select * From @Person


Declare @PersonId int

Set @PersonId = 8

;with Chain As (
	Select A.*, Lvl = 1
	From @Positions A
	Inner Join @Person B
	On A.PositionId = B.PositionId
	Where B.PersonId = @PersonId
	
	Union All
	
	Select A.*, Lvl = Lvl + 1
	From @Positions A
	Inner Join Chain B
	On A.PositionId = B.SuperiorId
)

Select A.*, B.Name 
From Chain A 
Left Join @Person B
On A.PositionId = B.PositionId
Order By Lvl desc


Update A Set PositionId = null From @Person A Where Name = 'Frank'


;with Chain As (
	Select A.*, Lvl = 1
	From @Positions A
	Inner Join @Person B
	On A.PositionId = B.PositionId
	Where B.PersonId = @PersonId
	
	Union All
	
	Select A.*, Lvl = Lvl + 1
	From @Positions A
	Inner Join Chain B
	On A.PositionId = B.SuperiorId
)

Select A.*, B.Name 
From Chain A 
Left Join @Person B
On A.PositionId = B.PositionId
Order By Lvl desc


Corey

I Has Returned!!
Go to Top of Page

Sintwar
Starting Member

11 Posts
Posted - 2011-07-06 : 12:00:30
Actually, in the example I give, Frank is rank 4, his superior is Bob who is rank 5. His [superior] is [memberID]=1 (Bob)

Guess I am just having a hard time visualizing how to make the hierarchy grow as people advance.

So (keep up with me here if you can hah) Lets say Bob starts, he is rank 1. Then Bob advances to rank 2, and Frank and Joe both start under him at rank 1. (The hierarchy officially begins). Then Bob advances to rank 3, but Frank and Joe are both still rank 1.

Now there is a gap between Bob and the other 2, but Bob is still "above" them.

So lets say Frank advances to 2, and is now directly above Joe (who is still rank 1), (and now immediately below Bob again, and he also brings in Tom (rank 1) directly under him.

So now it looks like this:

ID----Name----Suerior----Rank
---------------------------------------
1-----Bob-----0----------3
2-----Frank---1----------2
3-----Joe-----2----------1
4-----Tom-----2----------1

or this:

---------Bob---------
---Frank------NULL---
Joe---Tom--NULL--NULL

And so on.

So I guess what I am trying to figure out from here is lets say Joe advances to rank 2, and is placed under Bob in the NULL space under Bob. If Tom then advances, he can no longer be under Bob, because Bob can only have 2 people under him, so Tom has to go to his own instance of a completely different hierarchy.

I guess what I am really trying to figure out is how to create the record for a position that nobody has filled yet, but hypothetically exists under someone. Or something like that. My brain hurts. lol

Thanks for the script! I will go over it and try to visualize how this will work, I won't get to try it until later tonight.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-07-06 : 12:42:53
Part of the confusion is that you seem to be combining 2 different 'structuring' concepts. What is the purpose of the 'Rank' anyway? Normally, a hierarchy would just describe a series of parent/child relationships. Also, why are you limited to only 2 people 'directly under' a person? What are you really trying to model?

If Joe & Tom 'advanced' at the same time... how would you decide who is listed under Bob and who gets put into a different structure?

And btw... in my previous post, I meant John... not Frank. John is Rank 1, but his superior Tom is Rank 3...



Corey

I Has Returned!!
Go to Top of Page

Sintwar
Starting Member

11 Posts
Posted - 2011-07-06 : 14:09:02
It's really more in tune with a military type structure (sorta), where you have ranks, and people report directly to the person above them (or the person above that in the event of a broken link), but because they have ranks, they don't move up the hierarchy just because their superior is removed, or never existed.

Their superior may or may not be replaced by someone else. If not, they may move up into that position when they advance. Otherwise, if there are no open positions for their tank in their current hierarchy, they are simply ejected, and placed in another hierarchy.

The model I have with only 2 people below each person, is simply to limit the size of the overall hierarchy once filled to rank 10, meaning that one 1 person may be a rank 10 in one hierarchy, but he may only have 2 rank 9's under him, and they each may only have 2 ranks 8's under them, and so on.


If Joe & Tom 'advanced' at the same time, it would really depend on who got there first, even if just my milliseconds, which would decide who stays, and who goes.


As far as ranking, I am starting with 1 being the lowest, and 10 being the highest. It's not really a ranking system where #1 is the best.


The way it would work is that someone could advance to level 10, and it would be possible to only have 2 level 1's under them (way down the line), with the possibility of filling in the gaps.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-07-06 : 14:18:13
Maybe you should build the hierarchy to just show the reporting structure... and leave the Rank as an attribute of the Person. 'Empty' spots would be identifiable by a 2+ jump in Rank of a person and their superior.

Corey

I Has Returned!!
Go to Top of Page

Sintwar
Starting Member

11 Posts
Posted - 2011-07-06 : 15:31:08
How do you think I should build the hierarchy though?

Like I said, I imagine I would need to essentially create an entire empty hierarchy for each person who falls out of an existing hierarchy, and doesn't have another hierarchy to fall into with an open position in his rank, or just have a few hundred or thousand of them ready to be filled.

Or, (this is what I am aiming for) is there a more dynamic or organic way to handle it?

I was trying to get away with only using 1 table, but I agree that what you propose is probably the only way to do it.

I just can't seem to wrap my mind around how to "grow" it as the community grows.

I think I have an idea, but it would be tedious, and require quite a lot of calculations. Something I am willing to sort through, I was just hoping someone here might have the simple (and optimal) solution. :)
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-07-06 : 16:12:00
I'm honestly not sure why you think your originaly table design wouldn't suffice. It would require you to do a little calculation to find the 'empty' spots... but it's doable.

The person who 'falls out' would then just get a superiorId of '0'. The superior of the 'fall out' would become the superior of the 'fall out's children. The empty spot would be calculable by the difference in rank.

Maybe I'll through another sample together later.



Corey

I Has Returned!!
Go to Top of Page

Sintwar
Starting Member

11 Posts
Posted - 2011-07-06 : 17:04:58
Well, the underlying problem I have really hit the brick wall on is figuring out how to determine who is above who going up the chain.

A single table would be great if I can just figure a way to link the people who have missing links between them.

I do think the second table would be the best bet though, with a positions table being the glue that keeps them together.

I think I have a plan now though...

Imagining starting from nobody, 1 person starts in at level 1. He advances to rank 2, and a rank 2 position is created for him, as well as 2 blank rank 1 positions under it. When he advances, a rank 3 position is created for him, the rank 2 position he was in now becomes blank (but still there), and it creates another blank rank 2 under him, and 2 blank rank 1 positions under that.

Now, in the system I have in mind, the ranking members will have the option to select someone to fill the positions below them by selecting available members of the appropriate rank from a list, and may only fill 2+ rank positions below them if there is nobody else occupying the position above the position in question.

Only when someone is advancing, and there is a blank position above them (somewhere in the hierarchy) will they actually move up instead of being ejected.

If someone is ejected due to a lack of available positions, 1 of 2 things will happen. Either someone grabs them from the list and places them under them in a blank position in their branch (in another hierarchy), or they begin a new branch, forming a new hierarchy in which case, an entire set of blank positions will be created under them, ready to be filled.

At that point, positions could begin to be filled from the bottom tier, and still have blank (but linked) positions between the highest and lowest ranking members in that branch.

The only other solution I can think of would be to have 10 colums in each members record, listing who all is above you, and which position above you they are. I could work with that data, and it wouldn't be so "physical".

Of course if I do it that way, I will need to update every single member's record all the way down the branch to reflect changing data...

hmmmm....
Go to Top of Page

Sintwar
Starting Member

11 Posts
Posted - 2011-07-06 : 18:34:15
Ahh just read back and realized I did make a mistake in listing the rank/superior order.


Should be more like this

memberID--Name--Superior--Rank
============================================
1---------Bob---0---------5
2---------Frank-1---------4
3---------Joe---1---------4
4---------Tom---2---------3
5---------Josh--2---------3
6---------Karen-4---------2
7---------Kim---4---------2
8---------John--6---------1


Although, the system in mind would allow for the original way as well.
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts
Posted - 2011-07-16 : 11:30:30
Have you considered the Nested Sets Model instead of using an adjacency model to mimic pointer chains? Get a copy of TREES & HIERARCHIES IN SQL for other options.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2011-07-19 : 00:08:57
^ Spam
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site