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

[Keith McCarron]

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

 

[Jeff Boyce]

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)

 

[Keith McCarron]

Keith

Given the three junctions tables you mentioned,
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 Boyce]

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...

 

[Keith McCarron]

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

 

[John Nurick]

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

 

[Keith McCarron]

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

 

[Jeff Boyce]

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.

 

[Keith McCarron]

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

 

[Amy Blankenship]

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]

Fair point, Amy, b/c I have been vague up until this point, hoping I
wouldn't have to get too into it.

I'm working this within a defense/intel context. So, yes, certain people
work with specific info types as their area of analysis. Similarly, certain
topics apply to certain countries. Certain infotypes lend themselves to
different topics and countries. Etc. I'm building the database to show how
much time/effort is being spent per variable. I want to ultimately create a
4-d query (kind of like a 4-d cross-tab) of each slice I take, further
parsing and drilling into the data.

E.g.,

For a given topic, how much effort per country, per infotype, and per person.
For a given person, how much effort per country, per infotype and per topic.
For a given infotype, how much effort per person, per topic, and per country.
For a given country, how much effort per person, per topic, and per infotype.

I apologize for leaving this out, especially if this context was crucial to
my framing of the problem. Happy to answer any follow-up questions.

Regards,
Keith

 

[Amy Blankenship]

I'm not ignoring your response, I'm thinking further.

-Amy

 

[Keith McCarron]

No worries. I can only be appreciative of that!

Keith

 

[Amy Blankenship]

OK, here's what I think. You actually want to sum up the "efforts" that
combine to become the level of effort for a specific combination.

So what you actually need is an "efforts" table that allows you to insert
the key for each possible thing the effort could apply to, where you insert
the primary key of one or more of the fields to that particular effort. If
the level of effort will only make sense in certain combinations, your forms
could validate those combinations. So, for instance, if the level of effort
for a country makes no sense unless it also applies to a topic and
information type, you'd want to insist on at least that amount of data. If
it makes no sense without a person to apply to, you should require all.

Then you could use queries to sum the individual efforts any way you need.

Now, you might need additional relationships defined to make your forms
function correctly. For instance, if one person can only have InfoType A or
InfoType B, then a table containing that relationship would allow you to
filter infotype based on person. Now, if that person can only have InfoType
A in country A or InfoType B in country b, you might want to have a table
that defines that relationship. However, the more relationships you define
like this, the more data entry is needed, to define what is possible in a
combobox on a form. So you get accuracy, but you add data entry headaches.

Helps?

-Amy

 

[Keith McCarron]

Amy,

I have to chew on that a bit. You actually have several ideas in there, so I
may try several of them. I'm not at the form making stage yet, as I'm doing
all of the data entry myself and find the tables easier, for now. I've
resigned myself to a lot of data entry. As I complete each table, I'll check
to see what conclusions I can draw from the current level of joining. If
you're right, I'll ultimately be combining all of the "effort" contribution
tables into one.

Thanks for noodling this and offering some ideas. I guess I have to bring it
home now!

Keith

 

[Amy Blankenship]

You're welcome

-Amy

Amy,

I have to chew on that a bit. You actually have several ideas in there, so
I
may try several of them. I'm not at the form making stage yet, as I'm
doing
all of the data entry myself and find the tables easier, for now. I've
resigned myself to a lot of data entry. As I complete each table, I'll
check
to see what conclusions I can draw from the current level of joining. If
you're right, I'll ultimately be combining all of the "effort"
contribution
tables into one.

Thanks for noodling this and offering some ideas. I guess I have to bring
it
home now!

Keith

 

[John Nurick]

Amy's right: you probably need to take a step back and think even harder
about the subset of the real world that you are modelling and the
entities you'll need.

So far it sounds as if you have "real" entities including
People
Topics
Countries

Maybe you also need an entity such as "program" or "project" or
"investigation"; a Program might involve one or more topics, zero or
more countries, and zero or more people.

I'm not sure whether "effort" is an entity in its own right or merely an
attribute of a relationship: e.g. if Jim is spending all his time on
Project X the fact would be stored in a record in ProjectsAndPeople:

Project, Person, Effort, StartDate, EndDate
X, Jim, 100%, ...