Many-to-Many Resolver/Junction tables

[Karen]

Hi,

I have been reading a book that talks about using a many-
to-many resolver or junction table but I just don't get
it.

I have two tables: tblProjects and tblEmployees. A Project
can have many Employees and Employees can work on many
projects. Do I need one of these tables for this scenario?
I tried it out in our database but when I do a query (just
testing this concept out), it doesn't work. If I remove
the "resolver" table, it does.

Can anyone explain (simply!) this concept to me?

Thanks in advance,
Karen

 

[Lynn Trapp]

Yes, it sounds like that would be a good example for that kind of situation.
Basically, you would have a table -- let's call it tblProjectEmployees --
and it would have 2 fields -- the key field from tblProjects and the key
field from tblEmployees. You will need to create a form that will update the
junction table with the correct values from those 2 tables when you do the
data entry.

 

[Tim Ferguson]

I have two tables: tblProjects and tblEmployees. A Project
can have many Employees and Employees can work on many
projects.

.... then you need another table called WorksOn to record each _fact_ of
someone working on a project:

Employee Project
======== =======
Eric Port Wing
Eric Fuel Tanks
Eric Fuel Pump
Fred Fuel Pump
Fred Carburation
Fred Electronic Throttle
Gavin Carburation
Gavin Air Speed Sensors
Gavin Visual Displays

and so on... Often there may be other bits of information applicable to the
WorksOn entity, like DateStarted, PayRate, IsTeamLeader, SessionsPerWeek,
and so on.

Hope that makes sense


Tim F

 

[Van T. Dinh]

In real-life situations, we have M-to-M relationships as you described (an
Employee can work on a number of Projects and a Project can be worked on by
a number of Employees). However, the Relational Database Structure doesn't
allow us to establish the M-to-M relationship between 2 Tables, only 1-to-M
(most common) and 1-to-1 (rare).

In Database Structure, to establish / model the M-to-M relationship we need
to break the M-to-M to TWO 1-to-M relationships and to do this, we need to
use the 3rd (Link/Resolver) Table. The Link/Resolver Table has at least 2
Fields which are the ForeignKey, e.g. frg_EmployeeID and frg_ProjectID from
your other 2 Tables. This allows you to establish two 1-to-M relationship
(tblEmployee to tblResolver and tblProject to tblResolver). This allow you
to link a particular Employee to many Projects since you can have a number
of Records in tblResolver with the same value for frg_EmployeeID. The same
applies to Projects.

This way, we can model the real-life M-to-M relationship using 2 separate
1-to-M relationships.

Regarding your Query, I can't advise since it depends on what you try to get
out of your Query. Perhaps, you should post (in descriptive words) of what
you want the Query to do.

However, the Resolver Table is THE correct set-up to model the M-to-M
relationship in your database.

 

[Karen]

Thanks for the replies everyone! At least I know I'm on
the right track.

Lynn, I don't understand what you mean by creating a form
that will update with the correct values from those 2
tables. Could you explain?

Thanks again,
Karen

 

[Lynn Trapp]

Karen,
Well, put very simply, the junction table will not be automagically
populated with the right values. You need to create a data entry form that
has the junction table fields as part of it's record source.