Open Items Tracking Database Design Questions

[visphoto]

I am trying to create an Access database that will track open items generated by either Hazards or Vulnerabilities. I have created the following so far:

Item
ItemID (PK)
DateOpened
Item Description
SubSysID (FK)
Status
DateClosed
Resolution

Affects

System
SYSID (PK)


SysName

That has 1 or more

Subsystem
SubsysID (PK)
SystemID (FK)
Subsystem Name

My question is how do I proceed? Do I create Hazard and Vulnerability tables? If so, how do I relate them to the Item table? If the open item is related to a Hazard, it has different mitigation criteria than if it is related to a vulnerability (HAZMIT and VOLMIT tables?). Additionally, the risk index data is different for them as well (HazRisk and VulRisk tables?).

Any suggestions will be greatly appreciated.

[orange]

How about stepping back and giving us the 30000 foot overview of you situation --plain English, simple terms, no jargon.

What is an Item? remember we know nothing of your business/opportunity
Same for System and subSystem.
and you should include mitigation criteria and vulnerability,

Treat us like 10 year olds -- every detail - until we understand your post.

[NTC]

If there can only be 1: either a single Hazard or Vulnerability - then merge them into a single table....

If there can be both a Hazard and a Vulnerability - but 1 of each only ; going with 2 tables may make sense

On the otherhand - if there can be multiple of these - then you might consider going back to a single table but implementing it via a sub form so you can have unlimited quantity.

[visphoto]

The purpose of the database is to track Hazards and Vulnerabilities to Systems and SubSystems of a construction project to closure. An item represents one of many hazards or vulnerabilities that has been identified that will require a mitigation effort before that system (major component = about 8) or subsystem (between 2-10 per system) can be considered safe and acceptable by the owner. Hazards (safety related) have a different risk ratings (20 possible) than vulnerabilities (security related) (again 20 different ratings). An example of a hazard to a Communication (System) Backbone (SubSystem) is "Damage to communication cables from water intrusion" and potential mitigation measures include "Ensure adequate drainage, reduce or eliminate splices, seal duct openings, etc.". An example of a vulnerability is "Sabotage to Facility" (Where the Facility is the System and General is the SubSystem) and potential mitigation measures include " Provide adequate lighting to the interior, exterior, and perimeter of the facility, perform periodic and random security patrols, post no trespassing and other appropriate signs, install access control devices, etc." The Hazard or Vulnerability has an initial risk index (Higher) but through the use of the mitigation measures, it will ultimately have a residual (lower) index. Hopefully this helps.

Thanks for the response.

[orange]

A Project involves 1 or more Systems
A System may have 1 or more SubSystems
A ??System or Subsystem can incur 0 or many Hazards <--unsure if the Hazard relates to System or subsystem??
A ??System or Subsystem can be open to 0 or more Vulnerabilities<--same here
A Hazard is rectified by 1 or more MitigationMeasures
A Vulnerability is reduced by 1 or more MitigationMeasures
A MitigationMeasure is applicable to 0 or more Hazards
A MitigationMeasure is applicable to 0 or more Vulnerabilities
A Hazard or a Vulnerability may be considered a Weakness<---my term because there seems to be some overlap
A Weakness may be associated with a WeaknessType <----------my generalizing here
A Weakness has an initial RiskIndex
A MitigationMeasure affects a WeakNessRiskIndex <-------------not sure where this fits

Just some ideas from reading your post (great explanation by the way).
Some things to consider.

Get your tables and relationships set up before jumping into Forms/queries etc.
Get some test data scenarios and use them to test your model.
Reconcile every anomaly you encounter --fix the model/ fix the data .. whatever.
When you're satisfied your model is correct, then go on to the other Access parts (forms queries, interface..)

Good luck.

[NTC]

the fact that Hs & Vs have different descriptors isn't an issue; generically you can call them Issues and scroll down to select the 1 that is appropriate ..... so they can be set up in 1 table without a problem. Tables are multi column so you can identify each record as an H or V ...

How the Issues List is oriented/designed into the overall Project is unclear; if a System or Subsystem can have only 1 Issue vs multiple Issues will drive the design consideration. It may be no more complicated than:

Problems Table & Form:
Enter System/subsystem Name: select Issue
Enter System/subsystem Name: select Issue
Enter System/subsystem Name: select Issue
etc.

Make your 'Select Issue' field in your Problems Table to be a look up of your Issues List table