Table Relationships

[thesavo]

I need your advice on my ER Diagram. My data looks like this. We are tracking our projects in an application that is not well managed. I have started working on an external MS access db to track workflow of these projects in a table called ‘tblProject’ Daily tasks that I wish to accomplish with access db.

1. Track users – When new users are created and removed from the application.
   • When the Access Request Form is
     • Received into our office.
     • Signed by me
     • Signed by an approver
     • Created in active directory
     • Created account for this
     • User is notified of new account
2. Projects
   • Projects get monthly status updates,
   • Currently, this is placed on a form that is printed with the name of the 4 key people on the project and some dates.
     • Eventually, I would like this save, right now I am not.

A project will have only 1 of the following. All of which can be on multiple projects. All of which should be, but not always have accounts in the application:

• Engineers
• Supervisors
• Managers
• External Managers

I need to be able to address the users in some of my reports as they are users in the system. Should I put everyone in one ‘tblUsers’ table? That doesn’t seem right either. Am I going in the right direction with this? Thanks for any comments you can provide.

 

[spikepl]

So you have Staff, and each staff member has some position (one only?) and then you have Users, that each can have one (or more ?) roles in the workflow or not, and then you have Projects, where each has 4 predefined positions each filled (or not) by a staffer of corresponding position, that may or may not be a User. Does this help?

 

[thesavo]

Thank you for the fast response.

So you have Staff, and each staff member has some position (one only?) and then you have Users, that each can have one (or more ?) roles in the workflow or not, and then you have Projects, where each has 4 predefined positions each filled (or not) by a staffer of corresponding position, that may or may not be a User. Does this help?

Users/staff: Yes, mostly. The staff may or may not be a user. A staff member will fill only one role, ever. But can fulfill that role on multiple projects. Staff that don't have accounts currently, in the future, have and account added or revoked. But will still be a staff member in that role on their projects.

Projects: (as you said) Where each has 4 predefined positions each filled (or not) by a staffer of corresponding position, that may or may not be a User

 

[spikepl]

So to me it seems you might have

tblStaff
--------
StaffID
StaffPositionID
Personalia
RoleID (here we assume that one person can only have one role in all the workflows)
SystemUser ( a Boolean-field)

tblStaffPositions
----------------
StaffPositionID
StaffPositionName

tblRoles
--------
RoleID
RoleName (reviewer, approver ....)


tblProjects
-----------
EngineerPositionID -> pointing at one of the Engineers in the staff table
ManagerPositionID -> ....
whateverID-> ....

 

[thesavo]

You had me up-to tblProjects. Does this involve creating a relationship between tble projects and tblstaff or tblPositions And I was wrong eariler.

Projects: (as you said) Where each has 4 predefined positions each filled (or not) by a staffer of corresponding position, that may or may not be a User

Where each has 4 predefined ROLES each filled (or not) by a staffer of corresponding ROLE, that may or may not be a User