Personnel DB reporting structure

[jjturner]

Dusting off the rust and mental cobwebs after being away from development for some time . . .

Looking for any pointers or suggestions about how to setup a personnel db that will yield an accurate reporting structure as-of a given date. The 4 levels of reporting, from lowest to highest are thus:

(1)Employee
(2)Manager
(3)Officer
(4)SeniorOfficer

Additionally, would like to associate that reporting structure to particular transaction records (i.e. - given an Employee, derive the reporting hierarchy per transactionID in a table of transactions).

To complicate matters, there are also distinct Processing Units in the department for which a given Officer or combo of Officers is responsible. Factoring that in will be an issue as well.

By the looks of it, it appears likely I'll have to keep a fair number of lookup tables and intermediate tables, and from there a large number of queries to get the results I want.

Any help greatly appreciated!

Regards,
John

 

[FoFa]

I did this in Access no less. We implemented something like that we call an Entity Rollups. Basically (I used a treeview to view and maintain the structure) you setup as many rollup structures as you need, as it pertains to an organization (what ever that maybe). We have a regular reporting structure (employees to supervisors to managers to directors to vice presidents etc. Then we have an operational reporting structure which is different. A Deligation of Authority structure, and some work flow structures. Each one of these entitys (or positions depending on what you want to call them) has an effectivity date and the system looks at the current date, and gives you the results as of that date (ignoring future and history dates). That way you can implement an org change in the future, and it won't take affect until that date is reached. You can also look back and report "point in time" which they use to compare headcount from say a year ago to today, or after an org change to before as an example. You then basically tie an person to that entity, but the entity will remain even if the person changes. So to change a manager, you just have to swape out the person of that entity, and BANG! A new manager for the lower levels. Now they want to move it to SQLserver and implement a WEB reporting piece and manager list (so a manager can see his people) which is in a testing phase and seems to be pretty good also. They also want to implement a Web Service so other application can use it for workflow and such. The current system pulls people from our HR system (not stored in access) via a linked table.
Anyway, just thought I would mention it.

 

[jjturner]

FoFa,
Your DB sounds very similar to what I'm aiming for. Right now I'm conceptually trying to think of something more clean and less cluttered than having a table for each hierarchy level and a bunch of intermediate tables - maybe something like one 'Assignment' table (no matter what level) with the following fields:

Assignee (use EmployeeID from main Personnel table)
PositionAssignment (i.e. - lookup a value of 'Manager' or 'Officer', etc)
AssignmentDate
AssignedTo (use EmployeeID from main Personnel table)

Then from that table, break it into 4 queries (or however many levels of hierarchy there are) to derive a complete reporting structure for each/any employee as-of any given date. By my estimation, this avoids setting up a bunch of 1-to-many table relationships (a route that I've already tried but can really get messy).

May I inquire as to how your general table relationships are setup on your Entity Rollups and perhaps how involved it gets to build the necessary queries off of them?

Thanks,
John

 

[FoFa]

Mine only has 4 tables (because the people information is in another DB). Master_Rollups, which contains a key and a description of the different rollups. Like Org Rollup, Opr Rollup, Delegation of Authority rollup, etc. An entities table keeps basic information about that Entit and an Entity Rollup table (based on how Tree View works), think of it as a Binary Tree structure, and a Entity to Person cross reference table. The structure is maintained in the Entity Rollup table as a whole (for a Master Rollup) using basically a before and after (I call it parent and child) pointer to each prior and next entity.

 

[jjturner]

Please forgive as I've only just yesterday acquainted myself with a sample TreeView db, never having used that ActiveX control before.

I'm having trouble envisioning what fields you are keeping with each table, or how for instance [employee], [effective date(s)], [(relevant associated entity)], etc are setup at the atomic level.

The Master table you describe sounds as if it keeps info such as 'how many levels of hierarchy' exist for a particular Entity and any other relevant Entity attribute.

Maybe if you could give just a field breakdown of one example it would help. Also, what kind of queries are you running off these, or do you keep everything in code derived from the TreeView?

Thanks Again,
John

 

[jjturner]

FoFa,
OK, did some research and investigating into Adjacency Lists, Nested Sets, Materialized Path, and Binary Fractions. Right now I'm debating as to which solution to go with.

I'm curious as to what your table structure is and whether you've run into any drawbacks with your approach.
Are you using Adjacency Lists where fields would be:

• Employee
• ReportsTo

Or Materialized Path, fields being:

• Employee
• EncodedPath (i.e. - 1.1.2.1)

Or Nested Sets, fields being:

• Employee
• LeftBoundary
• RightBoundary

Your response greatly appreciated.

TIA,
John