A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multiple Many-To-Many Tables



 
 
Thread Tools Display Modes
  #1  
Old May 13th, 2004, 04:22 AM
Tom
external usenet poster
 
Posts: n/a
Default Multiple Many-To-Many Tables

Hello,

I am familiar with using a junction table for a "many-to-many" relationship.

However, I'm not exactly sure how I could string multiple "many-to-many" or
junction tables into an architecture.

Does anyone have a suggestion?

For instance, I may have members that are assigned to many projects (and
each project many members). But I also have boards that have multiple
priorities and multiple priorities are linked to multiple organizations.

Currently, I'm myself trying to figure out the relationships between all the
entities. Again, knowing how I could use (with a generic example) multiple
junction tables would be great advice.

Thanks,
Tom



  #2  
Old May 13th, 2004, 06:12 AM
TC
external usenet poster
 
Posts: n/a
Default Multiple Many-To-Many Tables


"Tom" wrote in message
...
Hello,

I am familiar with using a junction table for a "many-to-many"

relationship.

However, I'm not exactly sure how I could string multiple "many-to-many"

or
junction tables into an architecture.

Does anyone have a suggestion?


Here are two examples.

1. If A is many-to-many (m:m) with B, you need a junction table AB (or
whatever) - as you know. Similarly, if some other table C is m:m to D, you
need another junction table CD. Ditto for as many other tables & junction
pairs that are required.

2. Say you have Person, Club, and PersonClub. (The latter is the junction
table to allow a person to belong to many clubs, and a club to have many
persons.) Say you need to record each consecutive period that a person was a
member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for each
one entry in the PersonClub table, there are many entries in a table of
date-ranges:

tblPersonClub
PersonID ( composite )
ClubID ( primary key )

DateRange
PersonID ( composite )
ClubID ( primary )
DateFrom ( key )
DateTo

In the latter example:

- Person : Club is m:m ) the normal
- Person : PersonClub is 1:m ) junction table
- Club : PersonClub is 1:m ) structure

- PersonClub : DateRange is 1:m

Do those help?


For instance, I may have members that are assigned to many projects (and
each project many members). But I also have boards that have multiple
priorities and multiple priorities are linked to multiple organizations.

Currently, I'm myself trying to figure out the relationships between all

the
entities. Again, knowing how I could use (with a generic example)

multiple
junction tables would be great advice.

Thanks,
Tom





  #3  
Old May 13th, 2004, 04:54 PM
Tom
external usenet poster
 
Posts: n/a
Default Multiple Many-To-Many Tables

TC:

Thanks for the info.

Not sure if I entirely understand your schema.


I got the 3 tables:
tblPersonClub is the junction which has 2 Fields (Long Integer) that are
linked to the primary keys of the other 2 tables: tblPerson & tblClub

So here are my questions:
1. What did you mean by "composite"?
2. What is the "DateRange". I assume it's a field, right?
3. ClubID is the primary but DateFrom = "key"... not sure what you mean by
that?
4. Also, I thought I would end up with 6 tables. 2 pairs of 2 tables
linked via their junction tables. And then link the 2 junction tables to
one another... I'm probably wrong about this, right?


Thanks for any additional info,
Tom




"TC" wrote in message
...

"Tom" wrote in message
...
Hello,

I am familiar with using a junction table for a "many-to-many"

relationship.

However, I'm not exactly sure how I could string multiple "many-to-many"

or
junction tables into an architecture.

Does anyone have a suggestion?


Here are two examples.

1. If A is many-to-many (m:m) with B, you need a junction table AB (or
whatever) - as you know. Similarly, if some other table C is m:m to D, you
need another junction table CD. Ditto for as many other tables & junction
pairs that are required.

2. Say you have Person, Club, and PersonClub. (The latter is the junction
table to allow a person to belong to many clubs, and a club to have many
persons.) Say you need to record each consecutive period that a person was

a
member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for

each
one entry in the PersonClub table, there are many entries in a table of
date-ranges:

tblPersonClub
PersonID ( composite )
ClubID ( primary key )

DateRange
PersonID ( composite )
ClubID ( primary )
DateFrom ( key )
DateTo

In the latter example:

- Person : Club is m:m ) the normal
- Person : PersonClub is 1:m ) junction table
- Club : PersonClub is 1:m ) structure

- PersonClub : DateRange is 1:m

Do those help?


For instance, I may have members that are assigned to many projects (and
each project many members). But I also have boards that have multiple
priorities and multiple priorities are linked to multiple organizations.

Currently, I'm myself trying to figure out the relationships between all

the
entities. Again, knowing how I could use (with a generic example)

multiple
junction tables would be great advice.

Thanks,
Tom







  #4  
Old May 13th, 2004, 08:26 PM
rpw
external usenet poster
 
Posts: n/a
Default Multiple Many-To-Many Tables

Hi Tom,

it's been a while since TC answered, so I'll jump in here Comments in-line...

Not sure if I entirely understand your schema.


I got the 3 tables:
tblPersonClub is the junction which has 2 Fields (Long Integer) that are
linked to the primary keys of the other 2 tables: tblPerson & tblClub

So here are my questions:
1. What did you mean by "composite"?

Instead of one field of the table being designated as the PK, you can select multiple fields and designate the combination as a PK (hence "composite" ) It is difficult to illustrate here, but the intent is that two or three fields are grouped together as a "composite primary key" Try looking at TC's original post again with that in mind.

2. What is the "DateRange". I assume it's a field, right?

I believe he meant DateRange as a table (maybe tblDateRange is better) and the sub-listings are the fields.

3. ClubID is the primary but DateFrom = "key"... not sure what you mean by
that?

See the answer to number one above.

4. Also, I thought I would end up with 6 tables. 2 pairs of 2 tables
linked via their junction tables. And then link the 2 junction tables to
one another... I'm probably wrong about this, right?

TC's four table sample and explanation should be sufficient to accomplish what you asked for - stringing multiple m:m relationships together. Look at his tables/relations again:

tblPerson (has 1:m to tblPersonClub)
tblClub (has 1:m to tblPersonClub)
tblPersonClub (is the normal junction table structure)
tblDateRange (has 1:m to tblPersonClub)

In your first post you said:
For instance, I may have members that are assigned to many projects (and
each project many members). But I also have boards that have multiple
priorities and multiple priorities are linked to multiple organizations.


For what you state specifically, you could have these tables:

tblMembers
tblProjects
tblMemberProjects

and

tblBoards
tblOrganizations
tblBoardOrganization (or tblPriorities - this is your junction table)

Do you also want m:m Members to Boards, m:m Members to Organization, and m:m Members to Priorities? (you didn't say that's what you wanted, so I'm just guessing here) Do Projects have Priorities too? Knowing what you know, what do you think you would need to handle the m:m relationships listed here?

hope this helps you

rpw


Thanks for any additional info,
Tom




"TC" wrote in message
...
"Tom" wrote in message

...
Hello,
I am familiar with using a junction table for a "many-to-many"

relationship.
However, I'm not exactly sure how I could string multiple "many-to-many"

or
junction tables into an architecture.
Does anyone have a suggestion?

Here are two examples.
1. If A is many-to-many (m:m) with B, you need a junction table AB (or

whatever) - as you know. Similarly, if some other table C is m:m to D, you
need another junction table CD. Ditto for as many other tables & junction
pairs that are required.
2. Say you have Person, Club, and PersonClub. (The latter is the junction

table to allow a person to belong to many clubs, and a club to have many
persons.) Say you need to record each consecutive period that a person was

a
member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for

each
one entry in the PersonClub table, there are many entries in a table of
date-ranges:
tblPersonClub

PersonID ( composite )
ClubID ( primary key )
DateRange

PersonID ( composite )
ClubID ( primary )
DateFrom ( key )
DateTo
In the latter example:
- Person : Club is m:m ) the normal

- Person : PersonClub is 1:m ) junction table
- Club : PersonClub is 1:m ) structure
- PersonClub : DateRange is 1:m
Do those help?
For instance, I may have members that are assigned to many projects (and

each project many members). But I also have boards that have multiple
priorities and multiple priorities are linked to multiple organizations.
Currently, I'm myself trying to figure out the relationships between all

the
entities. Again, knowing how I could use (with a generic example)

multiple
junction tables would be great advice.
Thanks,

Tom

  #5  
Old May 14th, 2004, 02:32 AM
Tom
external usenet poster
 
Posts: n/a
Default Multiple Many-To-Many Tables

RPW:

Thanks for "jumping in" and providing me feedback.

Okay, I have attempted to recreate the table design that TC suggested. I'm
not too
sure though if I understand the concept of the "spliced in" tblDates.

As of now, the junction table (tblPersonClub) has only 2 fields:
1. PersonID (Number; Long Integer)
2. ClubID (Number; Long Integer)

Both of these fields are primary keys (or I should say now composite keys,
right?).

In tblDateRange, I have the following fields:
1. ClubID
2. PersonID
3. DateFrom

what are the data types of the 1st two fields? Are they also "Numbers;
Long Integers" or is one of them an "Autonumber".

Currently, no matter how I set them, I get the "Indeterminate Relationship"
in the Edit Relationship Window so I can not join the tblDateRange into the
junction table.



Okay, once I got this to work, I somehow need to get the actual table design
to work as well. Here, I really believe I need some serious help. I'm
completely lost (and don't have all the information either to provide the
Newsgroups w/ some real information... ugh).

Hopefully, I could rely on some of your expertise (and interpretion) as to
how the design can be achieved.

Here are the tables:

- Organization
- Boards
- Employees
- Tasks
- Priorities
- Projects
- Budget Categories
- Skill Sets

Again, I don't have all the exact information myself as to how the tables
should be joined. Maybe, there's even a chance that I don't need all the
tables listed above.

But, let me try to provide some more information:

Organization:
1. There is 1 organization
2. The organization has multiple boards
3. The organizaton has multiple projects
4. There are multiple tasks that either result from the boards or the
projects
5. Naturally, there are multipe members in the organization that can be
assigned to a) boards, b) projects ) boards and projects

Boards:
1. Multiple boards will have multiple members
2. As a result of board meetings, there will be multiple tasks
3. These tasks will have different priorities
4. The tasks will be subsidized via different funds (budget categories)

Employees:
1. Multiple employees can be assigned to multiple boards
2. Multiple employees can be assigned to multiple projects
3. All employees have differnt skills sets (multiple skills sets)
4. The employees will be assigned to different tasks


Budget Categories, Skill Sets have been listed in the description above.
Not sure how else they could be further described here.


Again, if I could get some ideas as to how best construct this, I would
really appreciate it!!! Any help would really help me in my job. THANKS!

Please don't hesitate to post additional questions if I didn't provide
sufficient information.


Thanks so much,

Tom












"rpw" wrote in message
news
Hi Tom,

it's been a while since TC answered, so I'll jump in here Comments
in-line...

Not sure if I entirely understand your schema.


I got the 3 tables:
tblPersonClub is the junction which has 2 Fields (Long Integer) that

are
linked to the primary keys of the other 2 tables: tblPerson & tblClub

So here are my questions:
1. What did you mean by "composite"?

Instead of one field of the table being designated as the PK, you can

select multiple fields and designate the combination as a PK (hence
"composite" ) It is difficult to illustrate here, but the intent is that
two or three fields are grouped together as a "composite primary key" Try
looking at TC's original post again with that in mind.

2. What is the "DateRange". I assume it's a field, right?

I believe he meant DateRange as a table (maybe tblDateRange is better) and

the sub-listings are the fields.

3. ClubID is the primary but DateFrom = "key"... not sure what you

mean by
that?

See the answer to number one above.

4. Also, I thought I would end up with 6 tables. 2 pairs of 2

tables
linked via their junction tables. And then link the 2 junction

tables to
one another... I'm probably wrong about this, right?

TC's four table sample and explanation should be sufficient to accomplish

what you asked for - stringing multiple m:m relationships together. Look at
his tables/relations again:

tblPerson (has 1:m to tblPersonClub)
tblClub (has 1:m to tblPersonClub)
tblPersonClub (is the normal junction table structure)
tblDateRange (has 1:m to tblPersonClub)

In your first post you said:
For instance, I may have members that are assigned to many

projects (and
each project many members). But I also have boards that have

multiple
priorities and multiple priorities are linked to multiple

organizations.

For what you state specifically, you could have these tables:

tblMembers
tblProjects
tblMemberProjects

and

tblBoards
tblOrganizations
tblBoardOrganization (or tblPriorities - this is your junction table)

Do you also want m:m Members to Boards, m:m Members to Organization, and

m:m Members to Priorities? (you didn't say that's what you wanted, so I'm
just guessing here) Do Projects have Priorities too? Knowing what you
know, what do you think you would need to handle the m:m relationships
listed here?

hope this helps you

rpw


Thanks for any additional info,
Tom




"TC" wrote in message
...
"Tom" wrote in message

...
Hello,
I am familiar with using a junction table for a "many-to-many"

relationship.
However, I'm not exactly sure how I could string multiple

"many-to-many"
or
junction tables into an architecture.
Does anyone have a suggestion?
Here are two examples.
1. If A is many-to-many (m:m) with B, you need a junction table AB

(or
whatever) - as you know. Similarly, if some other table C is m:m to

D, you
need another junction table CD. Ditto for as many other tables &

junction
pairs that are required.
2. Say you have Person, Club, and PersonClub. (The latter is the

junction
table to allow a person to belong to many clubs, and a club to have

many
persons.) Say you need to record each consecutive period that a

person was
a
member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case,

for
each
one entry in the PersonClub table, there are many entries in a

table of
date-ranges:
tblPersonClub

PersonID ( composite )
ClubID ( primary key )
DateRange

PersonID ( composite )
ClubID ( primary )
DateFrom ( key )
DateTo
In the latter example:
- Person : Club is m:m ) the normal

- Person : PersonClub is 1:m ) junction table
- Club : PersonClub is 1:m ) structure
- PersonClub : DateRange is 1:m
Do those help?
For instance, I may have members that are assigned to many

projects (and
each project many members). But I also have boards that have

multiple
priorities and multiple priorities are linked to multiple

organizations.
Currently, I'm myself trying to figure out the relationships

between all
the
entities. Again, knowing how I could use (with a generic

example)
multiple
junction tables would be great advice.
Thanks,
Tom



  #6  
Old May 14th, 2004, 06:00 AM
TC
external usenet poster
 
Posts: n/a
Default Multiple Many-To-Many Tables

Yes, you got me spot-on :-)

I can only get on the web once a day, & it gets difficult towards week-ends,
cos I have other obligations.

Cheers,
TC


"rpw" wrote in message
news
Hi Tom,

it's been a while since TC answered, so I'll jump in here Comments
in-line...

Not sure if I entirely understand your schema.


I got the 3 tables:
tblPersonClub is the junction which has 2 Fields (Long Integer) that

are
linked to the primary keys of the other 2 tables: tblPerson & tblClub

So here are my questions:
1. What did you mean by "composite"?

Instead of one field of the table being designated as the PK, you can

select multiple fields and designate the combination as a PK (hence
"composite" ) It is difficult to illustrate here, but the intent is that
two or three fields are grouped together as a "composite primary key" Try
looking at TC's original post again with that in mind.

