Correct juncture table design to link three tables?

[kdirvin]

Hello,

I used Access 2007 to build a database that tracks projects. All projects are listed in tbl_Projects:

tbl_Projects
ProjectID (PK)
ProjectTitle

Each project has three types of agencies associated with it. One type of agency works the actual projects. The second type approves the work. The third type pays for them. I list the names of all agencies in tbl_Agencies.

tbl_Agencies
AgencyID(PK)
AgencyName

I have another table, tbl_AgencyType, listing the three types of agencies.

Tbl_AgencyType
AgencyTypeID(PK)
AgencyTypeName (consists of three entries: Worker, Approver, and Payor).

Since each project has many agencies and one agency can have many projects, I created a juncture table, tbl_ProjectAgencies, to accommodate the many-to-many relationship between projects, agencies, and agency types.

tbl_ProjectAgencies
ProjectAgencyID (PK)
ProjectIDFK
AgencyIDFK
AgencyTypeIDFK

I was trying to structure my tables in conformance with data normalization principles, but right now this is a mess. I need to be able to query any agency and return all projects associated with it. My attempts to construct this query have so far returned no records or an error message saying that the expression is too complex to be evaluated.
Is this the correct table design for the situation that I am trying to track? Or should I simply create “Worker Agency”, “Approving Agency,” and “Payor Agency” fields in tbl_Projects, separate the Worker, Approver, and Payor agencies into three separate tables, and do three separate one-to-many relationships between tbl_Projects and the three agency tables?

I am utterly grateful for any assistance you can provide. Thank you!

 

[Beetle]

My first question would be can one Agency perform different roles for different projects? In other words, can the Acme Agency be a Worker on one project, an Approver on another and a Payor on another?

 

[kdirvin]

Hello,

No. Each agency can only be one role. A project will have three agencies, one from each role.

Thanks!

 

[Beetle]

Then just offhand I would think a table structure like the following;

tblProjects
***********
ProjectID (PK)
ProjectDate
ProjectDescription
WorkerID (FK to tblAgencies AgencyID)
ApproverID (FK to tblAgencies AgencyID)
PayorID (FK to tblAgencies AgencyID)
'other attributes of the project

tblAgencies
************
AgencyID (PK)
AgencyName
AgencyTypeID (FK to tblAgencyTypes)
'other attributes of the Agency

tblAgencyTypes (essentially just a lookup table for data integrity)
****************
TypeID (PK)
Description

 

[kdirvin]

Thank you for your quick responses!

I like your suggested table design better; that is closer to what I had originally. I restructured my tables according to this design. This created 3 one-to-many relationships between tblAgencies and tblProjects. I then created a query with the following fields:

From tbl_Projects:
ProjectID
ProjectName
WorkerIDFK
ApproverIDFK
PayorIDFK

From tbl_Agencies:
AgencyID
AgencyName

When I ran the query, it returned no records. So I deleted the 3 relationships and created 1 one-to-many relationship between tblAgencies and tblProjects (on the WorkerIDFK field). I ran the same query as above but with only ProjectID, ProjectName, and WorkerIDFK from tbl_Projects. The query worked and returned the proper agency names.
I’m wondering: should I split the three types of agencies into three different tables? Or am I doing something incorrectly with my relationships and/or query design?
Thank you so much for your help!

 

[Beetle]

You need to add tblAgencies to the query three times, then create a join between each instance of tblAgencies and one of the three FK fields in tblProjects.

 

[kdirvin]

That worked! The query is now working.

Thank you so much!

 

[PELIN]

My first question would be can one Agency perform different roles for different projects? In other words, can the Acme Agency be a Worker on one project, an Approver on another and a Payor on another?

Dear Beetle, if the answer to this question was YES, how would you handle it? I have exactly the same situation, and my agencies can have different roles in different projects.
Thanks ahead.

 

[Beetle]

In that case you would have a many-to-many relationship between Projects and Agencies, so you need a junction table. Something like;

tblProjects
***********
ProjectID (PK)
ProjectDate
ProjectDescription
'other attributes of the project

tblAgencies
************
AgencyID (PK)
AgencyName
'other attributes of the Agency

tblAgencyProjects (the junction table)
**************
AgencyID (FK to tblAgencies)
ProjectID (FK to tblProjects)
AgencyRoleID (FK to tblAgencyRoles)
(you could use the above three fields as a compound PK, or you could add a surrogate PK and just have a unique index on the above three fields)

tblAgencyRoles (stores information about the different roles an Agency might perform)
****************
AgencyRoleID (PK)
Description