The Area being Modelled is :-
A Database for Hazard Management

The Requirements have been defined by Jim Schnedar.  



Subject: Data Model Design
Question: Barry, We have several investigation mediums that produce recommendations.

We have a hazard report, inspection, or accident report.

Note that Barry has called these Events in the Data Model.

Any of these work products (that is Events)may generate one or more recommendations.

The following rules apply:
1. For the most part the recommendations are similar across the work products (fifteen fields in common),
but there are some minor differences.
For example, an accident report, a finding event is recorded as to the reason for the recommendation.

2. A recommendation may be due to an identified hazard (gravel on a sidewalk) or deficiency (exit sign not posted).

3. A hazard garners a risk assignment (probability/severity/quantified result) and interim control measures
(single free-form field) whereas a deficiency does not have either.

4. Recommendations from the originating report must be "preserved".
   That is, after completion of the work product, then the recommendations are worked.
When worked, there is a status (open/awaiting closure/closed),
resolution path (re-design, safety device, warning, device, procedure changes, etc.), progress monitor
(how far along are you on fixing the issue), and record of closure approval.

5. An accident report has a special QC that occurs.
Therefore, the original accident report recommendations are "preserved",
they are QC'd and may change, in which the nature of the result (no change, changed, admin change, deleted),
reason for change, and person responsible for approving change. Our team has spent many hours flopping back and forth across designs.

Do we:
1. Have a recommendation supertype table with subtypes for accident report,
hazard report, inspection, accident report QC, and In Work recommendations?
2. Have one common recommendation table with map tables from each work product
to the common recommendation table? Do the map tables contain unique work product
data (i.e. accident report QC unique data items)?
3. Have one common recommendation table with all attributes for all work products?
If so, map tables or individual foreign keys from common recommendation to each work product.
4. Have a recommendation table for each work product?

I would be happy to send any more information or proposed ER Diagrams.

Any input you have is greatly appreciated.

Thanks,

Jim Schnedar

Barry has defined the 'Things of Interest' as follows :-
A.1 Hazards
A.2 Recommendations
A.3 Reports
A.4 Others To Be Determined




 © DataBase Answers Ltd. 2010