Line-manager / staff relationship?

[The Stoat]

Hi all,

I'm a bit stuck with a db relationship design.

I need to hold information in a staff table about a person's Line-manager.

My initial idea was to just have the Staff Table and use the PK of a member who is a Line-manager as a FK in the Line-manager field in the same table for their staff.

I then came across a problem.

I need to be able to select all the staff under a Line-manager. For example a head of department may have 3 team leaders who each manage 10 staff. I need to know all of them, not just the 3 team leaders who are directly managed by the HoD but their staff as well.

As it happens there is no rigidgly defined managment structure per se for this organisation which makes things even more difficult.

I assume there is a reasonably standard way of doing this as all organisations have some form of line-manager/staff relationship. Anyone care to enlighten me?


Cheers

TS

 

[M8KWR]

for hat purpose are you using this, in a form or just on a report. I think their are several way you could go with this, if you let me know which way you need to go i'm post a sample database for you to use.

 

[The Stoat]

Hi,

My intention is to limit access to records depending on the relationship between the user and the staff under them. i.e. you will only be able to view/manipulate data for staff in your line. Ideally both forms and reports but certainly forms.

Cheers

TS

 

[The_Doc_Man]

You want a cascading relationship to find everyone in a management hierarchy, from the sound of it... Identify the boss, identify the managers working for that boss, identify the staff working for those managers... a parent, child, grandchild relation. And ALL of them are in the same table.

This sort of thing cries out for what used to be called a topological sort, which is basically an enumeration of a hierarchy. Applies to geneology and a couple of other fields.

First, for principles, you can try Google on Topology Sort or Topological Sort as a general topic.

Second, if I were doing this, I would have a structure like this:

tblEmployee
EmpID, autonumber or other employee unique ID, PK
employee data...

tblTopoSort
EmpID, FK
MgrID, FK
Depth, integer

You have to build a double relationship for this table. You would put the employee table in the relationships diagram TWICE. The second copy will have a "(2)" in its name. Then you have a relationship between EmpID and one of the references and between MgrID and the other reference. (Don't worry - you didn't duplicate the table - just made a new reference to it.)

Now, you need to write some recursive VBA code that does recordset ops based on key searching. Is it the Seek or Find method for that? I forget, but the Help Files will quickly resolve which one. OR you might get away with a DLookup.

The idea is, you create a new employee. You assign the person to a manager. Make an entry in the TopoSort table with the new employee key and the manager key and depth 1. (First-level relationship.) OK, now do a DLookup in the TopoSort for a record with the manager's key as EmpID. Get the MgrID from that record. Make a new entry in TopoSort with the original employee key and the new manager key and depth 2. (Second-level relationship.) Keep this up as far as you need to. If there are 12 levels of management, the last key is depth 12.

Now, you can use the TopoSort table to make your list of everyone who works for the boss's boss. The depth will tell you whether the person is a manager or staff or whatever.

The other way to do this is to not have the multi-level records, only have the first-level entry. Here you would write a RECURSIVE routine to open a recordset and find the person you want. Then run a parameter query seeking everyone who works for that person. For each such person, RECURSIVELY call the same routine to open another recordset looking for the persons who work for the person you called for (who in this case is the person working for the person for whom you started.)

If you take this approach, don't forget to CLOSE what you OPEN. When you enter the recursive routine, you will have to open a recordset. ALWAYS when you return from the recursion, you must CLOSE that same recordset.

Where this might become a problem is that you cannot, under any circumstance, allow yourself to use a Static/Own variable. I.e. a permanent private variable. You cannot use a variable declared in the module header, either. All variables must be declared in the subroutine, i.e. locally - AND they must be private.

The trick then will be to structure the output of the routine to show your hierarchy. I strongly suggest a bit of reading and Googling before you try either approach.

 

[The Stoat]

Hi Doc,

That's certainly comprehensive

Yep you've hit the nail on the head a management hierarchy enumeration.

The Topological sort sounds the most robust way of handling this problem and i think it will make creating an underlying recordsource for the staff form easier than the recursive method.


I've just written out a pseudo routine for populating and querying the data and it seems to work great

Thanks for your help, much appreciated.

TS

 

[Pat Hartman]

You don't really need two tables to handle this relationship as long as it is 1-many. In a matrix structure where an employee reports to multiple managers, you would need the relation table though.

There is a slightly simpler although less flexible solution and that is to use SQL. The problem with the SQL solution is that it isn't recursive so you need to fix some maximum depth to the hierarchy. If you decide that the maximum depth of the hierarchy is 8 levels, you would add the employee table to the query 8 times and use left joins to follow the hierarchial path from child to parent or parent to child depending on the order you want the data returned.

 

[The_Doc_Man]

I thought of that, Pat, and you are absolutely right about it working within limits. I just hate to build in limits when a general solution is available and can be programmed compactly. With a recursive routine, the code size is minimum and the queries are quite simple.

With nested/cascading SQL, I THINK you use more system resources because the lists you generate for each cascade step have to all co-exist. If you are on a big, new machine with more memory than most people ever dream about, that would be OK, but older machines might possibly barf after a few levels of nested query - particularly if more than a couple of those levels are populated.