Too many many-to-many?

[Huber57]

To all, I hope you can shed some light on this for me.

I am using Access 2003.
I have three main tables
1) Project
2) Contractor
3) Service (general contracting, soil testing, architecture, etc)

I have two 'join' tables to create a m:m relationship between Projects and
Contractors and Contractors and Services
a) ProjectContractorJoin
b) ContractorServiceJoin

I would like to be able to create a form where I can select a project, then
select one or more contractors, then select one or more services that each
contractor could offer (and track when they start and end those services). A
form with a subform and then a subform in that one?

My question is, do I need a third join table to track what services are
offered on a project? Essentially, a m:m table connecting the Project Table
with the Service Table with the following fields:
ProjectServiceID (PK)
ProjectName (FK)
ServiceName (FK)
StartDate
EndDate

Any suggestions are greatly appreciated. I can't find much on m:m
structures on the web.

Thanks in advance.

 

[Pat Hartman]

Yes, you need an additional join table. The ContractorServiceJoin is simply
there to indicate which services a particular contractor offers and has
nothing to do with services for a project. You would use this to populate
the drop down when you assign a contractor to a project so that you can only
choose a service that the contractor is defined to offer.

To handle the nested subforms, I frequently use a side-by-side view since
you cannot embed a subform in a form in continuous or datasheet view. The
main form would be for Project. The Left-Hand subform would be for
contractors and the Right-Hand subform would be for services by that
contractor. When you select, a contractor, the Right-Hand subform needs to
be requeried so that it reflects the services provided by the selected
contractor. You can use the master-child links to link subform a and be.
You just need to qualify the master field name with the subform a name.
BTW, you won't be able to do this with the wizard, you'll need to do it by
typing in the master link field. You should be able to link project to the
main form and contractor to sfrmA. If not, link both fields to sfrmA.

An alternative would be to swap the subforms and show services on the left
and then the contractors who will provide them on the right if that makes
more sense to your users. You might even want two forms so the user can
choose his view.

 

[Huber57]

Pat,

Thanks much. I assume I am okay to put start and end date fields in the
ProjectServiceJoin table.

I appreciate the quick and thorough response.

 

[Pat Hartman]

Yes. That will be fine. Any data that is dependent on BOTH fields of the
PK belongs in this junction table.

 

[Ken Sheridan]

By identifying the need for the ProjectServiceJoin table, what you've in fact
done is identify the fact that Fifth Normal Form (5NF) is not achieved
without it. Joining the two tables as they stand over Contractor could
produce spurious rows in the result table indicating that certain contractors
supply certain services to certain projects when that is not in fact the
case, but by further joining the result table to the third table these are
eliminated. 5NF is not an easy concept to understand, and your intuitively
identifying it here is impressive; well done!.

In case you are wondering what 5NF actually is here's Date's definition of it:

"Fifth Normal Form: A relvar R is in 5NF – also called projection-join
normal form (PJ/NF) – if and only if every nontrivial join dependency that
holds for R is implied by the candidate keys of R."

A relvar is Date's term for a relation variable, which loosely equates to a
table. When a relvar is 3-decomposed each of the 3 relvars are in 5NF
because they don't include any nontrivial join dependencies. A join
dependency is another tricky concept; I won't bore you with the formal
definition, but it relates to constraints which are satisfied only if the
original relvar is equal to the join of certain of its projections. In your
case the production of spurious rows by the join of the two tables would mean
that a relvar ProjectionContractorService might not equal the join of its
projections ProjectContractorJoin and ContractorServiceJoin. On the other
hand it would equal the join of its projections ProjectContractorJoin,
ContractorServiceJoin and ProjectServiceJoin.

I think you'll see why its not the easiest of concepts to get your teeth
around, but the above is really just the expression in the formal language of
the database relational model of what you've instinctively spotted.

Ken Sheridan
Stafford, England

 

[Pat Hartman]

Oops! Meant to say that data fields must be dependent on ALL fields of the
PK.

 

[Huber57]

Pat and Ken,

I have figured out the Project and Contractor Form and Subform but the
Service subform has me really stumped.

Let me lay out my table structure again.
Project Table
ProjectContractorJoin Table
Contractor Table
ContractorServiceJoinTable
ServiceTable
ServiceProjectJoin Table

I cannot figure out the query to complete the subform to get the services to
show up for a give contractor for a given project.

Any thoughts?

Sincerely,
Doug

 

[Pat Hartman]

Are you linking services to projects and then contractors to services OR
contractors to projects and services to contractors. You don't need both
ProjectContractorJoin Table and ServiceProjectJoin Table since one of the
joins can be inferred through the other.

 

[Huber57]

Pat,

To answer your question, I have linked projects to contractors and then
contractors to services.

I thought I needed both because I need a place to capture the start and end
date for each service offered on each project. This data is held in the
ProjectService Join Table
ProjectServiceID (PK)
ProjectName (FK)

Wait a second! ... it sounds like I may not need the ProjectContractorJoin
Table (be patient as I work this out ...). Then I could create two sub forms
(with the master being the project data). The first sub form would show the
service with start and end dates. The second would show the contractor
assigned to the service (through the ContractorServiceJoin table!).

Question 1) How do I link the second subform back to the first one? I
thought I could only link it to the master form.
Question 2) (and I appreciate your patience) Can you help me with the query
necessary to create the second subform? I can figure out the first subform.

Thanks much!!

Doug

 

[Pat Hartman]

The third subform should join the servicesToContractor table to the
Contractor table to obtain the contractor name. If you have multiple
contracters working on a single service, do they have independent end dates
or are the dates associated with the service?

One of my earlier posts mentioned how to use the side-by-side method for
displaying three-tiered structures. Use the master/child links but you'll
need to type the reference manually. You won't be able to choose it via the
wizard. Include the subform name in the reference. You need to requery the
third form in the current event of the second form in order to sync them.

Master - sfrmServices.ServiceID
Child - ServiceID

 

[Huber57]

Pat,

I now understand what you mean when you say I can't use the wizard. I
originally thought you meant the form wizard but you mean the SubForm Field
Linker.

I have created both both subforms but something is still not correct. I get
a box that wants me to enter a parameter for
ProjectServiceJoin_Subform.Service.

Each service for each project will only have one contractor. So, I am
questioning my tables and relationships.

Thoughts?

I appreciate your continued help.

Doug