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.
>
>
>
>