2. What is the "DateRange". I assume it's a field, right?

I believe he meant DateRange as a table (maybe tblDateRange is better) and

the sub-listings are the fields.

3. ClubID is the primary but DateFrom = "key"... not sure what you

mean by
that?

See the answer to number one above.

4. Also, I thought I would end up with 6 tables. 2 pairs of 2

tables
linked via their junction tables. And then link the 2 junction

tables to
one another... I'm probably wrong about this, right?

TC's four table sample and explanation should be sufficient to accomplish

what you asked for - stringing multiple m:m relationships together. Look at
his tables/relations again:

tblPerson (has 1:m to tblPersonClub)
tblClub (has 1:m to tblPersonClub)
tblPersonClub (is the normal junction table structure)
tblDateRange (has 1:m to tblPersonClub)

In your first post you said:
For instance, I may have members that are assigned to many

projects (and
each project many members). But I also have boards that have

multiple
priorities and multiple priorities are linked to multiple

organizations.

For what you state specifically, you could have these tables:

tblMembers
tblProjects
tblMemberProjects

and

tblBoards
tblOrganizations
tblBoardOrganization (or tblPriorities - this is your junction table)

Do you also want m:m Members to Boards, m:m Members to Organization, and

m:m Members to Priorities? (you didn't say that's what you wanted, so I'm
just guessing here) Do Projects have Priorities too? Knowing what you
know, what do you think you would need to handle the m:m relationships
listed here?

hope this helps you

rpw


Thanks for any additional info,
Tom




"TC" wrote in message
...
"Tom" wrote in message

...
Hello,
I am familiar with using a junction table for a "many-to-many"

relationship.
However, I'm not exactly sure how I could string multiple

"many-to-many"
or
junction tables into an architecture.
Does anyone have a suggestion?
Here are two examples.
1. If A is many-to-many (m:m) with B, you need a junction table AB

(or
whatever) - as you know. Similarly, if some other table C is m:m to

D, you
need another junction table CD. Ditto for as many other tables &

junction
pairs that are required.
2. Say you have Person, Club, and PersonClub. (The latter is the

junction
table to allow a person to belong to many clubs, and a club to have

many
persons.) Say you need to record each consecutive period that a

person was
a
member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case,

for
each
one entry in the PersonClub table, there are many entries in a

table of
date-ranges:
tblPersonClub

PersonID ( composite )
ClubID ( primary key )
DateRange

PersonID ( composite )
ClubID ( primary )
DateFrom ( key )
DateTo
In the latter example:
- Person : Club is m:m ) the normal

- Person : PersonClub is 1:m ) junction table
- Club : PersonClub is 1:m ) structure
- PersonClub : DateRange is 1:m
Do those help?
For instance, I may have members that are assigned to many

projects (and
each project many members). But I also have boards that have

multiple
priorities and multiple priorities are linked to multiple

organizations.
Currently, I'm myself trying to figure out the relationships

between all
the
entities. Again, knowing how I could use (with a generic

example)
multiple
junction tables would be great advice.
Thanks,
Tom



  #7  
Old May 14th, 2004, 11:46 AM
Tom
external usenet poster
 
Posts: n/a
Default Multiple Many-To-Many Tables

TC,

anyhow, I appreciate your feedback so far. I'll go ahead and post another
thread in hope to get more advice.

--
Thanks,
Tom


"TC" wrote in message
...
Yes, you got me spot-on :-)

I can only get on the web once a day, & it gets difficult towards

week-ends,
cos I have other obligations.

Cheers,
TC


"rpw" wrote in message
news
Hi Tom,

it's been a while since TC answered, so I'll jump in here Comments

in-line...

Not sure if I entirely understand your schema.


I got the 3 tables:
tblPersonClub is the junction which has 2 Fields (Long Integer)

that
are
linked to the primary keys of the other 2 tables: tblPerson &

tblClub

So here are my questions:
1. What did you mean by "composite"?

