Query design - conceptual question

[sg2808]

Hello,

I guess this is a basic question (may sound stupid but happy to get it clarified) so I will match my original scenario and I will try to keep it simple.

I have three tables with following fields:
[Rules]
RuleID - PK
RuleName

[Process]
ProcessID - PK
RuleID - FK
ProcessName

[Risk]
RiskID - PK
ProcessID - FK
RiskName

The relationship between the tables are:
[Rules] M:M [Process] 1:M [Risk]

No direct relation between [Rules] and [Risk].

So, operationally, one rule can impact multiple processes and one process could be impacted by multiple rules.
Also, one rule could impact many risks and one risk could be impacted by many rules.

If I have to find out (using a query) all the risks impacted by a specific rule, how can I do it with the above scenario?

Probably, cannot be done. Kindly confirm/clarify.

Is it ok if I add the field [RuleID] in the [Risk] table and then in the Query criteria put something like:

[Rule].[RuleID]=[Risk].[RuleID] to get all the [RiskName] for a specific [RuleName]?

Will this work? Is this a standard approach?

OR - Do I have to add a junction table between [Rules] and [Risk]?

Also, is this a standard approach to find specific information for the scenario between a Parent table and Nth Grandchildren table?

Kindly advise.
SG

[Rabbit]

You should normalize your data but to get from one end to the other end, you just join rules to process and then join process to risk.

[sg2808]

Hi Rabbit,

Sorry, I am not clear. On which field do I join, on Table IDs or [RuleName]/[ProcessName]/[RiskName]?

Also, what criteria will I have to use to find the [RiskName] for a specific rule? eg, a process may have 5 Risks but say for a specific rule eg [Rule1], only one of them is linked to Rule 1. So, how will I filter out this information in the query?

Thanks for your help.

[Rabbit]

You join on the keys. You filter for the rule id you want.