Hierarch

[ilanray]

Hi
I would like to create Hierarch in my employee table. I created a new field with name "teamLeader" which is the manager of the employee
I would like to have in a new table all Hierarchy sepetate by comma for example
A is the Boss, b is the manager, c is teamleader which has few employee . The new field for b should look like(A,B)
For the employee it should be (A,B,C) etc
Anyone have an idea

Thanks

 

[MajP]

I would recommend doing a self reference.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

The example includes an employee tree

 

[The_Doc_Man]

The way I did this is with a Team table and a Personnel table.

Every team had its own PK (as well as a name). Every employee had his/her own PK (as well as names). Each Team entry had a slot for "team leader" which pointed to one of the members of the Personnel list. Each Personnel entry had a slot for "team" which pointed to one of the Team entries.

That lasted until things got so hectic that we had to allow some overlap. The second iteration became a junction table of "Team ID, Person ID" and there was a Yes/No flag that was "Team Leader" - So each team leader entry was marked as such and other team members were just <Team ID, Person ID, No>.

 

[CJ_London]

suggest you also need a from date and perhaps a to date to cover situations where an employee moves to a new role or leaves the company

 

[Pat Hartman]

A self-referencing table as suggested by @MajP is the typical solution for a standard organizational chart. However, if you have some new-fangled matrix organization, you need to use a many-many schema. Both sides of the junction can be the Employee table though.

If teams/departments exist without a manager, then you need a team/department table as well so the relationship doesn't break if the manager leaves the company or is reassigned.

 

[MajP]

Self Referencing. Data storage and treeview display. Treeview demos drag and drop. So you can move a manager under a new boss and the whole branch moves because you simply changed the managers supervisor ID.

 

[Pat Hartman]

I'm not arguing with your advice Majp, just pointing out how to handle some management organizations I have had to deal with over the years that didn't quite fit the self referencing model. Sometimes a team is left temporarily without a manager so to handle that, you can create a dummy employee and move the team to the dummy until the new manager is hired/appointed.

 

[MajP]

just pointing out how to handle some management organizations I have had to deal with over the years that didn't quite fit the self referencing model.

Fully concur. I could not model my job in a pure hierarchy. I would have three team leads across two companies. However the OP only a presented a single pure hierarchy for a new employee, ABC, not a matrix (ABC and AEG and FBC and...)

 

[Pat Hartman]

I agree but sometimes at the beginning, the developer isn't even aware that there are potential kinks in what he thinks is straightforward.