There are many interpretations of risk management that result in different implementations. In the path I have followed, I have made a conscious decision to make the tables as ‘skinny’ as possible and to use indexes and foreign keys to the maximum benefit. Due to this, I hope to make the database engine find records as fast as possible. in the spirit of keeping things simple, I have avoided use of triggers (so far) and because I have used MySQL in the model, there are no check constraints (because the MySQL version I used did not support it).

It is interesting to note that in the modelling software I used, MySQL Workbench, some of the relationships appear to be one to many relationships visually however in some cases (not shown), the relationship is one to one because unique indexes are employed. That said, the below diagram is an initial attempt at a risk management design that has major components of most risk management applications.

model of a risk management database that supports projects and risks.

The basic aspects of the model allow for users creating one or more projects and where each project can contain multiple risks. The risks are assigned a severity value and a probability value. Because of the way the database is designed it is possible to enter in the probability and severity values separately.

Additionally the model uses a unique index to only multiple ‘risk owners’ per risk and multiple ‘project owner’ per project. Not shown in the diagram, the idrisk and iduser columns have a unqiue index in the riskowners table. Likewise, the idproject and iduser columns have a unique index in the project owners table. But, a change to those indexes would allow for just one risk owner and just one project owner. As mentioned in a prior post on the subject, an organization may want to have more than one person managing a project or a risk for a variety reasons such as a policy of overlapping certain reporting functions. In any case the idea is to keep the database structure the same but alter the index if need be to accommodate a customer’s preferences.

Adding Support for Risk Mitigation to the database model

The ability of any risk management database model to handle risk mitigation tracking is essential. Mitigation of risks is a central function of risk management activities.

Due to this, in the model above, risk mitigation relations were added. A user may enter in free text to describe the mitigation while assigning a known risk mitigation strategy. It is possible to use multiple strategies for one risk. One possible change could be made to allow for much larger text size. Instead of a varchar fixed size it might be better to allow for unlimited text space. Why? I have seen a few cases where comments can become quite large, unusually so, beyond what would normally be expected. That said, I used a varchar value in this model mainly because I judged it to be decent compromise. I wanted to avoid a situation where a print out of a risk mitigation report becomes unwieldy due to extra long text sizes.