June 13, 2007 at 8:37 am
I have a security app that is like this: a Facility has many Apps and those Apps may have any combination Modules, Roles, or Privileges.
Problem Query: Put together a query(s) that will delete all records in all tables related to a single Facility (FacilityID). I am having the most trouble identifying the records in the ModulesRolesPrivileges and ModulesRolesPrivilegesHierarchy tables.
CREATE TABLE Applications
(Appl_ID int IDENTITY,
FacilityID char(4) NOT NULL,
ApplName char(200) NOT NULL,
CONSTRAINT PK_Applications PRIMARY KEY CLUSTERED (Appl_ID))
-- No uniqueness among MRP_Name
CREATE TABLE ModulesRolesPrivileges
(MRP_ID int IDENTITY,
MRP_Name char(150) NOT NULL,
MRP_Type int NOT NULL, -- 1=Role 2=Module 3=Privilege
CONSTRAINT PK_ModulesRolesPrivileges PRIMARY KEY CLUSTERED (MRP_ID))
CREATE TABLE ApplicationsModulesRolesPrivileges -- Intersection table between Applications and ModulesRolesPrivileges
(AMRP_ID int IDENTITY,
Appl_ID int NOT NULL,
MRP_ID int NOT NULL,
CONSTRAINT PK_ApplicationsModulesRolesPrivileges PRIMARY KEY CLUSTERED (AMRP_ID),
CONSTRAINT R_1 FOREIGN KEY (Appl_ID) REFERENCES Applications (Appl_ID),
CONSTRAINT R_2 FOREIGN KEY (MRP_ID) REFERENCES ModulesRolesPrivileges (MRP_ID))
CREATE TABLE ModulesRolesPrivilegesHierarchy -- Hierarchy of Modules/Roles, Roles to Privileges
(MRPH_ID int IDENTITY,
Parent_AMRP_ID int NOT NULL,
Child_AMRP_ID int NOT NULL,
CONSTRAINT PK_ModulesRolesPrivilegesHierarchy PRIMARY KEY CLUSTERED (MRPH_ID),
CONSTRAINT R_3 FOREIGN KEY (Parent_AMRP_ID) REFERENCES ModulesRolesPrivileges (MRP_ID),
CONSTRAINT R_4 FOREIGN KEY (Child_AMRP_ID) REFERENCES ModulesRolesPrivileges (MRP_ID))
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Host Financials') -- 1
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'PeopleSoft') -- 2
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Acct Recv') -- 3
INSERT Applications (FacilityID, ApplName) VALUES ('0914', 'Acct Recv') -- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Budget', 2) -- 1
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('GL', 2) -- 2
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1) -- 3
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Admin', 1) -- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Query', 3) -- 5
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Update', 3) -- 6
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3) -- 7
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1) -- 8
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3) -- 9
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 1) -- 1
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 2) -- 2
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (2, 3) -- 3
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (4, 8) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID) VALUES (1, 3) -- 1
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID) VALUES (3, 5) -- 2
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID) VALUES (3, 6) -- 3
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID) VALUES (3, 7) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID) VALUES (2, 4) -- 5
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID) VALUES (4, 5) -- 6
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID) VALUES (2, 3) -- 7
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID) VALUES (8, 9) -- 8
June 13, 2007 at 12:11 pm
Can an Appl_ID (and name for that matter) belong to more than one facility? Can an MRP_ID belong to more than one Appl_ID?
Edit: Ignore the first question, as you've answered it.
June 14, 2007 at 8:45 am
No, everything is completely unique. I didn't design this table structure and I loathe working with it. There can be n iterations of an Application named 'PeopleSoft' but each one is unique to a set of MRP_ID values. And no, an MRP_ID cannot belong to more than one Appl_ID.
Thanks for your interest in this.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply