Project Audit database

[accessbro]

I work in a project management office. I am trying to create a database that will hold project audit information. There are four program managers responsible for overseeing their respective areas.



1. Hank - Software Development Projects
2. Walt - Infrastructure Projects
3. Rick - Information Security Projects
4. Carl - Internal Projects

Each area has its own checklist of required actions that need to be completed. Every time a project is completed, the auditors use an Excel workbook to check off if the check list item has been completed. They're using a separate workbook for each manager and separating projects on different worksheets. I am trying to create something to make that's more scalable if more managers/areas are added or the check list items change. I want to be able to save the audit results for all projects past, present and future.

I am not sure if this is going in the right direction or if I'm missing anything. Here is my table design:

1. tblProject
   
   • ProjectID
   • ProjectName
   • StartDate
   • EndDate
   
   
2. tblChecklist
   
   • ChecklistID
   • ChecklistItemName
   
   
3. tblManager
   
   • ManagerID
   • ManagerName
   
   
4. tblArea
   
   • AreaID
   • AreaName
   
   
5. tblProject_Audit_Results
   
   • ProjectAuditID
   • fkProjectID
   • fkChecklistID
   • fkAreaID
   • Completed (True/False)
   
   

[rpeare]

1. Hank - Software Development Projects
2. Walt - Infrastructure Projects
3. Rick - Information Security Projects
4. Carl - Internal Projects

Each manager represents a different area
Does each Area have a specific checklist or does the checklist change from project to project?
Do any of the checklist items overlap areas? (can the same check be performed by multiple areas)

Can a project affect multiple areas?

You want your structure to reflect the reality of how you do business, if you perform the same checks for an area for every project (in other words your checklist is always the same for a given area) I would have a junction table that defines which checklist item goes with which area, then when you create a new project you can define which areas are related to the project. This could help you avoid a lot of unnecessary data entry (enter the area and checklist item and the result instead of just the result)

[accessbro]

1. Hank - Software Development Projects
2. Walt - Infrastructure Projects
3. Rick - Information Security Projects
4. Carl - Internal Projects

Each manager represents a different area
Does each Area have a specific checklist or does the checklist change from project to project?
Do any of the checklist items overlap areas? (can the same check be performed by multiple areas)

Can a project affect multiple areas?

You want your structure to reflect the reality of how you do business, if you perform the same checks for an area for every project (in other words your checklist is always the same for a given area) I would have a junction table that defines which checklist item goes with which area, then when you create a new project you can define which areas are related to the project. This could help you avoid a lot of unnecessary data entry (enter the area and checklist item and the result instead of just the result)

Each area has a specific checklist as defined by the manager of that area.
A checklist item can overlap areas.
A project can only fall under one area.

1. tblCheckListItemsByArea
   
   • CheckListItemsByAreaID
   • fkCheckListItemID
   • fkAreaID
   
   
2. tblProjectCategory
   
   • ProjectCategoryID
   • ProjectCategoryName
   
   
3. tblRequiredCheckListItemsByCategory
   
   • fkProjectCategoryID
   • fkCheckListItemsByArea
   
   

I created a junction table called tblCheckListItemsByArea. Some checklist items (like requirements documents) overlap for areas but I chose to create separate records for each area. For example i would have an entry with the foreign key for requirements document and foreign key for Software Development projects and another entry with the foreign key for requirements document and Infrastructure projects. Would this cause any issues?

The managers can also decide which checklist items are required based on the project type/category. Some managers have defined their projects are small, medium, large but not all of them do. Rick categorizes his projects as "Project", "SOC", and "Systems Maintenance". I created tblProjectCategory and included small, medium, large, project, SOC, Systems Maintenance as separate entities. Would this cause any issues?

I created another junction table called tblRequiredCheckListItemsByCategory and included fkProjectCategoryID and fkCheckListItemsByArea. For example, if Software Development projects has 10 checklist items in tblCheckListItemsByArea, all 10 are required for large projects but only 7 are required for small and medium projects. I would have 10 entries in tblRequiredCheckListItemsByCategory with fkProjectCategoryID (for large) and fkCheckListItemsByAreaID (for all ten checklist items). I would then have 7 more entries with fkProjectCategoryID (for medium) and fkCheckListItemsByAreaID (for 7 of 10 checklist items as defined by the manager for that area). I would also have 7 more items for small.

I did it this way in the event that checklist items are added or removed or project categories are added or re-defined. Can this model scale?