What is the fastest way of finding the employees user has access to?

Question

I have a employee table which has general employee information. Another User table with user information. Users can create employees.

When users create employee, user assign division, product, sub product and region to employees.

Users themselevs have access to particular division, product, sub product and region.

e.g. User A has access to Division D1, Product P1 (Region= Asia, America) , P2 (Region= Asia) , P3 (Region= Asia, America).

Division is a parent of Product. Each division can have many products.

When I say User A has access to Product P1 (Region= Asia, America), it means user A can add employees with product = P1 and region = Asia or america.

He can not add employee to any other region for product P1 or any other products itself.

If assuming User A has added 500 employees in db, another user B has added 500 another employees and so on.

How do I write an efficient query to get the employees which I have access to?

Please note that It may happen that another user with same access right as I have can add employees and I should be able to see those employees as well.

Below is the db schema I have.

        --------------------------------------------------------
    --  DDL for Table BI_DIVISION
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_DIVISION" 
       (    "DIVISION_ID" NUMBER(*,0) NOT NULL 
        "DIVISION_NAME" VARCHAR2(4000) 
       ) ;

    --------------------------------------------------------
    --  DDL for Table BI_PRODUCT
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_PRODUCT" 
       (    "PRODUCT_ID" NUMBER(*,0) NOT NULL , 
        "PRODUCT_NAME" VARCHAR2(4000), 
        "DIVISION_ID" NUMBER(*,0) 
       ) ;


    --------------------------------------------------------
    --  DDL for Table BI_SUB_PRODUCT
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_SUB_PRODUCT" 
       (    "SUB_PRODUCT_ID" NUMBER(*,0) NOT NULL, 
        "SUB_PRODUCT_NAME" VARCHAR2(4000), 
        "PRODUCT_ID" NUMBER(*,0), 
       ) ;


    --------------------------------------------------------
    --  DDL for Table BI_REGION
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_REGION" 
       (    "REGION_ID" NUMBER(*,0) NOT NULL, 
        "REGION_NAME" VARCHAR2(4000) NOT NULL ENABLE 
       ) ;


    --------------------------------------------------------
    --  DDL for Table BI_EMPLOYEE
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_EMPLOYEE" 
       (    "EMP_ID" NUMBER(*,0) NOT NULL , 
        "DIVISION_ID" NUMBER(*,0), 
        "PRODUCT_ID" NUMBER(*,0), 
        "SUB_PRODUCT_ID" NUMBER(*,0), 
        "REGION_ID" NUMBER(*,0) ,
        "CONFIDENTIAL" VARCHAR2(1) DEFAULT 'Y' 
       );


    --------------------------------------------------------
    --  DDL for Table BI_USER
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_USER" 
       (    "USER_ID" NUMBER(*,0) NOT NULL, 
        "FIRSTNAME" VARCHAR2(4000), 
        "LASTNAME" VARCHAR2(4000) 
       ) ;


    --------------------------------------------------------
    --  DDL for Table BI_USER_ACCESS
    --------------------------------------------------------

      CREATE TABLE "HEADCOUNT_BI"."BI_USER_ACCESS" 
       (    "USER_ACCESS_ID" NUMBER(*,0) NOT NULL, 
        "USER_ID" NUMBER(*,0), 
        "DIVISION_ID" NUMBER(*,0), 
        "PRODUCT_ID" NUMBER(*,0), 
        "SUB_PRODUCT_ID" NUMBER(*,0), 
        "REGION_ID" NUMBER(*,0), 
        "ACCESS_LEVEL" NUMBER(*,0), 
        "CONFIDENTIAL" VARCHAR2(1) DEFAULT 'Y' 
       ) ;

    Insert into BI_DIVISION (DIVISION_ID,DIVISION_NAME) values (1,'DIVISION 1');
    Insert into BI_DIVISION (DIVISION_ID,DIVISION_NAME) values (2,'DIVISION 2');

    Insert into BI_PRODUCT (PRODUCT_NAME,DIVISION_ID,PRODUCT_ID) values ('PRODUCT 1',1,1);
    Insert into BI_PRODUCT (PRODUCT_NAME,DIVISION_ID,PRODUCT_ID) values ('PRODUCT 2',1,2);
    Insert into BI_PRODUCT (PRODUCT_NAME,DIVISION_ID,PRODUCT_ID) values ('PRODUCT 3',2,3);
    Insert into BI_PRODUCT (PRODUCT_NAME,DIVISION_ID,PRODUCT_ID) values ('PRODUCT 4',2,4);

    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (1,'SUB PRODUCT 1', 1);
    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (2,'SUB PRODUCT 2', 1);
    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (3,'SUB PRODUCT 3', 2);
    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (4,'SUB PRODUCT 4', 2);
    Insert into BI_SUB_PRODUCT (SUB_PRODUCT_ID,SUB_PRODUCT_NAME,PRODUCT_ID) values (5,'SUB PRODUCT 5', 3);


    Insert into BI_REGION (REGION_ID,REGION_NAME) values (1,'Americas');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (2,'Asia');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (3,'Germany');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (4,'Japan');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (5,'Pacific');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (6,'ROE');
    Insert into BI_REGION (REGION_ID,REGION_NAME) values (7,'United Kingdom');

    Insert into BI_USER (USER_ID,FIRSTNAME,LASTNAME) values (1,'Adam,'Smith);
    Insert into BI_USER (USER_ID,FIRSTNAME,LASTNAME) values (2,'Steve','Jones');

    -- user with user id = 1 has access to division 1 , product 1 , sub product 1 in regons americas, asia, germany with ACCESS_LEVEL = write access (2) and also access to confidential data 
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,1,1,1,2,'Y');
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,1,1,2,2,'Y');
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,1,1,3,2,'Y');

    -- user with user id = 1 has access to division 1 , product 2 , sub product 4 in regons americas, asia, germany with ACCESS_LEVEL = write access (2) and also NO access to confidential data 
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,2,4,1,2,'N');
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,2,4,2,2,'N');
    Insert into BI_USER_ACCESS (USER_ACCESS_ID,USER_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,ACCESS_LEVEL, CONFIDENTIAL) values (1,1,1,2,4,3,2,'N');

    -- employees in division 1 , product 1, sub product 1 and region americas and not confi.
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (1,'1','1','1',1,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (2,'1','1','1',1,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (3,'1','1','1',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (4,'1','1','1',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (5,'1','1','1',7,'N');

    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (11,'1','1','2',1,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (12,'1','1','2',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (13,'1','1','2',3,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (14,'1','1','2',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (15,'1','1','2',3,'N');

    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (111,'2','3','5',1,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (112,'2','3','5',2,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (113,'2','3','5',3,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (114,'2','3','5',4,'N');
    Insert into BI_EMPLOYEE (EMP_ID,DIVISION_ID,PRODUCT_ID,SUB_PRODUCT_ID,REGION_ID,CONFIDENTIAL) values (115,'2','3','5',5,'N');

and below is the query I have written so far but I am not sure if it is best way of doing it.

    SELECT 
  *
FROM 
  BI_EMPLOYEE e 
JOIN BI_USER_ACCESS uad On uad.DIVISION_ID = e.DIVISION_ID and uad.USER_ID = 137
JOIN BI_USER_ACCESS uap On uap.PRODUCT_ID = e.PRODUCT_ID and uap.USER_ID = 137
JOIN BI_USER_ACCESS uasp On uasp.SUB_PRODUCT_ID = e.SUB_PRODUCT_ID and uasp.USER_ID = 137
JOIN BI_USER_ACCESS uar On uar.REGION_ID = e.REGION_ID  and uar.SUB_PRODUCT_ID = e.SUB_PRODUCT_ID and uar.USER_ID = 137

EDIT 1 :

I have updated my question with the db script and some sample data.

Answer 1

The list of employees that a user can access would be given by a query like this:

SELECT * 
  FROM bi_employee e
 WHERE EXISTS (SELECT NULL
                 FROM bi_user_access ua
                WHERE ua.division_id = e.division_id
                  AND ua.product_id = e.product_id
                  AND ua.sub_product_id = e.sub_product_id
                  AND ua.region_id = e.region_id
                  AND (e.confidential = 'N' OR ua.confidential = 'Y')
                  AND ua.user_id = :user_id);

With your data sample user 1 has access to employees 1 to 4.

Answer 2

Your question specifically asks what is the fastest way to get the list of employees accessible by a user. So I'll answer that.

I used to work on a similar system a few years ago where it was critical we could evaluate this information extremely quickly. The principle was the same, but we had many more criteria (division, product, region, country, city, unit, department, etc.).

If performance is really critical, it is worth materialising the results of the query into a table e.g. ACL_CACHE(USER_ID, EMP_ID)

The query to get employees accessible by a user then becomes trivial:

SELECT EMP_ID
FROM ACL_CACHE
WHERE USER_ID = ####

You can also join the ACL_CACHE table into other queries if you want to limit the results a user can see based on their level of access.

This works extremely well, and brings substantial benefits when working with a large number of employees and/or users; we were typically working with ~500,000 records.

The downside is obviously that you need to keep the ACL_CACHE table up-to-date. This means that some other transactions can become a little bit slower. For example, when adding a new employee, you also need to add records to the ACL_CACHEtable for all users that can see the new employee.

In our experience, the extra delay on these kind of transactions isn't noticeable for the user and is well worth the sacrifice to have all read-only transactions work an order of magnitude faster.

Alternatively, you can update the ACL_CACHE table as a nightly job in one mass update, if you can put up with the data being up to 24 hours "old". Since your tables are called "HEADCOUNT_BI" I'm guessing it may be acceptable if your headcount reports are always accurate up to yesterday evening.