Relationship Help (one-to-many, many-to-many?)

[MelT]

I need help with a relationship issue. In my Access 2010 database I have these tables seen in the attached file. I have Staff who are assigned to Projects (could be one or multiple projects). Now I need to assign my Project Directors to a Staff person and Project. The Project Director could be assigned to multiple Staff and multiple Projects at the same time. Also different Project Directors working on the same project. What do I need to do to set this up? See my attached file

[CJ_London]

my comments:

As you have described your requirement:

not sure about the benefit of a separate table for project descriptions

I would put staff and project directors in the same table with an additional field to indicate what type they are.

I would rename your ID named fields with something more descriptive such as ProjectID

As for the rest of it, I think you need to ask yourself a number of 'what if' questions such as:

what if a project director leaves or is replaced on a project?
ditto staff?
ditto both for temporary but significant absence (holidays, long term sick, etc)?
what if the staff assigned to a project are also signed to another project at the same time?
what if a project ends?

[MelT]

The table for project descriptions is a junction table

what if a project director leaves or is replaced on a project? not irrelevant
ditto staff? not relevant
ditto both for temporary but significant absence (holidays, long term sick, etc)? not relevant
what if the staff assigned to a project are also signed to another project at the same time? yes, staff can be assigned to multiple projects at the same time
what if a project ends? not relevant

[CJ_London]

The table for project descriptions is a junction table

To be a junction table there should be at least two FK fields, you have none

With regards my 'what if's', they are just my suggestions, it's your project, there may well be other scenarios, but others on this and other forums would be asking the same type of basic questions. If you are happy that the structure correctly maps your business process and the requirements from the db, then I would do as I suggested before, combine the project directors and staff tables.

That leaves the situation where a director is managing staff on other projects, but are not managing that project themselves. I suggest you either modify the staffprojects table to include a DirectorID field so you complete either the projectID or the directorid field, depending on requirement. Or you create a separate junction table like StaffProjects but with the DirectorID field rather than ProjectID

[MelT]

A little background. I have hundreds of workers anywhere from the East coast to the hawaiian islands working on projects where they are each assigned to a team (let's say Project A team). Each Project A team is assigned a project director. So if I have 5 teams all working on Project A and each team has a different project director, I want to be able to connect each worker with their project director in one query or report.

Let's say I have Project A thru Z and each project might have the same or new workers and project directors. Does that make sense or help clarify what I'm trying to do?

Thks

[CJ_London]

it does and implies the suggestion I made

a) to combine the staff and directors tables
b) modify the staffprojects table to include a DirectorID field

would work for you

[orange]

if I have 5 teams all working on Project A and each team has a different project director

It seems a Project can have many ProjectDirectors???

Do you really have workers on Teams where each Team has a Director.
You can assign 1 or more Team(s) to a Project?

It may just be terminology, but (to me) Project and Team are confusing. Perhaps you could clarify what each is in your situation.

[CJ_London]

I think the OP is describing this structure

[orange]

I tend to agree, but I think the OP should confirm/ be explicit.
With this structure what exactly is a ProjectDirector? I'm think in real life terms ---Project Director, then perhaps foremen etc.
Maybe I'm just trying to sort out who is the authority on Project A for example? In most jobs I've had, there was an "ultimate" authority. Perhaps the OP is using/working from a different concept. Maybe there's a regional person (EastCoast, Central, West....) for each geoRegion, or maybe there's total bliss and harmony among a number of co-project directors (wouldn't that be nice).