February 1, 2011 at 8:29 am
Adam Machanic (1/31/2011)
....Next, a unique constraint on the employee column, so that the same employee can't appear in the hierarchy twice under different managers....
Thereby, of course, hangs a fairly fundamental problem. The technical logic is fine, but in practice, why shouldn't an employee have two managers? I'll admit it's not that usual, but if a managerial position is a job-share, you've got two part time employees who're both legitimate managers to the whole of the subordinate team. It might be difficult to map hierarchally, but we can't force a change on the business process just because our software can't cope with the business's needs.
Semper in excretia, suus solum profundum variat
February 1, 2011 at 8:36 am
majorbloodnock (2/1/2011)
The technical logic is fine, but in practice, why shouldn't an employee have two managers?
Having actually had two managers (and then, later, three) at one point in my career, I can tell you firsthand that it's a nightmare.
Manager A: "Drop everything and fix widget #123."
[later]
Manager B: "Why haven't you finished fixing widget #456?"
Me: "Manager A told me to stop working on that!"
Manager B: "Forget what he said! Work on #456!"
[later]
Manager A: "Why the **** haven't you fixed #123?!?!"
... etc. Oh, the two managers should have talked to one another and synchronized? Sure, they should have, but human nature works in different ways and people tend to disagree, especially when they're put into positions of what is supposed to be equal power. I don't, personally, believe that any form of matrix management can work in any organization where something is actually supposed to get accomplished.
But, YMMV. And apologies for the aside to the conversation 😀
--
Adam Machanic
whoisactive
February 1, 2011 at 10:42 am
Adam Machanic (2/1/2011)
majorbloodnock (2/1/2011)
The technical logic is fine, but in practice, why shouldn't an employee have two managers?Having actually had two managers (and then, later, three) at one point in my career, I can tell you firsthand that it's a nightmare.
Manager A: "Drop everything and fix widget #123."
[later]
Manager B: "Why haven't you finished fixing widget #456?"
Me: "Manager A told me to stop working on that!"
Manager B: "Forget what he said! Work on #456!"
[later]
Manager A: "Why the **** haven't you fixed #123?!?!"
... etc. Oh, the two managers should have talked to one another and synchronized? Sure, they should have, but human nature works in different ways and people tend to disagree, especially when they're put into positions of what is supposed to be equal power. I don't, personally, believe that any form of matrix management can work in any organization where something is actually supposed to get accomplished.
But, YMMV. And apologies for the aside to the conversation 😀
To be frank, I've seen that rather more from managers who can't let go. The ones who have managers reporting to them, but who still insist on micromanaging the whole team. Not pretty....
And no problem with wandering off at a tangent; I'm regularly guilty of doing it to other people's threads.
Semper in excretia, suus solum profundum variat
February 1, 2011 at 11:07 am
I chuckled when I saw the title of this article as I was working on a recursive CTE just last Friday and ran into circular references. I laughed out loud when I started to read the article as I, too, was working with Active Directory group memberships as well. FWIW, I came up with essentially the same techniques for dealing with it. I also plan to bring the circular relationships to the attention of our AD administrators and see if we can remove them somehow.
Thanks for the timely article.
February 2, 2011 at 5:25 am
Well, now you're getting my poor old brain smoking. I had to think long and hard before realising the problem here.
I agree that in a true tree structure each node except the root will have one and only one parent. However, there are in practice many perfectly acceptable hierarchal situations where relationships mesh together so that a node can have more than one parent, and that's where I think this trigger would have an issue. However, I have to admit I'm by no means certain I haven't missed something in my understanding here.
Semper in excretia, suus solum profundum variat
March 18, 2012 at 10:01 am
For those of you who followed this thread, did anyone use this method to handle foreign keys with circular references?
I adapted a couple of the posted examples, and against one of my databases which i know has circular references, I'm thinking this is not really going to give results in a hierarchy correctly; it seems that the hierarchy is more of the count of FK levels between the objects, and not necessarily the hiarchy order.
i was fiddling around with this trying to get all objects in a hierarchy, but
It seems to me to be pretty inefficient; 20 plus minutes on a machine with no resources in use at all, along with 60,000 rows;
;WITH ChildrenAndParents
AS
(
SELECT
parent_object_id AS object_id,
convert(varchar(255),OBJECT_NAME(parent_object_id)) AS child,
referenced_object_id AS referenced_major_id,
convert(varchar(255),OBJECT_NAME(referenced_object_id)) AS parent
FROM sys.foreign_keys
)
,GroupMembers (Child, ParentGroup, Level, hierarchypath)
AS
(-- Anchor member definition
SELECTg.child,
g.parent,
0 AS Level,
convert(varchar(max), g.child + '->' + g.parent) AS hierarchypath
FROM ChildrenAndParents AS g
WHERE child not in (select parent from ChildrenAndParents)
UNION ALL
-- Recursive member definition
SELECTg.child,
g.parent,
Level + 1,
hierarchypath + '->' + g.parent -- Add '-->...' to end when recursion found
+ Case When gm.hierarchypath like '%->'+g.parent+'->%' Then '-->...'
Else''
End
FROM ChildrenAndParents as g
INNER JOIN GroupMembers AS gm
ON gm.parentgroup = g.child
--Exclude if the hierarchypath text contains a recursion
where gm.hierarchypath not like '%->...'
)
select left(hierarchypath, charindex('->', hierarchypath) - 1) as child, parentgroup, level, hierarchypath
from groupmembers
option(maxrecursion 20);
Lowell
May 9, 2020 at 2:54 am
Thank you so much! This is very helpful!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply