Many to Many Relationships- How far is too far?

[Megan]

hello everybody,

i know this is a very long post, but i wanted to provide as much
detail as possible.

quick overview- i want to create a couple of many to many
relationships and am wondering how many relationships to create. i am
also trying to figure out what relationships to create.
there is one huge table that i need to breakup into several tables.

the table i need to split into multiple tables is called case.

two of the new tables i want to create are called issues and outcomes.
right now, the fields that store these data are part of the table,
case.

for example, the table, case, has the following fields-

caseid = primary key
issue1
issue2
issue3
outcome1
outcome2
outcome3
etc...

i want to replace the issue fields and the outcome fields with tables.
i'm thinking about creating a many to many relationship between case
and issue and a many to many relationship between case and outcomes.
so, i'll create a bridge table for each many to many relationship.

for example, i'll create a bridge table between the tables case and
issue named caseissue. the table, case, has caseid as a primary key,
and the table, issue, has issueid as a primary key. so, my bridge
table, caseissue, will have caseid and issueid as a double primary
key.

i'm also considering creating a bridge table between the tables case
and outcome named caseoutcome. the table, case, has caseid as a
primary key, and the table, outcome, has outcomeid as a primary key.
so, my bridge table, caseoutcome, will have caseid and outcomeid as a
double primary key.
i have a form, caseentry, that returns all of the related data, i.e.
caseid, issue1, issue2, issue3, outcome1, outcome2, outcome3, etc...
if i create these many to many relationships, i suppose that i'll have
to link them to my main form, caseentry, through 2 subforms. one
subform for the many to many relationship between case and issue and
one subform for the many to many relationship between case and
outcome.

BUT i'd like to link the table, issue, with the table, outcome.

so, do i need to create a many to many relationship between issues and
outcomes? or will my bridge tables between my many to many
relationships between case and issue and between case and outcome be
sufficient to determine which outcomes are associated with an issue or
issues?

here is an overview of what i want to do...

1. i want to have a form, caseentry, that can be used to both enter
data and display a record.

2. i want to have 3 combo boxes for the user to select an issue from.
each issue will be unique. the user can only select an issue once, but
he or she can select 3 issues or less. so, only those issues selected
will be saved in bridge table, caseissue. i also want to be able to
display the previously selected issues when a user looks at the
records. this is one of the reasons why i want to remove the multiple
issue fields from the table, case; so that only those issues selected
will be saved. in the inherited database, those extra fields in the
table, case, will be unused or blank and be a waste of memory.

3. i also want to have a similar setup for the outcomes- 3 combo boxes
that the user can select outcomes from and will display the previously
chosen outcomes.

4. i want the issues to be linked to the outcomes. for example, i can
create a query to return all of the outcomes that are associated with
a particular issue or issues.
thanks a lot for your time and effort reading my post. i look forward
to reading your suggestions. thanks again.

megan

[Bob Quintal]

me**************@hotmail.com (Megan) wrote in
news:5c**************************@posting.google.c om:

hello everybody,

i know this is a very long post, but i wanted to provide as
much detail as possible.

quick overview- i want to create a couple of many to many
relationships and am wondering how many relationships to
create. i am also trying to figure out what relationships to
create.
i have a form, caseentry, that returns all of the related
data, i.e. caseid, issue1, issue2, issue3, outcome1, outcome2,
outcome3, etc... if i create these many to many relationships,
i suppose that i'll have to link them to my main form,
caseentry, through 2 subforms. one subform for the many to
many relationship between case and issue and one subform for
the many to many relationship between case and outcome.

BUT i'd like to link the table, issue, with the table,
outcome.

Actually, IMHO, you only need one table for issue and (each
issue's) outcome. In my view, every issue needs to be resolved
before a case can be closed. Therefore keeping the pair in the
same table seems to be a good way to ensure this.

so, do i need to create a many to many relationship between
issues and outcomes? or will my bridge tables between my many
to many relationships between case and issue and between case
and outcome be sufficient to determine which outcomes are
associated with an issue or issues?

I believe that one to one between issue and outcome is
sufficient. Occasionally you will close an issue by referring to
the outcome of another issue in the same case.

here is an overview of what i want to do...

1. i want to have a form, caseentry, that can be used to both
enter data and display a record.

2. i want to have 3 combo boxes for the user to select an
issue from. each issue will be unique. the user can only
select an issue once, but he or she can select 3 issues or
less. so, only those issues selected will be saved in bridge
table, caseissue. i also want to be able to display the
previously selected issues when a user looks at the records.
this is one of the reasons why i want to remove the multiple
issue fields from the table, case; so that only those issues
selected will be saved. in the inherited database, those extra
fields in the table, case, will be unused or blank and be a
waste of memory.
What if your case has four issues? So a much better way would be
to design the form with a continuous forms subform that shows all
the issues for that case, and the outcome of that issue. You will
only need a single combobox to choose the issue, not the three,

3. i also want to have a similar setup for the outcomes- 3
combo boxes that the user can select outcomes from and will
display the previously chosen outcomes.
See above.
4. i want the issues to be linked to the outcomes. for
example, i can create a query to return all of the outcomes
that are associated with a particular issue or issues.
thanks a lot for your time and effort reading my post. i look
forward to reading your suggestions. thanks again.
Good luck.
megan

[(Pete Cresswell)]

RE/

quick overview- i want to create a couple of many to many
relationships and am wondering how many relationships to create. i am
also trying to figure out what relationships to create.

for example, the table, case, has the following fields-

caseid = primary key
issue1
issue2
issue3
outcome1
outcome2
outcome3
etc...

I'd think you would want a couple of one-to-many relationships.

Viz:

1 Case has
Many Issues
1 Issue has
Many Outcomes.
Put another way,
tblCase (pk=CaseID)
tblIssue (pk=IssueID, fk=CaseID)
tblOutcome (pk=OutcomeID, fk=IssueID)

In practice, maybe one Issue always has only one outcome...but it seems making
it a 1:many wouldn't cost anything and if somebody wanted many outcome records
(e.g. maybe a log of actions on an issue as they occur) it wouldn't break the
architecture.
--
PeteCresswell