By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Query design - conceptual question

P: 91
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
Apr 12 '12 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,315
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.
Apr 12 '12 #2

P: 91
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.
Apr 12 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
You join on the keys. You filter for the rule id you want.
Apr 12 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.