Instead of one field of the table being designated as the PK, you can

select multiple fields and designate the combination as a PK (hence
"composite" ) It is difficult to illustrate here, but the intent is that
two or three fields are grouped together as a "composite primary key" Try
looking at TC's original post again with that in mind.

2. What is the "DateRange". I assume it's a field, right?

I believe he meant DateRange as a table (maybe tblDateRange is better)

and
the sub-listings are the fields.

3. ClubID is the primary but DateFrom = "key"... not sure what you

mean by
that?

See the answer to number one above.

4. Also, I thought I would end up with 6 tables. 2 pairs of 2

tables
linked via their junction tables. And then link the 2 junction

tables to
one another... I'm probably wrong about this, right?

TC's four table sample and explanation should be sufficient to

accomplish
what you asked for - stringing multiple m:m relationships together. Look

at
his tables/relations again:

tblPerson (has 1:m to tblPersonClub)
tblClub (has 1:m to tblPersonClub)
tblPersonClub (is the normal junction table structure)
tblDateRange (has 1:m to tblPersonClub)

In your first post you said:
For instance, I may have members that are assigned to many

projects (and
each project many members). But I also have boards that have

multiple
priorities and multiple priorities are linked to multiple

organizations.

For what you state specifically, you could have these tables:

tblMembers
tblProjects
tblMemberProjects

and

tblBoards
tblOrganizations
tblBoardOrganization (or tblPriorities - this is your junction table)

Do you also want m:m Members to Boards, m:m Members to Organization, and

m:m Members to Priorities? (you didn't say that's what you wanted, so I'm
just guessing here) Do Projects have Priorities too? Knowing what you
know, what do you think you would need to handle the m:m relationships
listed here?

hope this helps you

rpw


Thanks for any additional info,
Tom




"TC" wrote in message
...
"Tom" wrote in message
...
Hello,
I am familiar with using a junction table for a "many-to-many"
relationship.
However, I'm not exactly sure how I could string multiple

"many-to-many"
or
junction tables into an architecture.
Does anyone have a suggestion?
Here are two examples.
1. If A is many-to-many (m:m) with B, you need a junction table

AB
(or
whatever) - as you know. Similarly, if some other table C is m:m

to
D, you
need another junction table CD. Ditto for as many other tables &

junction
pairs that are required.
2. Say you have Person, Club, and PersonClub. (The latter is the

junction
table to allow a person to belong to many clubs, and a club to

have
many
persons.) Say you need to record each consecutive period that a

person was
a
member of each club. (Eg. 1990-1993, 1998-2001 etc). In this

case,
for
each
one entry in the PersonClub table, there are many entries in a

table of
date-ranges:
tblPersonClub
PersonID ( composite )
ClubID ( primary key )
DateRange
PersonID ( composite )
ClubID ( primary )
DateFrom ( key )
DateTo
In the latter example:
- Person : Club is m:m ) the normal
- Person : PersonClub is 1:m ) junction table
- Club : PersonClub is 1:m ) structure
- PersonClub : DateRange is 1:m
Do those help?
For instance, I may have members that are assigned to many

projects (and
each project many members). But I also have boards that have

multiple
priorities and multiple priorities are linked to multiple

organizations.
Currently, I'm myself trying to figure out the relationships

between all
the
entities. Again, knowing how I could use (with a generic

example)
multiple
junction tables would be great advice.
Thanks,
Tom





  #8  
Old May 15th, 2004, 03:47 AM
TC
external usenet poster
 
Posts: n/a
Default Multiple Many-To-Many Tables

Thanks Tom. Good luck with your quest!

TC


"Tom" wrote in message
...
TC,

anyhow, I appreciate your feedback so far. I'll go ahead and post

another
thread in hope to get more advice.

--
Thanks,
Tom


"TC" wrote in message
...
Yes, you got me spot-on :-)

I can only get on the web once a day, & it gets difficult towards

