当用户创建员工时,用户将分配部门、产品、子产品和区域给员工。
用户本身可以访问特定的部门、产品、子产品和区域。
例如,用户A可以访问D1部门、P1产品(区域=亚洲、美洲)、P2产品(区域=亚洲)和P3产品(区域=亚洲、美洲)。
部门是产品的父级。每个部门可以有多个产品。
当我说用户A可以访问产品P1(区域=亚洲、美洲)时,这意味着用户A可以添加产品为P1且区域为亚洲或美洲的员工。
他不能为P1产品或其他任何产品添加员工到其他地区。
假设用户A已经在数据库中添加了500名员工,另一个用户B也添加了500名员工等等。
如何编写高效的查询以获取我可以访问的员工?
请注意,可能会有与我拥有相同访问权限的其他用户添加员工,我也应该能够看到这些员工。
以下是我所拥有的数据库架构。
--------------------------------------------------------
-- 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');
以下是我目前编写的查询,但我不确定这是否是最佳方法。
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
编辑1:
我已经更新了我的问题,并提供了数据库脚本和一些样例数据。
BI_USER_ACCESS
表。您能否提供一些示例数据(INSERT...)和期望的结果。您还可以删除与问题无直接关系的所有列。 - Vincent Malgrat