Organisation Structure

[Naoise]

I have three tables, I am looking to get a dendritic structure of organisation roles and who they report to.

A sample might be

tblOrganisationRoles
===========================
OrganisationRoleID int | OrganisationID int | Role varchar
1 1000 CEO
2 1000 CTO
3 1000 Tech
4 1000 General

tblOrganisationRoleOrgChart
===========================
OrganisationRoleID int | ReportsToID int
1 1
2 1
3 2
4 2

tblOrganisationUsers
===========================
OrganisationUserID int | OrganisationID int | Firstname varchar | Lastname varchar
1 1 Bill Gates
2 1 Tom Crack
3 1 Paul Curran
4 1 Brian Walsh
5 1 John Doe

This structure would know to have CEO as the root because of the record in tblOrganisationRoleOrgChart
as CEO reports to himself This will always be the way, after this it is a case of finding out who reports
to CEO and building it up. If 2 roles report to one role as is the case with Tech and General who
report to CTO then then we sort alphabetically, so General appears first then Tech. If I were to sort
the data above I would be trying to get :

Role Name ReportsToID
CEO Bill Gates 1
CTO Tom Crack 1
General John Doe 2
Tech Brian Walsh 2
Tech John Doe 2

So the structure is based on Roles and then just sorted alphabetically by Rolename, Name

Any help/pointers appreciated

 

[Naoise]

I think I'd like to approach this from a different angle using a recursive function to calculate the depth of each Role as it is added. So a new field called depth will be added to the table tblOrganisationRoleOrgChart.

Given these two tables

tblOrganisationRoles
===========================
OrganisationRoleID int | OrganisationID int | Role varchar
1 1000 CEO
2 1000 CTO
3 1000 Tech
4 1000 General

tblOrganisationRoleOrgChart
===========================
OrganisationRoleID int | ReportsToID int | Depth
1 1
2 1
3 2
4 2

How would you write a recursive function to calculate the depth of a given Role as it was added? Any help appreciated

 

[r937]

which dbms is this? you should be asking in the appropriate forum for your particular dbms, not the ANSI SQL forum

you're not likely to get an ANSI SQL solution, and if you do, there's a good chance it won't work for your particular dbms

r937.com | rudy.ca