How to Create Non-Identifying relationship

[Guest]

I have two tables. One is Issues and the other is Risks. Risks has a one-to-many relationship with Issues. A Risk can have 0 or more related Issues. In contrast, there can be many Issues without related Risks. When I create the one-to-many relationship between Risks to Issues and enforce referential integrity, I cannot create an Issue without a Risk. I’ve talked to a co-worker who is an Oracle person and they said it has to be a “non-identifying†relationship. How does one do that in Access? The primary key in Risks is "RiskID." The primary key in Issues is "IssueID" and the foreign key is RiskID. I've made sure that the FK in Issues is not set to "required.

The only way I can get this to work is to leave it without referential integrity, but this blows my queries that needs to show everything; all Issues and all Risks.

I appreciate any help anyone can offer. I'm a new user and really confused by this. In theory, I should be able to set cardinality, but don't see that capability in Access.

 

[PC Datasheet]

Deb,

Saying there can be many Issues without related Risks is contradictory to saying
risks has a one-to-many relationship with Issues. I suspect there is a problem
with the logic of your tables. Please describe what your database is trying to
do in terms of risks, issues and related data and we will try and resolve the
problem with you.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I have two tables. One is Issues and the other is Risks. Risks has a

one-to-many relationship with Issues. A Risk can have 0 or more related Issues.
In contrast, there can be many Issues without related Risks. When I create the
one-to-many relationship between Risks to Issues and enforce referential
integrity, I cannot create an Issue without a Risk. I've talked to a co-worker
who is an Oracle person and they said it has to be a "non-identifying"
relationship. How does one do that in Access? The primary key in Risks is
"RiskID." The primary key in Issues is "IssueID" and the foreign key is RiskID.
I've made sure that the FK in Issues is not set to "required."

The only way I can get this to work is to leave it without referential

integrity, but this blows my queries that needs to show everything; all Issues
and all Risks.

I appreciate any help anyone can offer. I'm a new user and really confused by

this. In theory, I should be able to set cardinality, but don't see that
capability in Access.

 

[Guest]

I'm not sure I understand why the relationship would be contrary. I'll explain. At the outset of a project, a project manager identifies risks to the success of the project. Throughout the life of the project the risks may never be realized because of effective mitigation strategies. However, a risk may be realized and in that case it becomes an Issue, or multiple Issue. These need to be in the Issues table; hence the many-to-one relationship. Issues, on the other hand, are not necessarily generated as a result of a Risk. They could just be an issue that arises -- say, as the project progress some things are identified as needing to be handled by someone. They're not risks realized, they're just issues that need to be captured and followed up on

From a purely puritanical perspective, all Issues are the result of a risk realized; however, in reality, it would be a cumbersome requirement to force a PM to create an associated Risk for every Issue that comes up. The more likely scenario is that they do a post-mortem at the end of the project, during whch they analyze all the issues and identify the related risks which then roll up into the next project as it's risks identified at the outset. Even this rarely happens in the real world.

Does that help?

 

[rkc]

I'm not sure I understand why the relationship would be contrary. I'll

explain. At the outset of a project, a project manager identifies risks to
the success of the project. Throughout the life of the project the risks
may never be realized because of effective mitigation strategies. However,
a risk may be realized and in that case it becomes an Issue, or multiple
Issue. These need to be in the Issues table; hence the many-to-one
relationship. Issues, on the other hand, are not necessarily generated as a
result of a Risk. They could just be an issue that arises -- say, as the
project progress some things are identified as needing to be handled by
someone. They're not risks realized, they're just issues that need to be
captured and followed up on.

From a purely puritanical perspective, all Issues are the result of a risk

realized; however, in reality, it would be a cumbersome requirement to force
a PM to create an associated Risk for every Issue that comes up. The more
likely scenario is that they do a post-mortem at the end of the project,
during whch they analyze all the issues and identify the related risks which
then roll up into the next project as it's risks identified at the outset.
Even this rarely happens in the real world.

You could have a bogus Risk record in the Risks table that all issues
without a risk are linked to.
or
You could have three tables
Risk (RiskID*)
Issue(IssueID*)
RiskIssue(RiskID*, IssueID*)

Three tables is probably the more correct model.

 

[PC Datasheet]

The flaw in the logic is that Issue is too broadly defined in that what is being
called an issue encompasses two totally different things. One is all related
risks where issue is being used as a pseudo category to group risks. The other
is followup items which are in your words "They're not risks realized, they're
just issues that need to be captured and followed up on" and hense are totally
dissimilar to being a pseudo category.

You can easily see the flaw in the three tables suggested by rkc:
Risk (RiskID*)
Issue(IssueID*)
RiskIssue(RiskID*, IssueID*)

In Risk, RiskID starts at 1. In Issue, Issue starts at 1. You can not enter a
record of only IssueID in RiskIssue because you would have to enter RiskID = 0
and since there is no record in Risk with RiskID = 0, you can not enforce
referential integrity.

The solution is to redefine Issue into its two elemental parts, Issue as a
pseudo category and followup issue. You need the following tables:

TblIssue
IssueID
other issue fields

TblRisk
RiskID
IssueID
other risk fields

