R
rich
Hello All,
My apologies for the length of this posting but I need to clearly convey the
information.
I am struggling with a database design issue.
Here’s a brief overview of the project and problem.
This is an Access application that will track an employee’s progress through
a process similar to an employee hiring. This process is basically getting
user accounts created, email account, and access to various applications.
In addition, and this is the problem part, I need to track an employee’s
access to the individual applications that he/she should have access to based
on their role (or job function).
So typically, here is the entire process for a new employee:
1. Request form is submitted for a computer account. Form includes the
role (job title/function) for this role. This results in the employee record
being created.
2. Computer account info is received by the employee. Employee activates
the computer account.
3. Email account is requested.
4. Employee is notified that email account has been created.
5. Requests are made to grant access to multiple applications (based on
role/job function). The applications needed are determined by the employee
role.
6. Access to individual applications are granted. This is what needs to be
tracked and reported on.
7. User verifies access to each required applications.
Several tables are involved in the problem at hand.
(LKTBL = Lookup table. ) Generally very static.
tblEmplMaster: This is basically the employee master record. PK is the
unique employee ID number.
lktblApplMaster: This is a lookup table with all the applications. There
are about 50 applications in total.
lktblRoleMaster: This is a lookup table with all employee roles. There are
11 roles.
lktblRoleApplMapping: This table shows which applications as assigned to
each role. (1-to-many: 1 role has many apps.)
Functional Requirements:
1. Each employee must be assigned to a unique role. This happens when the
employee record is initially created.
2. Each role needs to be associated with the applications for that role.
This is handled in the "lktblRoleApplMapping" table. This table has 1 row
for each role/application combination. So:
row_1 = Role_1, Appl_1
row_2 = Role_1, Appl_2
row_3 = Role_1, Appl_3........
row_4 = Role_2, Appl_1
row_5 = Role_2, Appl_2........
row_6 = Role_3, Appl_1
row_7 = Role_3, Appl_2........
and so on............
3. On a single form, have checkboxes and text boxes to indicate access has
been granted and the date granted. I see this form as having the
EmplIDNumber, name, Role, and a list(appl name, Y/N checkbox for access
granted, txtbox for the date access was granted)
Problem:
1. Thus far, I cannot accomplish #3 above. I need to ensure my table
design is adequate, including keys and relationships.
2. I don't have the form to accomplish #3 above. When info on this form is
submitted, I believe there should be 1 new row added for each
role/application combo (as illustrated in #2 above).
Current table designs:
EMPLOYEE MASTER TABLE
EmplIDNumber: PK
EmplName
Other descriptive fields
LKTBLAPPLMASTER
ApplIDNumber: PK
Application Name
Other fields...
LKTBLROLEMASTER
RoleIDNumber: PK
RoleName
Other role descriptive columns
LKTBLROLEAPPLMAPPING: PK currently undefined. Also, not sure if anything
should be pre-populated.
EmplIDNumber: Column is currently empty and may need to be
removed from this table.
RoleIDNumber: FK
Other desriptive fields
I hope I've provided sufficient detail for this problem to be understood
clearly. If not, just let me know.
TIA,
Rich
My apologies for the length of this posting but I need to clearly convey the
information.
I am struggling with a database design issue.
Here’s a brief overview of the project and problem.
This is an Access application that will track an employee’s progress through
a process similar to an employee hiring. This process is basically getting
user accounts created, email account, and access to various applications.
In addition, and this is the problem part, I need to track an employee’s
access to the individual applications that he/she should have access to based
on their role (or job function).
So typically, here is the entire process for a new employee:
1. Request form is submitted for a computer account. Form includes the
role (job title/function) for this role. This results in the employee record
being created.
2. Computer account info is received by the employee. Employee activates
the computer account.
3. Email account is requested.
4. Employee is notified that email account has been created.
5. Requests are made to grant access to multiple applications (based on
role/job function). The applications needed are determined by the employee
role.
6. Access to individual applications are granted. This is what needs to be
tracked and reported on.
7. User verifies access to each required applications.
Several tables are involved in the problem at hand.
(LKTBL = Lookup table. ) Generally very static.
tblEmplMaster: This is basically the employee master record. PK is the
unique employee ID number.
lktblApplMaster: This is a lookup table with all the applications. There
are about 50 applications in total.
lktblRoleMaster: This is a lookup table with all employee roles. There are
11 roles.
lktblRoleApplMapping: This table shows which applications as assigned to
each role. (1-to-many: 1 role has many apps.)
Functional Requirements:
1. Each employee must be assigned to a unique role. This happens when the
employee record is initially created.
2. Each role needs to be associated with the applications for that role.
This is handled in the "lktblRoleApplMapping" table. This table has 1 row
for each role/application combination. So:
row_1 = Role_1, Appl_1
row_2 = Role_1, Appl_2
row_3 = Role_1, Appl_3........
row_4 = Role_2, Appl_1
row_5 = Role_2, Appl_2........
row_6 = Role_3, Appl_1
row_7 = Role_3, Appl_2........
and so on............
3. On a single form, have checkboxes and text boxes to indicate access has
been granted and the date granted. I see this form as having the
EmplIDNumber, name, Role, and a list(appl name, Y/N checkbox for access
granted, txtbox for the date access was granted)
Problem:
1. Thus far, I cannot accomplish #3 above. I need to ensure my table
design is adequate, including keys and relationships.
2. I don't have the form to accomplish #3 above. When info on this form is
submitted, I believe there should be 1 new row added for each
role/application combo (as illustrated in #2 above).
Current table designs:
EMPLOYEE MASTER TABLE
EmplIDNumber: PK
EmplName
Other descriptive fields
LKTBLAPPLMASTER
ApplIDNumber: PK
Application Name
Other fields...
LKTBLROLEMASTER
RoleIDNumber: PK
RoleName
Other role descriptive columns
LKTBLROLEAPPLMAPPING: PK currently undefined. Also, not sure if anything
should be pre-populated.
EmplIDNumber: Column is currently empty and may need to be
removed from this table.
RoleIDNumber: FK
Other desriptive fields
I hope I've provided sufficient detail for this problem to be understood
clearly. If not, just let me know.
TIA,
Rich