SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Everybody Reports to Somebody


Everybody Reports to Somebody

Author
Message
Craig Hatley
Craig Hatley
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 6
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/cHatley/3001.asp
Mike C
Mike C
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4633 Visits: 1172

Ahhh, ye olde adjacency list model. You might get better efficiency from a set-based solution, like the nested sets model or one of its variants. Here's an Q&D sample.

CREATE TABLE #Employees (ID INT PRIMARY KEY,
LastName VARCHAR(30),
FirstName VARCHAR(30),
LeftID INT,
RightID INT)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (1, 'Smith', 'John', 1, 24)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (2, 'Johnson', 'Bob', 2, 15)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (4, 'Fields', 'Sue', 3, 4)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (3, 'Burton', 'Debra', 16, 23)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (5, 'Jacobs', 'Todd', 11, 14)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (6, 'Sanders', 'Mary', 5, 10)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (10, 'Sims', 'Tom', 17, 18)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (11, 'Wright', 'Larry', 19, 20)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (12, 'Morgan', 'Betty', 21, 22)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (7, 'Teeter', 'Sandy', 12, 13)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (8, 'Morris', 'Megan', 6, 7)

INSERT INTO #Employees (ID, LastName, FirstName, LeftID, RightID)
VALUES (9, 'Binks', 'Randy', 8, 9)
GO

CREATE PROCEDURE dbo.GetOrgChart (@startID INT)
AS
BEGIN
SELECT e1.ID, e1.LastName, e1.FirstName, e1.LeftID, e1.RightID, COUNT(e2.ID) AS Level
FROM #Employees e1
INNER JOIN #Employees e2
ON e1.LeftID BETWEEN e2.LeftID AND e2.RightID
WHERE e1.LeftID BETWEEN (SELECT LeftID FROM #Employees WHERE ID = @startid)
AND (SELECT RightID FROM #Employees WHERE ID = @startid)
GROUP BY e1.ID, e1.LastName, e1.FirstName, e1.LeftID, e1.RightID
ORDER BY e1.LeftID
END
GO

EXEC dbo.GetOrgChart 1


Paul Nicklin
Paul Nicklin
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 20

Oracle has "Connect by prior"

select * from person connect by prior id=supervisor_id

.. it makes traversing hierarchies trivial.

I was hoping it would appear in SS2005...


David le Quesne
David le Quesne
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1331 Visits: 32

Hi Mike,

apologies for this, but i am interested in your right-brain set based solution, but I am feeling a bit left-brain this Monday morning, probably lack of coffee, so please could you set me right about what left-id and right-id represent because at the moment I'm feeling a bit left-out.

Thanks

David



If it ain't broke, don't fix it...
CraigIW
CraigIW
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 235
Quite liked that one, though as surprised as you at the efficiency. Will give it a bash sometime.
Timothy-313907
Timothy-313907
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 721
Mike's example is actually a nested set model, not an adjecent list model per the article. Basically instead of having a "boss" column in your employee table that's the same as an employee, each employee has containment information. You use two columns which represent the range of containment, a set, an employee has. Joe Celko explains it better, http://www.developersdex.com/gurus/articles/112.asp.
David le Quesne
David le Quesne
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1331 Visits: 32

Hi Tim,

Thanks for that link to Joe Celko's article, I had not come across this solution before. I can see that it is easier to model an org tree in SQL using set theory. In practice, does the data in the set correspond to an organisation's departmental structure, rather than individual relationships, otherwise you could get real problems if an employee switches managers and correspondingly needed to switch containments?

I like the idea that because the containment encapsulates all levels of the hierarchy, there is no break in the management chain if an employee in the middle-tier leaves, the model is effectively self-healing.

Presumably by creating overlapping sets you could also create organisation structures where individuals have multiple lines of management (many DBAs may be familiar with this )

David



If it ain't broke, don't fix it...
Craig Hatley
Craig Hatley
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 6

Hi Mike,

Although your solution is a viable option (and we considered it), we chose to identify each employee's manager as a single field on each record because we wanted the ability to easily update this information to accomodate the incredible growth of our organization. With the method we are using, we basically complete this single piece of information (manager's user ID) for each new employee during the account setup process and we are good to go.


David le Quesne
David le Quesne
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1331 Visits: 32

Hi Mike,

found a further article at http://www.intelligententerprise.com/001020/celko1_1.jhtml

It appears that if your organisation structure fequently changes, you would still need an adjacency list to calculate the nested sets though? Joe provides the code to convert one to the other. I guess the practical application of this solution would be to write a trigger which recalculates the nested sets whenever the organisation structure is changed (i.e. you change an employee's manager id), storing the left and right values you calculate and using them as an aid to produce faster organisation structure reports.

If someone left the organisation, you would leave the existing calculated left and right values in place to provide a management hierarchy until a new manager was appointed.

PS: Have had several coffees now

David



If it ain't broke, don't fix it...
Craig Hatley
Craig Hatley
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 6

Hi,

I have received a private question asking about how to deal with employees that may report to multiple bosses and I wanted to provide my thoughts on the subject. I think the best approach to address this issue would be to move the 'Manager User ID' field to a separate table containing at least the 'User ID' and 'Manager User ID' columns which could be joined to the 'Employee' table via the 'User ID' column. You could then modify the stored procedure to include the new table via a join such as (Changes Highlighted In Yellow) . . .

CREATE PROCEDURE select_subordinates_by_manager
@UserID int
As
Set NoCount On

Declare @UserCount int
Set @UserCount = 1

Create Table #In (UserID int)
Create Table #Out (UserID int)
Create Table #Result (UserID int)

Insert Into #In (UserID) Values (@UserID)
Insert Into #Result (UserID) Values (@UserID)

While @UserCount > 0
Begin
Insert Into #Out (UserID)
Select Distinct UserID From Test..Employee as e
Join Test..ManagedBy as m on m.UserID = e.UserID
Where m.ManagerUserID In (Select UserID From #In)
Select @UserCount = (Select Count(UserID) From #Out)
If @UserCount > 0
Begin
Insert Into #Result (UserID)
Select UserID From #Out
End
Delete From #In
Insert Into #In
Select UserID From #Out
Delete From #Out
End

Set NoCount Off

Select UserID From #Result

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search