Database design

[Luc Ferrari]

Rookie question :

I want to make a database for the evaluation of occupational safety of
different jobs

Here is how it should work :

Job : Example : "Installing electrical cabinets & cables"
I want to split the job in tasks : example "Task 1: Installing cable
ladders"
Each task can have multiple risks attached to it : example risk
for Task 1 : "Falling from height"
For each risk there are different sub-risks : example :
"Falling through the roof"
Four each sub-risk there are different preventive
measures that can be coupled with the sub-risk : example " use safety
harness"


The risks, sub-risks and preventive measures are all selectable throug a
table (or extra items can be added).


So how do i set up such a database.
I want to enter the data through a form, in a easy and clear way.
Are there examples with the same structure available ?


Thanks for your replies,

Luc

 

[Allen Browne]

Luc, I'm not sure if I have this right, but it seems that jobs are made up
of several steps, and those steps could also appear in other jobs. Therefore
it's a many-to-many, so tables would be like this:

tblJobtype table (one record for each kind of job), with fields:
- JobTypeID primary key
- JobTypeName description of this type of job

tblStep table (the possible steps for jobs), with fields:
- StepID primary key
- StepName Text

tblJobtypeStep (the actual steps in a job type), with fields:
- JobTypeID relates to tblJobType.JobTypeID
- StepID relates to tblStep.StepID
- SortOrder number. The order the steps are performed in.

Now you also have a list of the possible risks that could be faced, and a
list of preventative measures. Again, one risk can have many preventative
measures, and one measure could be used for multiple risks, so the
many-to-many would be modelled like this:

tblRisk (one record for each risk), with fields:
- RiskID primary key
- RiskName Text

tblMeasure (one record for each preventative measure), fields:
- MeasureID primary key
- MeasureName Text

tblRiskMeasure (the actual measures for each risk), fields:
- RiskID relates to tblRisk.RiskID
- MeasureID relates to tblMeasure.MeasureID

Finally, each step could have multiple risks, so you need a tblStepRisk with
fields:
- StepID relates to tblStep.StepID
- RiskID relates to tblRisk.RiskID

To interface this, you will need:
- a form where you enter the possible preventative measures
- a form where you enter the possible risks, with a continuous subform for
the applicable preventative measures (one per row.)
- a form where you enter the steps, with a continuous subform for the risks
in that step.
- a form where you enter the job types, with a continuous subform for the
steps in that type of job.