TblFollowUpItem
FollowupItemID
FollowupItem

You can then build a form/subform to enter Issues and Risks where Issue is the
main form.

Then a separate form for followup items.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I'm not sure I understand why the relationship would be contrary. I'll

explain. At the outset of a project, a project manager identifies risks to the
success of the project. Throughout the life of the project the risks may never
be realized because of effective mitigation strategies. However, a risk may be
realized and in that case it becomes an Issue, or multiple Issue. These need to
be in the Issues table; hence the many-to-one relationship. Issues, on the
other hand, are not necessarily generated as a result of a Risk. They could
just be an issue that arises -- say, as the project progress some things are
identified as needing to be handled by someone. They're not risks realized,
they're just issues that need to be captured and followed up on.

From a purely puritanical perspective, all Issues are the result of a risk

realized; however, in reality, it would be a cumbersome requirement to force a
PM to create an associated Risk for every Issue that comes up. The more likely
scenario is that they do a post-mortem at the end of the project, during whch
they analyze all the issues and identify the related risks which then roll up
into the next project as it's risks identified at the outset. Even this rarely
happens in the real world.

 

[rkc]

The flaw in the logic is that Issue is too broadly defined in that what is being
called an issue encompasses two totally different things. One is all related
risks where issue is being used as a pseudo category to group risks. The other
is followup items which are in your words "They're not risks realized, they're
just issues that need to be captured and followed up on" and hense are totally
dissimilar to being a pseudo category.

You can easily see the flaw in the three tables suggested by rkc:
Risk (RiskID*)
Issue(IssueID*)
RiskIssue(RiskID*, IssueID*)

In Risk, RiskID starts at 1. In Issue, Issue starts at 1. You can not enter a
record of only IssueID in RiskIssue because you would have to enter RiskID = 0
and since there is no record in Risk with RiskID = 0, you can not enforce
referential integrity.

The solution is to redefine Issue into its two elemental parts, Issue as a
pseudo category and followup issue. You need the following tables:

TblIssue
IssueID
other issue fields

TblRisk
RiskID
IssueID
other risk fields

TblFollowUpItem
FollowupItemID
FollowupItem

You can then build a form/subform to enter Issues and Risks where Issue is the
main form.

Risk table.
A Risk is the first entity identified by the project manager.
A Risk can exist without becoming an Issue. It's a pre-emptive strike.
Let's watch out for this risk happening. It may never happen so an
Issue may never be related to it. The Risk table starts out populated
with identified Risks

Issue table.
An Issue can exist without ever being related to a Risk. An Issue can come
up
when a 'problem' that wasn't thought of and flagged by the project manager
arises. An Issue can also come up that causes an identified Risk to be
realized.
"See. I told you to watch out for that."
The Issue table starts out unpopulated.

RiskIssue table
When a Risk is realized it's because an Issue that embodies that Risk is
realized.
More than one issue can come up that embodies the identified Risk.
The RiskIssue table is populated only with previously Identified Risks and
related Issues that arise.

Your explanation of the flaw in that is lost on me.

 

[Rolls]

One < Many

Projects < Risks
Projects < Issues

not

Projects < Risks < Issues

If you want to relate an issue to a risk, do so with a parent:child
form:subform but referential integrity cannot be enforced since the "risk"
is optional.

 

[rkc]

One < Many

Projects < Risks
Projects < Issues

not

Projects < Risks < Issues

If you want to relate an issue to a risk, do so with a parent:child
form:subform but referential integrity cannot be enforced since the "risk"
is optional.

Maybe I mis-intepreted the op's intentions. I thought a risk was related to
a
project and an issue was sometimes related to a risk, but could also be
something that came up out of the blue. You would want to know which
issues were forseen in the form of a risk and which issues were not forseen.
You'd also want to know which risks were never realized because they
were managed away.

You can't do that with

Projects < Risks
Projects < Issues

 

[Guest]

You're absolutely on-track with my intent, RKC. Thank you for the suggestion to create an Risks.Issues table.

I'm a student of Access and am curious why this can't be done using my original design of one Issues table. From a theoretical perspective shouldn't you be able to specify that you can have 0 on the many side? Isn't that what cardinality is all about? And isn't that, in the Oracle world, what they call a non-identifying relationship? Is it just that Access as a tool can't do it or am I misunderstanding the theory

So, the other suggestion you had was to create a blank record in the Risks table and associate all Issues to that so you can satisfy the referential integrity need. Would this have a negative impact on queries/reports? Wouldn't I have to build in some logic that deletes that dead record

Thanks so much for your help. What I thought would be a very easy database project has turned way more complicated for my class than I anticipated. This project is for an information systems 101 course, not an Access course. ;-

Deb

 

[Guest]

I haven't gotten to forms in my class yet ... am I reading in your message that you can get around the inability to have a 0 on the many side in a query when you get to a form.subform? So, there must be some advantages/disadvantages to, for example, use a query vs. a form for calculations

Deb

 

[rkc]

Thanks so much for your help. What I thought would be a very easy

database project has turned way more complicated for my class than I
anticipated. This project is for an information systems 101 course, not an
Access course. ;-)

Hmm. Are you teaching the course or taking it?