week-ends,
cos I have other obligations.

Cheers,
TC


"rpw" wrote in message
news
Hi Tom,

it's been a while since TC answered, so I'll jump in here Comments

in-line...

Not sure if I entirely understand your schema.


I got the 3 tables:
tblPersonClub is the junction which has 2 Fields (Long Integer)

that
are
linked to the primary keys of the other 2 tables: tblPerson &

tblClub

So here are my questions:
1. What did you mean by "composite"?

Instead of one field of the table being designated as the PK, you can

select multiple fields and designate the combination as a PK (hence
"composite" ) It is difficult to illustrate here, but the intent is

that
two or three fields are grouped together as a "composite primary key"

Try
looking at TC's original post again with that in mind.

2. What is the "DateRange". I assume it's a field, right?

I believe he meant DateRange as a table (maybe tblDateRange is better)

and
the sub-listings are the fields.

3. ClubID is the primary but DateFrom = "key"... not sure what

you
mean by
that?

See the answer to number one above.

4. Also, I thought I would end up with 6 tables. 2 pairs of 2

tables
linked via their junction tables. And then link the 2 junction

tables to
one another... I'm probably wrong about this, right?

TC's four table sample and explanation should be sufficient to

accomplish
what you asked for - stringing multiple m:m relationships together.

Look
at
his tables/relations again:

tblPerson (has 1:m to tblPersonClub)
tblClub (has 1:m to tblPersonClub)
tblPersonClub (is the normal junction table structure)
tblDateRange (has 1:m to tblPersonClub)

In your first post you said:
For instance, I may have members that are assigned to many

projects (and
each project many members). But I also have boards that have

multiple
priorities and multiple priorities are linked to multiple

organizations.

For what you state specifically, you could have these tables:

tblMembers
tblProjects
tblMemberProjects

and

tblBoards
tblOrganizations
tblBoardOrganization (or tblPriorities - this is your junction table)

Do you also want m:m Members to Boards, m:m Members to Organization,

and
m:m Members to Priorities? (you didn't say that's what you wanted, so

I'm
just guessing here) Do Projects have Priorities too? Knowing what you
know, what do you think you would need to handle the m:m relationships
listed here?

hope this helps you

rpw


Thanks for any additional info,
Tom




"TC" wrote in message
...
"Tom" wrote in message
...
Hello,
I am familiar with using a junction table for a

"many-to-many"
relationship.
However, I'm not exactly sure how I could string multiple

"many-to-many"
or
junction tables into an architecture.
Does anyone have a suggestion?
Here are two examples.
1. If A is many-to-many (m:m) with B, you need a junction

table
AB
(or
whatever) - as you know. Similarly, if some other table C is

m:m
to
D, you
need another junction table CD. Ditto for as many other tables

&
junction
pairs that are required.
2. Say you have Person, Club, and PersonClub. (The latter is

the
junction
table to allow a person to belong to many clubs, and a club to

have
many
persons.) Say you need to record each consecutive period that a

person was
a
member of each club. (Eg. 1990-1993, 1998-2001 etc). In this

case,
for
each
one entry in the PersonClub table, there are many entries in a

table of
date-ranges:
tblPersonClub
PersonID ( composite )
ClubID ( primary key )
DateRange
PersonID ( composite )
ClubID ( primary )
DateFrom ( key )
DateTo
In the latter example:
- Person : Club is m:m ) the normal
- Person : PersonClub is 1:m ) junction table
- Club : PersonClub is 1:m ) structure
- PersonClub : DateRange is 1:m
Do those help?
For instance, I may have members that are assigned to many

projects (and
each project many members). But I also have boards that have

multiple
priorities and multiple priorities are linked to multiple

organizations.
Currently, I'm myself trying to figure out the relationships

between all
the
entities. Again, knowing how I could use (with a generic

example)
multiple
junction tables would be great advice.
Thanks,
Tom







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.