Joining m-to-m Junction Tables for N-Dim queries

I have several many-to-many junction tables. I'll name three:
PeopletoCountries, PeopletoTopics, PeopletoInfotypes.

My ultimate goal is to show the mapping between people, countries, topics,
and infotypes, e.g.. for a given person, what topics, countries, and
infotypes are implicated; for a given country, what people, topics, and
infotypes are implicated? And so on ...

My question: Are my pairwise junction tables sufficient or will my ultimate
query(ies) require some master junction table of my junction tables?

Thanks in advance for any help rendered

Keith

Given the three junctions tables you mentioned,
PeopletoCountries, PeopletoTopics, PeopletoInfotypes.
it sounds like "person" could be a common (shared) field.

If that is the extent of your relationships and m-m resolver/junction
tables (i.e., "PersonToX"), I don't see the need to create a "master".

If your real-world relationships include "CountryToTopic" and/or
"InfoTypeToTopic" or ..., then it still doesn't imply a need for a master
junction table.

Now, if you have unique combinations of Person, Place, Topic, Type, ..., a
junction table WOULD be needed to resolve those.

JOPO (Just one person's opinion)

--
Regards

Jeff Boyce
MS Office/Access MVP


My ultimate goal is to show the mapping between people, countries, topics,
and infotypes, e.g.. for a given person, what topics, countries, and
infotypes are implicated; for a given country, what people, topics, and
infotypes are implicated? And so on ...

My question: Are my pairwise junction tables sufficient or will my
ultimate
query(ies) require some master junction table of my junction tables?

Thanks in advance for any help rendered

"Jeff Boyce" wrote:

Keith

Given the three junctions tables you mentioned,
PeopletoCountries, PeopletoTopics, PeopletoInfotypes.
it sounds like "person" could be a common (shared) field.

If that is the extent of your relationships and m-m resolver/junction
tables (i.e., "PersonToX"), I don't see the need to create a "master".

If your real-world relationships include "CountryToTopic" and/or
"InfoTypeToTopic" or ..., then it still doesn't imply a need for a master
junction table.

Now, if you have unique combinations of Person, Place, Topic, Type, ..., a
junction table WOULD be needed to resolve those.

JOPO (Just one person's opinion)

--
Regards

Jeff Boyce
MS Office/Access MVP


Jeff,

Thanks for your response. Can you say a little more about the "unique"
combinations of my 4 variables. If you can imagine a 4-D cube where the 4
variables I listed are each axis, what I am effectively trying to do is place
an a "High, medium, low, or N/A" in every gridbox. I suspect that means that
I fall into the uniqueness case that you spoke about.

How will the results of my query change if I leave the junction tables
separated and pairwise vice all merged into one "master" junction table.

Thanks for all of your help.

Keith

Keith

I may still not understand the real-world situation you are dealing with.

If the ONLY common denominator is Person, and you are relating Person-to-X,
Person-to-Y, etc., you don't need a master table. A query that connects the
three tables would show the X, the Y and the Z related to a person.

The concept of a "unique" combination is something only you can define.
Forget about Access for a moment and describe the real-world (yours).

A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes

This is what I got from your post. You fill in the rest...

--
Regards

Jeff Boyce
MS Office/Access MVP

"Keith McCarron" wrote in message
...


"Jeff Boyce" wrote:

Keith

Given the three junctions tables you mentioned,
PeopletoCountries, PeopletoTopics, PeopletoInfotypes.
it sounds like "person" could be a common (shared) field.

If that is the extent of your relationships and m-m resolver/junction
tables (i.e., "PersonToX"), I don't see the need to create a "master".

If your real-world relationships include "CountryToTopic" and/or
"InfoTypeToTopic" or ..., then it still doesn't imply a need for a
master
junction table.

Now, if you have unique combinations of Person, Place, Topic, Type, ...,
a
junction table WOULD be needed to resolve those.

JOPO (Just one person's opinion)

--
Regards

Jeff Boyce
MS Office/Access MVP


Jeff,

Thanks for your response. Can you say a little more about the "unique"
combinations of my 4 variables. If you can imagine a 4-D cube where the 4
variables I listed are each axis, what I am effectively trying to do is
place
an a "High, medium, low, or N/A" in every gridbox. I suspect that means
that
I fall into the uniqueness case that you spoke about.

How will the results of my query change if I leave the junction tables
separated and pairwise vice all merged into one "master" junction table.

Thanks for all of your help.

Keith

Jeff,

I understand exactly what you mean relative to the list you composed. I'm
just wrestling with the members of that list that are inter-related. Consider
this like a call center kind of deal. Not only do certain people handle
certain countries, topics, etc., but also certain topics are dominated by
certain countries and vice versa. So while the people provide the linkage to
a lot of the data, I know that both country and topic are also inter-related
and I foresee a need to slice my data by these as well.

So to make this even more clear, given your list:
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes

What if a topic can have 1-m countries and vice versa? Should I combine
Person, Countries, Topics into a master junction table then?

I appreciate your patience. I suspect I'm being a bit thick-headed here.
You've been very helpful.

Regards,
Keith

"Jeff Boyce" wrote:

Keith

I may still not understand the real-world situation you are dealing with.

If the ONLY common denominator is Person, and you are relating Person-to-X,
Person-to-Y, etc., you don't need a master table. A query that connects the
three tables would show the X, the Y and the Z related to a person.

The concept of a "unique" combination is something only you can define.
Forget about Access for a moment and describe the real-world (yours).

A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes

This is what I got from your post. You fill in the rest...

--
Regards

Jeff Boyce
MS Office/Access MVP

PMFJI, but surely the key questions are along the lines "Can a country
be related to a topic (or a topic to a country) otherwise than through a
person?"

For example, suppose person "Jim" has "Tanzania" as a country and
"diamonds" as a Topic, and is the only person of whom this is true. That
gives you an indirect relationship between "Tanzania" and "diamonds" via
"Jim", which is easily retrieved with a query joining PeopletoCountries
and PeopletoTopics (and maybe the Countries and Topics tables).

Now suppose Jim leaves. With this structure, the Tanzania-diamonds
relationship disappears with him. If that's what you want to happen,
well and good - but if you need to be able to relate a Country to a
Topic independently of there being a Person who deals with both of them,
you'll need a TopicsToCountries table too.

HTH

On Sun, 25 Sep 2005 17:28:03 -0700, "Keith McCarron"
wrote:

Jeff,

I understand exactly what you mean relative to the list you composed. I'm
just wrestling with the members of that list that are inter-related. Consider
this like a call center kind of deal. Not only do certain people handle
certain countries, topics, etc., but also certain topics are dominated by
certain countries and vice versa. So while the people provide the linkage to
a lot of the data, I know that both country and topic are also inter-related
and I foresee a need to slice my data by these as well.

So to make this even more clear, given your list:
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes

What if a topic can have 1-m countries and vice versa? Should I combine
Person, Countries, Topics into a master junction table then?

I appreciate your patience. I suspect I'm being a bit thick-headed here.
You've been very helpful.

Regards,
Keith

"Jeff Boyce" wrote:

Keith

I may still not understand the real-world situation you are dealing with.

If the ONLY common denominator is Person, and you are relating Person-to-X,
Person-to-Y, etc., you don't need a master table. A query that connects the
three tables would show the X, the Y and the Z related to a person.

The concept of a "unique" combination is something only you can define.
Forget about Access for a moment and describe the real-world (yours).

A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes

This is what I got from your post. You fill in the rest...

--
Regards

Jeff Boyce
MS Office/Access MVP

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

"John Nurick" wrote:

PMFJI, but surely the key questions are along the lines "Can a country
be related to a topic (or a topic to a country) otherwise than through a
person?"

For example, suppose person "Jim" has "Tanzania" as a country and
"diamonds" as a Topic, and is the only person of whom this is true. That
gives you an indirect relationship between "Tanzania" and "diamonds" via
"Jim", which is easily retrieved with a query joining PeopletoCountries
and PeopletoTopics (and maybe the Countries and Topics tables).

Now suppose Jim leaves. With this structure, the Tanzania-diamonds
relationship disappears with him. If that's what you want to happen,
well and good - but if you need to be able to relate a Country to a
Topic independently of there being a Person who deals with both of them,
you'll need a TopicsToCountries table too.

HTH

John Nurick [Microsoft Access MVP]

John,

Thanks for jumping into the fray. I believe I'm dealing with the latter
situation here. I need to relate countries to topics independent of whether
or not there is actually a person assigned to work them. So, you've convinced
me that I need a CountrytoTopic table.

Does this mean, due to the relationship of the following three variables
(person, country, topic), that I should join all three Junction tables into a
"3-D" junction table, i.e., a table named PersontoCountrytoTopic Junction
Tbl, in order to properly query the info? Or is having the 3 Junction tables
separate sufficient for any querying of my data by those variables?

Thanks for your help. Hope you had a good weekend.

Keith

Thanks for jumping in, John. I believe you stated what I wanted to ask.

Keith, to rephrase, YOU need to define the relationships, not John, not me.
By asking you to step away from Access and your computer, I wanted you to
consider what the real-world situation is you are dealing with.

First define the entities and relationships, then build the structures in
Access to help you record facts about those.

--
Regards

Jeff Boyce
MS Office/Access MVP

"John Nurick" wrote in message
...
PMFJI, but surely the key questions are along the lines "Can a country
be related to a topic (or a topic to a country) otherwise than through a
person?"

For example, suppose person "Jim" has "Tanzania" as a country and
"diamonds" as a Topic, and is the only person of whom this is true. That
gives you an indirect relationship between "Tanzania" and "diamonds" via
"Jim", which is easily retrieved with a query joining PeopletoCountries
and PeopletoTopics (and maybe the Countries and Topics tables).

Now suppose Jim leaves. With this structure, the Tanzania-diamonds
relationship disappears with him. If that's what you want to happen,
well and good - but if you need to be able to relate a Country to a
Topic independently of there being a Person who deals with both of them,
you'll need a TopicsToCountries table too.

HTH

On Sun, 25 Sep 2005 17:28:03 -0700, "Keith McCarron"
wrote:

Jeff,

I understand exactly what you mean relative to the list you composed. I'm
just wrestling with the members of that list that are inter-related.
Consider
this like a call center kind of deal. Not only do certain people handle
certain countries, topics, etc., but also certain topics are dominated by
certain countries and vice versa. So while the people provide the linkage
to
a lot of the data, I know that both country and topic are also
inter-related
and I foresee a need to slice my data by these as well.

So to make this even more clear, given your list:
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes

What if a topic can have 1-m countries and vice versa? Should I combine
Person, Countries, Topics into a master junction table then?

I appreciate your patience. I suspect I'm being a bit thick-headed here.
You've been very helpful.

Regards,
Keith

"Jeff Boyce" wrote:

Keith

I may still not understand the real-world situation you are dealing
with.

If the ONLY common denominator is Person, and you are relating
Person-to-X,
Person-to-Y, etc., you don't need a master table. A query that
connects the
three tables would show the X, the Y and the Z related to a person.

The concept of a "unique" combination is something only you can define.
Forget about Access for a moment and describe the real-world (yours).

A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes

This is what I got from your post. You fill in the rest...

--
Regards

Jeff Boyce
MS Office/Access MVP

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Jeff, John - appreciate you continuing to engage. I fear you think your
advice is falling on deaf ears, so let me be clear about what I've taken
away. You've advised to think, independent of Access, about the relationships
I have and how they work in real world. Good and necessary step. Here's what
I have convinced myself of:

I have 4 variables all inter-related: Person, country, topic, info type and,
per Jeff's list ...
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes
Similarly ...
A topic can have 1-m Countries
A topic can have 1-m People
A topic can have 1-m InfoTypes
etc.

In my real world situation, it is reasonable for me to slice my entire data
set by any of these 4. I began the databasing process by creating junction
tables of the pairwise permutations of each of these 4 tables. My question
boils down to whether a junction tbl of junction tbls is necessary to
complete matching my real world scenario to the database structure.

(Of course, the easy answer is to try both ways!)

As I've never had to consider anything more complicated than a two-table
junction table in Access, my intuition is not serving me well here for a
4-table junction case.

Hope you are having a great day.

Keith

Could you describe this in more detail? For instance, does the person
ACTUALLY have infotypes, or is it the topic related to a person that has
infotypes? I can't for the life of me see where a person could have as a
characteristic an infotype.

Why would a topic have a country? Maybe if you went more into detail about
the actual purpose of your database, we can help you find the best design
for it, but with you talking in the abstract, it doesn't make much sense.

-Amy

"Keith McCarron" wrote in message
...
Jeff, John - appreciate you continuing to engage. I fear you think your
advice is falling on deaf ears, so let me be clear about what I've taken
away. You've advised to think, independent of Access, about the
relationships
I have and how they work in real world. Good and necessary step. Here's
what
I have convinced myself of:

I have 4 variables all inter-related: Person, country, topic, info type
and,
per Jeff's list ...
A Person can have 1-m Countries
A Person can have 1-m Topics
A Person can have 1-m InfoTypes
Similarly ...
A topic can have 1-m Countries
A topic can have 1-m People
A topic can have 1-m InfoTypes
etc.

In my real world situation, it is reasonable for me to slice my entire
data
set by any of these 4. I began the databasing process by creating junction
tables of the pairwise permutations of each of these 4 tables. My question
boils down to whether a junction tbl of junction tbls is necessary to
complete matching my real world scenario to the database structure.

(Of course, the easy answer is to try both ways!)

As I've never had to consider anything more complicated than a two-table
junction table in Access, my intuition is not serving me well here for a
4-table junction case.

Hope you are having a great day.

Keith