junction tables for 3 many to many relationships

Imagine 3 tables: A, B, C, with many-to-many relationships among all pairs
(AB, AC, BC). Three junction tables can be used to deal with the pairwise
relationships. Question: Would a single junction table work that has
primary keys from each of the 3 tables?

Insufficient information.

There may be scenarios where a single table with 3 foreign keys would be
usuable, especially if you use the Validation Rule of the table (not fields)
to require that at least 2 foreign keys are present. That scenario could be
attractive rather than trying to UNION the 3 junction tables regularly, or
if there could actually be a meaningful combination of all 3 (where all 3
foreign keys are present).

But there are many other approaches. Example: a single table in place of A,
B, and C, with a table identifying a "relationship" that exists, with a
related table containing a foreign key record for each record of the
original table that is invovled in this "relationship". The advantage of
this approach is that there is only a single field to examine to find all
the relationship that one of the original records has. (That may be useless
for what you are doing: we just don't know.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LAF" wrote in message
...
Imagine 3 tables: A, B, C, with many-to-many relationships among all pairs
(AB, AC, BC). Three junction tables can be used to deal with the pairwise
relationships. Question: Would a single junction table work that has
primary keys from each of the 3 tables?

Additional information is indeed required. This information can be generic.
There are 3 fields (ID, Place, Time) such that ID and Place, ID and
Date-Time, Place and Date-Time can have many-to-many relationships. In a
single table with these 3 fields, the 3 fields as a primary key "capture" the
relationships that the 3 field junction table would have. With one
additional field (Happening) in this single table, the table would be useful
for many queries. ID and Place would be primary keys in their own tables.
The other table(s) in the database minimally require ID and Date-Time as
primary key, with numerous fields about the ID at each Date-Time. Place
would come from the Happening table.

Would this work? The principle here is whether a multi-field primary key
can capture the many-to-many relationships that those fields contain.

Thanks,

LAF

"Allen Browne" wrote:

Insufficient information.

There may be scenarios where a single table with 3 foreign keys would be
usuable, especially if you use the Validation Rule of the table (not fields)
to require that at least 2 foreign keys are present. That scenario could be
attractive rather than trying to UNION the 3 junction tables regularly, or
if there could actually be a meaningful combination of all 3 (where all 3
foreign keys are present).

But there are many other approaches. Example: a single table in place of A,
B, and C, with a table identifying a "relationship" that exists, with a
related table containing a foreign key record for each record of the
original table that is invovled in this "relationship". The advantage of
this approach is that there is only a single field to examine to find all
the relationship that one of the original records has. (That may be useless
for what you are doing: we just don't know.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LAF" wrote in message
...
Imagine 3 tables: A, B, C, with many-to-many relationships among all pairs
(AB, AC, BC). Three junction tables can be used to deal with the pairwise
relationships. Question: Would a single junction table work that has
primary keys from each of the 3 tables?

A table of happenings with 3 foreign keys - ID, Place, and Time - sounds
fine.

The 3-field primary key would be suitable if:
- all 3 are always required, and
- there could never be 2 IDs at the same place and time (for example), and
- there are not further related tables that would then need a 3-field
foreign key, where a single field key would be better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LAF" wrote in message
...
Additional information is indeed required. This information can be
generic.
There are 3 fields (ID, Place, Time) such that ID and Place, ID and
Date-Time, Place and Date-Time can have many-to-many relationships. In a
single table with these 3 fields, the 3 fields as a primary key "capture"
the
relationships that the 3 field junction table would have. With one
additional field (Happening) in this single table, the table would be
useful
for many queries. ID and Place would be primary keys in their own tables.
The other table(s) in the database minimally require ID and Date-Time as
primary key, with numerous fields about the ID at each Date-Time. Place
would come from the Happening table.

Would this work? The principle here is whether a multi-field primary key
can capture the many-to-many relationships that those fields contain.

Thanks,

LAF

"Allen Browne" wrote:

Insufficient information.

There may be scenarios where a single table with 3 foreign keys would be
usuable, especially if you use the Validation Rule of the table (not
fields)
to require that at least 2 foreign keys are present. That scenario could
be
attractive rather than trying to UNION the 3 junction tables regularly,
or
if there could actually be a meaningful combination of all 3 (where all 3
foreign keys are present).

But there are many other approaches. Example: a single table in place of
A,
B, and C, with a table identifying a "relationship" that exists, with a
related table containing a foreign key record for each record of the
original table that is invovled in this "relationship". The advantage of
this approach is that there is only a single field to examine to find all
the relationship that one of the original records has. (That may be
useless
for what you are doing: we just don't know.)


"LAF" wrote in message
...
Imagine 3 tables: A, B, C, with many-to-many relationships among all
pairs
(AB, AC, BC). Three junction tables can be used to deal with the
pairwise
relationships. Question: Would a single junction table work that has
primary keys from each of the 3 tables?

The 3-field primary key (ID, Place, Date-Time) serves to identify unique
records, but frequently several ID's occur at the same Date-Time at the same
Place. It is not clear to me why the condition of no 2 ID's at the same
Place at the same Date-Time is necessary.

Thanks, LAF

"Allen Browne" wrote:

A table of happenings with 3 foreign keys - ID, Place, and Time - sounds
fine.

The 3-field primary key would be suitable if:
- all 3 are always required, and
- there could never be 2 IDs at the same place and time (for example), and
- there are not further related tables that would then need a 3-field
foreign key, where a single field key would be better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LAF" wrote in message
...
Additional information is indeed required. This information can be
generic.
There are 3 fields (ID, Place, Time) such that ID and Place, ID and
Date-Time, Place and Date-Time can have many-to-many relationships. In a
single table with these 3 fields, the 3 fields as a primary key "capture"
the
relationships that the 3 field junction table would have. With one
additional field (Happening) in this single table, the table would be
useful
for many queries. ID and Place would be primary keys in their own tables.
The other table(s) in the database minimally require ID and Date-Time as
primary key, with numerous fields about the ID at each Date-Time. Place
would come from the Happening table.

Would this work? The principle here is whether a multi-field primary key
can capture the many-to-many relationships that those fields contain.

Thanks,

LAF

"Allen Browne" wrote:

Insufficient information.

There may be scenarios where a single table with 3 foreign keys would be
usuable, especially if you use the Validation Rule of the table (not
fields)
to require that at least 2 foreign keys are present. That scenario could
be
attractive rather than trying to UNION the 3 junction tables regularly,
or
if there could actually be a meaningful combination of all 3 (where all 3
foreign keys are present).

But there are many other approaches. Example: a single table in place of
A,
B, and C, with a table identifying a "relationship" that exists, with a
related table containing a foreign key record for each record of the
original table that is invovled in this "relationship". The advantage of
this approach is that there is only a single field to examine to find all
the relationship that one of the original records has. (That may be
useless
for what you are doing: we just don't know.)


"LAF" wrote in message
...
Imagine 3 tables: A, B, C, with many-to-many relationships among all
pairs
(AB, AC, BC). Three junction tables can be used to deal with the
pairwise
relationships. Question: Would a single junction table work that has
primary keys from each of the 3 tables?

"Allen Browne" wrote in
:

The 3-field primary key would be suitable if:
- all 3 are always required, and
- there could never be 2 IDs at the same place and time (for example),
and - there are not further related tables that would then need a
3-field foreign key, where a single field key would be better.


Sorry to butt in, but isn't the argument here dependent on the semantics
rather than what is allowable?

For example, a business that allows People to bring their individual Skills
to particular Projects could have:

Person Skill Project
====== ===== =======
Peter Design Website
Peter Italian Website
Derek JScript Website
Derek Italian Help files
Peter Design Help files

so that Peter does design and translation on the website, but does not do
any translation on the help files, etc. This is, FWIW, straightforward
fifth normal form (I think!).

On the other hand, the database might only be concerned with skills rather
than who-does-what. In this case a set of three relationships is required:

PeopleHaveSkills
Person Skill
====== =====
Peter Design
Peter Italian
Derek JScript
Derek Italian

PeopleOnProjects
Person Project
====== =======
Peter Website
Peter Help files
Derek Help files
Derek Website

ProjectsNeedSkills
Project Skill
======= =====
Website Design
Website Italian
Website JScript
Help files Design
Help files Italian


Although these two designs are in roughly the same area, they represent
different business needs, and are thus completely non-equivalent. Surely
the OP's real problem is establishing his actual needs.

All the best


Tim F

Butting in is encouraged because we are going to the heart of data base
design for users. Perhaps one of the considerations that goes beyond
semantics are the queries that might be performed. For example, which ID's
were involved in 2 or more Places? Which place had fewest ID's? Which ID's
were carried over from a previous year, which ID's were newly established in
a given year and were detected again in that year? Many queries involve
looping over recordsets with counters. It is in this spirit that I am
concerned about 3-field primary keys that at least identify unique records.

However, I am still a little confused about the condition that there could
never be 2 ID's at the same Place at the same Date-Time, or any other
analogous situations. Perhaps it would be useful to specify how these
violated conditions can mess up use of the database, even when each other
table in the database has a unique primary key made up of one or two fields
of the 3-field primary key that identifies unique records.

Thanks for the participation,

LAF

"Tim Ferguson" wrote:

"Allen Browne" wrote in
:

The 3-field primary key would be suitable if:
- all 3 are always required, and
- there could never be 2 IDs at the same place and time (for example),
and - there are not further related tables that would then need a
3-field foreign key, where a single field key would be better.


Sorry to butt in, but isn't the argument here dependent on the semantics
rather than what is allowable?

For example, a business that allows People to bring their individual Skills
to particular Projects could have:

Person Skill Project
====== ===== =======
Peter Design Website
Peter Italian Website
Derek JScript Website
Derek Italian Help files
Peter Design Help files

so that Peter does design and translation on the website, but does not do
any translation on the help files, etc. This is, FWIW, straightforward
fifth normal form (I think!).

On the other hand, the database might only be concerned with skills rather
than who-does-what. In this case a set of three relationships is required:

PeopleHaveSkills
Person Skill
====== =====
Peter Design
Peter Italian
Derek JScript
Derek Italian

PeopleOnProjects
Person Project
====== =======
Peter Website
Peter Help files
Derek Help files
Derek Website

ProjectsNeedSkills
Project Skill
======= =====
Website Design
Website Italian
Website JScript
Help files Design
Help files Italian


Although these two designs are in roughly the same area, they represent
different business needs, and are thus completely non-equivalent. Surely
the OP's real problem is establishing his actual needs.

All the best


Tim F

LAF, I'm really not clear what is the issue here.

The condition that the combination of ID + Place + Date/Time must be unique
swings on that combination being primary key. If there could validly be 2 of
whatever the ID is, at the same time, in the same place, then the
combination is not suitable as a p.k.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LAF" wrote in message
...
The 3-field primary key (ID, Place, Date-Time) serves to identify unique
records, but frequently several ID's occur at the same Date-Time at the
same
Place. It is not clear to me why the condition of no 2 ID's at the same
Place at the same Date-Time is necessary.

Thanks, LAF

"Allen Browne" wrote:

A table of happenings with 3 foreign keys - ID, Place, and Time - sounds
fine.

The 3-field primary key would be suitable if:
- all 3 are always required, and
- there could never be 2 IDs at the same place and time (for example),
and
- there are not further related tables that would then need a 3-field
foreign key, where a single field key would be better.


"LAF" wrote in message
...
Additional information is indeed required. This information can be
generic.
There are 3 fields (ID, Place, Time) such that ID and Place, ID and
Date-Time, Place and Date-Time can have many-to-many relationships. In
a
single table with these 3 fields, the 3 fields as a primary key
"capture"
the
relationships that the 3 field junction table would have. With one
additional field (Happening) in this single table, the table would be
useful
for many queries. ID and Place would be primary keys in their own
tables.
The other table(s) in the database minimally require ID and Date-Time
as
primary key, with numerous fields about the ID at each Date-Time.
Place
would come from the Happening table.

Would this work? The principle here is whether a multi-field primary
key
can capture the many-to-many relationships that those fields contain.

Thanks,

LAF

"Allen Browne" wrote:

Insufficient information.

There may be scenarios where a single table with 3 foreign keys would
be
usuable, especially if you use the Validation Rule of the table (not
fields)
to require that at least 2 foreign keys are present. That scenario
could
be
attractive rather than trying to UNION the 3 junction tables
regularly,
or
if there could actually be a meaningful combination of all 3 (where
all 3
foreign keys are present).

But there are many other approaches. Example: a single table in place
of
A,
B, and C, with a table identifying a "relationship" that exists, with
a
related table containing a foreign key record for each record of the
original table that is invovled in this "relationship". The advantage
of
this approach is that there is only a single field to examine to find
all
the relationship that one of the original records has. (That may be
useless
for what you are doing: we just don't know.)


"LAF" wrote in message
...
Imagine 3 tables: A, B, C, with many-to-many relationships among all
pairs
(AB, AC, BC). Three junction tables can be used to deal with the
pairwise
relationships. Question: Would a single junction table work that
has
primary keys from each of the 3 tables?

=?Utf-8?B?TEFG?= wrote in
:

However, I am still a little confused about the condition that there
could never be 2 ID's at the same Place at the same Date-Time, or any
other analogous situations. Perhaps it would be useful to specify how
these violated conditions can mess up use of the database, even when
each other table in the database has a unique primary key made up of
one or two fields of the 3-field primary key that identifies unique
records.



As I remember, this thread began with "Tables A, B, and C". Although
various real-world scenarios have been put forward as examples, I don't
think we've been let into what you are actually modelling; and furthermore
I don't think it's possible to discuss general cases sensibly in any depth.

The 3NF and 5NF solutions are simply solutions to different problems, and
it makes as much sense to compare them as it does to ask, "What is the best
way to sail across the Atlantic - carry more coal or eat more limes?"

All the best


Tim F

Thanks to Allen and Tim for pursuing this. The 3 field primary key
absolutely identifies a unique record. Yet, several combinations of two of
these fields have many to many relationships. These a

The same ID may be in different sites, but at different times.

Different ID's may be in different sites, but at the same time.

By the way, the ID's are aluminum bands placed on the legs of birds.
Banding operations can occur at different sites on the same day, and the time
is when we capture a bird in a mist-net. Several birds can be caught in a
mist-net at the same time.

My original question still stands. Do I need to worry about many to many
relationships among subsets of the 3-field primary key if the 3-filed key
identifies unique records?


Many of the tables in the database deal with lots of measurements we take on
the birds. For these tables, just ID and Date-Time are sufficient as primary
key to identify unique records.

I may be making a mountain out of a molehill, but no book on access deals
with junction tables for more than a single many to many relationship. I am
viewing my 3-field primary key as a junction table because of inferred many
to many relationships among some of the fields that are part of the primary
key.

Thanks, LAF


"Allen Browne" wrote:

LAF, I'm really not clear what is the issue here.

The condition that the combination of ID + Place + Date/Time must be unique
swings on that combination being primary key. If there could validly be 2 of
whatever the ID is, at the same time, in the same place, then the
combination is not suitable as a p.k.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LAF" wrote in message
...
The 3-field primary key (ID, Place, Date-Time) serves to identify unique
records, but frequently several ID's occur at the same Date-Time at the
same
Place. It is not clear to me why the condition of no 2 ID's at the same
Place at the same Date-Time is necessary.

Thanks, LAF

"Allen Browne" wrote:

A table of happenings with 3 foreign keys - ID, Place, and Time - sounds
fine.

The 3-field primary key would be suitable if:
- all 3 are always required, and
- there could never be 2 IDs at the same place and time (for example),
and
- there are not further related tables that would then need a 3-field
foreign key, where a single field key would be better.


"LAF" wrote in message
...
Additional information is indeed required. This information can be
generic.
There are 3 fields (ID, Place, Time) such that ID and Place, ID and
Date-Time, Place and Date-Time can have many-to-many relationships. In
a
single table with these 3 fields, the 3 fields as a primary key
"capture"
the
relationships that the 3 field junction table would have. With one
additional field (Happening) in this single table, the table would be
useful
for many queries. ID and Place would be primary keys in their own
tables.
The other table(s) in the database minimally require ID and Date-Time
as
primary key, with numerous fields about the ID at each Date-Time.
Place
would come from the Happening table.

Would this work? The principle here is whether a multi-field primary
key
can capture the many-to-many relationships that those fields contain.

Thanks,

LAF

"Allen Browne" wrote:

Insufficient information.

There may be scenarios where a single table with 3 foreign keys would
be
usuable, especially if you use the Validation Rule of the table (not
fields)
to require that at least 2 foreign keys are present. That scenario
could
be
attractive rather than trying to UNION the 3 junction tables
regularly,
or
if there could actually be a meaningful combination of all 3 (where
all 3
foreign keys are present).

But there are many other approaches. Example: a single table in place
of
A,
B, and C, with a table identifying a "relationship" that exists, with
a
related table containing a foreign key record for each record of the
original table that is invovled in this "relationship". The advantage
of
this approach is that there is only a single field to examine to find
all
the relationship that one of the original records has. (That may be
useless
for what you are doing: we just don't know.)


"LAF" wrote in message
...
Imagine 3 tables: A, B, C, with many-to-many relationships among all
pairs
(AB, AC, BC). Three junction tables can be used to deal with the
pairwise
relationships. Question: Would a single junction table work that
has
primary keys from each of the 3 tables?