recursive relationships

I am trying to figure out the proper table layout for my database.
I have 3 tables at this time:
THREAT, VULNERABILITY & CONTROL

A THREAT entry will generate 1 to many VULNERABILITIES
A VULNERABILITY will generate 1 to many CONTROLS.
But a VULNEARBILITY after applying CONTROLS can have residual VULNERABILITY
which in itself is a VULNERABILITY. I thought of creating a
RESIDUAL_VULNERABILITY table which could generate 1 to many CONTROLS. But the
information in VULNERABILITY and RESIDUAL_VULNERABILITY will have the exact
same fields and link to the same CONTROL table.
Now CONTROL can generate THREAT entries and I really don’t want to create a
second THREAT table, because as I stated earlier a THREAT can generate 1 to
many VULNERABILITY entries.

Does anyone have any suggestions of how I could link VULNERABILITY back to
itself and CONTROL to THREAT. I am not sure if I should have some
intermediate tables that I perform this recursive activity.

The database design should implement your goals and definitions. You'll
need to start by making (or telling us about) a few more decisions /
definitions. There's no "right" or "wrong" on these, it's database :

Precisely what do you want your One-to-many ("A to B") relationships to
record/document? Your overall theme is that they record GENERATION, i.e.
instances of where the linke record in "A" generated the linked record in
"B". Other choices besides "generation" might be "GeneratedOrModified"
"Modified" etc.

If your answer is "generated":

Ignoring it's different "roots", do your consider/ seek to record a
residualVulnerability to be a new different type of entity entity than a
VUlnerability?

And a related question, does a control really generate a new vulnerabiliity?
(vs. just modify an existing threat)










"zzqv9p" wrote:

I am trying to figure out the proper table layout for my database.
I have 3 tables at this time:
THREAT, VULNERABILITY & CONTROL

A THREAT entry will generate 1 to many VULNERABILITIES
A VULNERABILITY will generate 1 to many CONTROLS.
But a VULNEARBILITY after applying CONTROLS can have residual VULNERABILITY
which in itself is a VULNERABILITY. I thought of creating a
RESIDUAL_VULNERABILITY table which could generate 1 to many CONTROLS. But the
information in VULNERABILITY and RESIDUAL_VULNERABILITY will have the exact
same fields and link to the same CONTROL table.
Now CONTROL can generate THREAT entries and I really don’t want to create a
second THREAT table, because as I stated earlier a THREAT can generate 1 to
many VULNERABILITY entries.

Does anyone have any suggestions of how I could link VULNERABILITY back to
itself and CONTROL to THREAT. I am not sure if I should have some
intermediate tables that I perform this recursive activity.

Hello zzqv9p,

How does this sound:

tblThreats
tblVulnerabilities (linked to threats, and a link indicates that it is a
vulnerability that arises fro that particular threeat
tblControls (linked to vulnerabilities) Actions intended to mitigate the
vulnerability that they are linked to..


By your answers and by the above standard, "residual vulnerabilities" are
just vulnerabilities, with no distinction. They are the same entity that you
originally recorded as the vulnerability, even if mitigated etc.

And then a "loop" recursive link from controls back to threats, to record
them as a source of particular threats.

thanks I was hoping for something simple like this. I just wasn't sure if
from a databsse design this was an acceptable practice.

"Fred" wrote:

Hello zzqv9p,

How does this sound:

tblThreats
tblVulnerabilities (linked to threats, and a link indicates that it is a
vulnerability that arises fro that particular threeat
tblControls (linked to vulnerabilities) Actions intended to mitigate the
vulnerability that they are linked to..


By your answers and by the above standard, "residual vulnerabilities" are
just vulnerabilities, with no distinction. They are the same entity that you
originally recorded as the vulnerability, even if mitigated etc.

And then a "loop" recursive link from controls back to threats, to record
them as a source of particular threats.