Multiple Many-to-Many's

[Squid11]

Hey all.

First post....cool site

Got into an Access project that requires multiple many-to-many relationships and I'm curious if the way I'm setting it up makes sense. The project covers research projects which have multiple authors and multiple areas of impact. I've got the Many-to-Many relationships all keyed to the main table via the Project_ID field in my project list. So, I've got the Project_ID in my Projects table as the primary key going out to multiple intermediate tables. Is this sound design to have multiple relationships tied to the same key or is there another way?

I don't want to build this on a faulty foundation. Thanks for any advice

-Jeff

 

[llkhoutx]

It seems to me that you are describing one to many relationsships, i.e. one project, many authors; one project, many impact areas; and possibly, one project, many impact areas, with each impact area many authors. Those are one to many at best, one to many to one, at worst.

 

[Squid11]

Maybe I didn't describe it right...I'm quite sure they are many-to-many. It's the "classic" books and authors example. One Author can write multiple books, and every once in a while a book can have multiple authors...necessitating a many-to-many with an intermediate table.

I've got that situation with research projects:

1. Many to Many with researchers

2. Many-to-Many with areas of interest. As in a project could be about electricity, heating, and conservation...and these three areas could apply to other projects as well. (I have a table of about 40 impact areas used to classify projects)

My concern is if it is good design to have the intermediate tables of these many to many's all point to the primary index of the Projects table. Is it poor design to have multiple relationships tie into the same index? Don't think so, but not sure.

Thanks,

Jeff

 

[llkhoutx]

Intermediate, that is junction, tables are quite common and necessary.

 

[Squid11]

Yes....my typo...."junction table" above...."intermediate table" is ambiguous....any help on the substance of the Q?

 

[llkhoutx]

Is it poor design to have multiple relationships tie into the same index?

If the forgoing appears to be your only question. I'd answer, no.