I'm designing a database, in SQL Server 2005, and one of the tables stores 'risk assessments'. This 'risk assessments', besides the description, refers some risks, which are stored on other tables (image):
As the image shows, the risks are hierarchized, by category, then by risk_danger, risk_condition and, finally, measures to prevent the risk.
This can be seen as:
List of Categories ->has-> List of Risk/Dangers ->has-> List of Risk Conditions ->has-> Risk of measures
When the user creates a new risk assessment, he selects one or more categories, one or more risks/dangers inside each category, one or more risk conditions inside the risks/dangers and zero (0) or more measures to prevent the risk condition.
I want to retrieve all this data, after the creation of the risk assessment, in the correct hierarchy. My problem is where to save the data refering the several categories, risk/dangers, risk conditions and mesaures.
I thought of this:
But the column 'control_measure_id' can be
NULL (as I said, one risk does not need, mandatorily, one or more control measures), so it may not be the best approach.
Hope I've explained te problem clear enough.