Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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):

hierarchy relationship between tables

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:

junction table between 'risk_assessments' and the risks

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.

share|improve this question
    
What is the actual question that you want answered? –  JNK Jan 18 '12 at 13:35
    
Thaks for the answer JNK. I would like to know what is the best approach to do this. I know I could have a junction table linking 'risk_assessments' and 'control_measures', and from there get the 'risk_condition', the 'risk/danger' and the category'. But since one 'condition_risk' may not have 'control_measures', it wouldn't work. –  Pedro Duarte Jan 18 '12 at 14:10
    
I've made some drawings to help me understand the problem. This one demonstrates the hierarchy between the categories, risks/dangers, risk conditions, and control measures: Hierarchy This is a sample of a 'risk assessment' entry on the database: Risk Assessment Entry As you can see, a 'risk_assessment' entry does not need all categories, neither all risks/dangers inside, and so on. –  Pedro Duarte Jan 18 '12 at 15:03
add comment

1 Answer

In your case, I'd not think of tables yet: write down your facts.
You seem to have skipped the modelling phase

In any event, your proposed junction table will allow mismatches of category, danger and conditions.

I knocked this ORM (Object Role Model, using NORMA) up just now (the Fact Editor makes it quick), which gives an ERD and relational view (all below). As I see it:

  • A Danger should be many-many with Conditions
  • An Assessment is many-many with Conditions
  • For each Assessment/Condition pair, you may have a measure
  • The same measure can be used for more then one Assessment/Condition pairs

This is where the strength of ORM comes in: you can "verbalise" the model in plain English.

Example, this is the tenary fact type for Assessment-Condition-Measure

Assessment has Condition has Measure.

Each Assessment has some Condition has some Measure.

For each Condition, some Assessment has that Condition has some Measure.

For each Assessment and Condition, that Assessment has that Condition has at most one Measure.

This association with Assessment, Condition provides the preferred identification scheme for AssessmentHasConditionHasMeasure.

And for Condition/Danger

Condition consists of RiskDanger.

It is possible that more than one Condition consists of the same RiskDanger and that the same Condition consists of more than one RiskDanger.

In each population of Condition consists of RiskDanger, each Condition, RiskDanger combination occurs at most once.

This association with Condition, RiskDanger provides the preferred identification scheme for DangerCondition.

Each Condition consists of some RiskDanger.

For each RiskDanger, some Condition consists of that RiskDanger.

Diagrams:

enter image description here
enter image description here
enter image description here

share|improve this answer
    
Hi @gbn! Thanks for all the effort to help me, I really appreciate it! You were right by saying I neglected the modelling phase, but I'm really struggling to have the project finished on time. I read all you've written, and it helped me a lot on how to face the problem. However, I can't follow some of your advices. Explaining: the hierarchical model of the Categories->Dangers/Risk->RiskConditions->ControlMeasures has to be just like it is, unfortunatly. I have to be able to have a RiskCondition with several (or none) ControlMeasures. –  Pedro Duarte Feb 13 '12 at 15:07
    
I have to continue here. I made this drawing wich I guess is closer to the solution: link. I realized I don't need to care with RiskCategories and Risks/Dangers, as I can access them by the RiskCondition. Basically, I have all the data in those tables, and then I need to be able to create a RiskAssessment with some of that data. It may have some RiskConditions, and some of those conditions may have ControlMeasures. –  Pedro Duarte Feb 13 '12 at 15:11
    
@PedroDuarte: the main point here is how the condition-measure is modelled, which I think I captured. Note, your deadline issues may come from lack of modelling... –  gbn Feb 13 '12 at 15:14
    
Yes, you've made a perfect job, and I can't be thankful enough. The main point is: all the data in the tables 'RiskCategories','RisksDangers','RiskConditions' and 'ControlMeasures' is already there. I use it, for instance, to populate combo box in the GUI. The user then selects some of those Conditions and Measures and i must save them. Some Conditions may have more than one Measure. I understand it may be a little confusing to explain, though. –  Pedro Duarte Feb 13 '12 at 15:24
    
@PedroDuarte: ok then You should be able to capture that last bit then based on what I've done... –  gbn Feb 13 '12 at 15:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.