Improvements to the Risk Management Database Design

In the prior post we introduced a partially completed Risk Management Database Design. In this post we will expand it to include the ability of a single risk to be managed by one or more ‘risk owners’. Why do this? There are some benefits to allowing a risk to have more than one risk owner. First, in cases where more than one person manages a project the risk management software¬† must be able to describe both users. If the table structure only supports one risk owner, then the organization must duplicate risks, or share account information to allow both managers to logon. Neither of these choices are good choices. In the case of duplication of risks, it makes no sense to manage two records because of the extra work involved. Additionally, sharing a single account is a violation of best practices for securing information systems. For application security, it is required that a single account be linked to a single user. If the two managers use the same account then when the information system audits activity, it cannot know for certain which manager actually performed an action in the application. Finally, from a feature set standpoint of the application, allowing one or more risk owners will benefit a wider range of organizations than restricting it.

entities in the risk management database design

The risk management database software should be capable of tracking

projects – a project name and the project owners are defined.

users a user name is defined.

risks – a risk name is defined

Initial Features

users can create many risks

a user can ‘own’ many risks

Risk a Risk Management Database Design Visual

In the below model notice that there is a one to many relationship between the project owners and project. Similarly, a one to many relationship exists between risks and risk owners. This allows sharing of responsibilities by teams rather than individuals.

A team friendly database design of a risk management database application

Risks, Projects, Risk Owners and Project Owners are designed for teams to manage risks and projects rather than individuals.

The above design is easily adjusted to fit the opposite requirement of having a single risk owner and a single project owner without removing any of the above tables or columns.

Modify the database design to constrain only one risk owner per risk?

Rather than rely on the front end code to enforce your organizations risk management philosophy it is better to make the change in the database first. In the above diagram if you modify the risk owners table to include a unique index on idrisk and idusers it will enforce your organization’s preference for only one risk owner per risk.

Modify the database design to constrain only one project owner per project?

It is almost always better to adjust the database code to enforce business rules rather than the front end. For example if we were to change the database to enforce a rule that a single project owner will exist for each project we could make index adjustments. We could for example, alter the project owners table and create a unique index on idproject. In this way, it will allow a single user to ‘own’ the project but generates an error if an attempt is made to add an additional user to the same project.

In the above case, consider if your market research determines that 50% of your customers prefer a single project owner per project while the other 50% prefer allowing for one or more. You could then create an installation set up program that  allows the customer to choose an option they prefer and then adjust the index accordingly.