Company hierarchy data access level implementation in database

Question

I want to create the given company structure in the database for an application and want to traverse the employee details based on the structure. The problem in given below.

When a employee login into the system, he should be able to see the employee's details who are working under his level. Say for example if "Executive B" login into the system, he should be able to see the details of the staff A, staff B, staff C and staff C's sub staffs.

At the same time, Staff C is reporting to Section Head C where the Section head C only can view Staff C's details, not his sub staffs. That means, when Section Head C login, he can view his sub employees and well as Staff C.

But Section Head D can view staff C's details and his sub staffs details as he have the full access to the branch starts with Staff C. That means he can view his sub employees and also staff C and his sub employees.

Can any one help me to implement this structure and the access levels in the database and how to query them in the efficient way?

Answer 1

A common approach to solving this kind of hierarchy traversal problem is to use a technique called visitation numbers, which I describe in detail in my answer to this question. Using visitation numbers, you can easily find the list of all nodes that are below any given node anywhere in the hierarchy.

Note that you would still record each employee's direct reporting superior using an involuted foreign key on the employee table.

For your case, you also have dotted-line reporting (Staff C to Section Head C) outside of the regular reporting hierarchy. This means that you will need two approaches. The first is to use visitation numbers for regular reporting, where managers can see all of their direct and indirect reports, and then something else for dotted-line reporting.

It seems like you have two kinds of rules for dotted line reporting. Some dotted line supervisors can see sub-staff and others can only see their dotted line direct-reports. Since people can have more than one dotted line supervisor, you need to add an intersection table to record these dotted line relationships. This intersection table can also include a flag attribute that indicates whether or not the dotted line supervisor is able to see the only the immediate dotted line subordinate or that person and all of their subordinates as well.

Either way, the dotted line relationships are recorded directly between an employee and their supervisor and the regular reporting relationships (which may be indirect) are managed by visitation numbers.

Answer 2

You need a couple of self-join tables on employee. One represents the supervisor to employee relation. The second represents the peer relationship between employees.

Here is the SQL for PostgreSQL

drop schema if exists stackoverflow cascade;

create schema stackoverflow;

set search_path to stackoverflow, public;

create table employee

(

id serial not null unique,

name text not null unique,

title text not null,

primary key ( id )

);

create table reports

(

supervisorid integer not null references employee ( id )  on delete cascade ,

subordinateid integer not null references employee ( id )
       check ( supervisorid !=  subordinateid ),

unique ( supervisorid, subordinateid ),

unique( subordinateid )

);

create table peer

(

supervisorid integer not null references employee ( id )  on delete cascade ,

peerid integer not null references employee ( id )

       check ( supervisorid != peerid ),

unique ( supervisorid, peerid )

);

create or replace view directreports as

 select  supervisor.id  as  "supervisor id", 

        supervisor.name as  "supervisor name",  

  reporting.id as "employee id", reporting.name as  "employee name"

from 

    employee  supervisor, employee reporting , reports

where 

supervisor.id = reports.supervisorid

and reporting.id = reports.subordinateid;






 create or  replace view peerreports as

select * from directreports, peer, employee

   where   

     employee.id = peer.peerid 

       and peer.supervisorid = directreports."supervisor id";

insert into employee (name, title)

values ( 'c head', 'c head'), 
       ( 'd head', 'd head'), 
       ('c emp1', 'c emp1' ) , 
       ('c emp2', 'c emp2' ) ;




insert  into reports 

select employee.id as "supervisorid",
        reportsto.id as "subordinateid" 

   from employee,  employee reportsto  

    where employee.name = 'c head'  

   and reportsto.name in ('c emp1',  'c emp2' )

   and  reportsto.name !=  employee.name ;




 insert into peer    

  select employee.id as "supervisorid",  
          peerto.id as "peer.peerid" 

   from employee, employee peerto

      where employee.name = 'c head' and peerto.name = 'd head' 

       and employee.id != peerto.id;

Here are the typical queries

select * from employee;

select * from reports;

select * from directreports;

select * from peer;

select * from peerreports, employee where employee.name= 'd head';