Populating Junction Table & Many to Many Relationship

[sg2808]

I have two tables (tblPolicyMap and tblBusiness) and the fields within it are:

1. tblPolicyMap
PolicyID (PK)
PolicyDetail

2. tblBusiness
BusinessID (PK)
HLProcessName
Business_Functions

One policy could affect more than 1 HLProcessName and Business_Functions.
Also, One Business_Function / HLProcessName could have multiple policy.
Therefore, this is a many to many relationship.

My questions are:

a. As Policy detail has n-n relationship with both HLProcessName and Business_Functions, should I create 2 n-n relationship (Policy - HLProcessname) and (Policy - Business-Functions)? or it is it still ok to have only two tables as above?

b. With the assumption that only two tables will suffice (as above), I have created a junction table (tblPolicy_Business) with fields - PolBizID (PK), PolicyID (FK)and BusinessID (FK). I do not know how to populate the junction table, What approach should I take to populate the Junction table assuming that I have all the data in tblPolicyMap and tblBusiness?

c.To add to the above, how should I create a form so that I can easily populate the junction table for new records?


Kindly advise.

[mshmyob]

Since you have a M:N relationship between the two tables you need your bridge table like you are thinking.

Your bridge table can consist of only two fields:

PolicyID (PK)
BusinessID (PK)

Make the two fields a composite primary key.

To populate the bridge table your form will have combo box controls for selecting the Policy and Business. Upon selection of a value from the combo box the combo box will save the PK from the appropriate table and store it into the appropriate FK in the bridge table.

Note: Each part of the PK in the bridge table is the FK to the corresponding table.

cheers,

[NeoPa]

There is no way to reflect the relationships between the two main tables without creating a bridging table.

Mshmyob's suggestion of two FK fields joined together as a composite PK is certainly workable, but I'd recommend your original suggestion (AutoNumber PK as well as two separate FK fields.) over that generally, and especially if the volume of bridge records is vast. The composite key would still be required, to ensure no duplication of the data, but searches can be faster on indices containing less data. If you can be sure that the volumes won't be high then Mshmyob's simpler solution might win out, as it takes less space and less time to create records in the first place.

Both approaches are perfectly valid of course.

PS. Please note that questions must not be multiple in a thread. The rule is one thread - one question. In this case, principally because Mshmyob had already responded fairly fully, but also because the questions were all so closely related, I was able to treat it as a single composite question rather than three separate ones, but please bear in mind for future questions.

PPS. Nice job to clarify the question in the first post rather than simply leave it spread out over multiple posts. Saved me a job ;-)

[NeoPa]

Another discussion, related to this thread, was started in here, but has now been moved to Discussion: About Relationships.

[sg2808]

Given my scenario (as above), does this mean that I will have to create 3 Forms? - 2 Form for the two tables and another Form for Junction table?
I am not quite clear on this from mshmyob's post above.

What is the best way here? Kindly clarify which Forms should capture which fields.
Many thanks.

[NeoPa]

I'm not sure there's actually anything missing from Mshmyob's post related to the question, but let's see if we can go over it again to aid comprehension.

There are, of course, less basic approaches conceivable which include code to add records to recordsets not bound to the current form, but I'll take you through the standard approach :

1. A form is needed for each main table. Each handles maintenance of the associated table.
2. A form is required, just as Mshmyob outlined, to maintain the bridging table. This allows adding of FK values from ComboBoxes listing the existing PK values of both main tables separately.

Thus, yes. You will need three tables and three separate forms in order to maintain them. This is pretty standard in your circumstances.

[sg2808]

Thank you so much NeoPa and mshmyob for your advices.