Set-up Junction Table with Additional Fields (1 Viewer)

[PSSMargaret]

Requesting advice on setting up a table/relationship.

The database contains Contacts and Communications that are sent out to contacts. I believe it’s a many-to-many relationship (a communication can go out to many contacts and a contact can have many communications).

So I’ve set up

• tblContacts – contains ContactID plus a number of other fields regarding a contact
• tlbCommunications – contains CommunicationID and a few other fields that describe the Communication
• tblJctCommunications – junction table that contains ContactID and CommunicationID

I need to set-up another table with the fields listed below to record comments received from contacts regarding a communication a response back to them.

• ContactID – already in tblJctCommunications
• CommunicationID – already in tblJctCommunications
• DateCommentReceived
• CommentReceived
• ResponseDate
• Response

Should the fields above in blue go in the junction table or a separate table? It doesn’t make sense to me that they’d go in a separate table because it would require both the ContactID and CommunicationID (seems duplicative) but I want to make sure I’m not missing something before I proceed.

The junctions tables I've done in the past only had two separate ID fields. This would have the two ID plus the four fields above in blue.

Any guidance is appreciated.

 

[Pat Hartman]

individual attributes go in the table which has the PK that defines them. So, SOME of your ancillary fields go into the junction table. this happens all the time in real life. I think the DateCommentReceived belongs in tlbCommunications. Sometimes, junction tables need to have additional fields in the unique index. For example, in my example, if you want to support the employee taking the same class multiple times, you would add a ClassDT to the junction table and make that part of the Unique index.

Here's an example I built to show m-m. It has ancillary fields and it shows the two ways to display "the other side" using either a subform or a popup form which ever makes more sense to you.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

Also, your schema handles only a single response so you might want to rethink the structure.

I think the reply should be in tblCommunications with a link to the communication it relates to. This gives you a much more complex structure if the communication chains get long plus you will need recursion to process the chain correctly.

The alternative is to add a child table for the Junction table and use that to link in a flat way, all the communications in a thread. As long as they are always added in order, the autonumber can serve to sequence them. If there is a possibility that they might be added out of order, you need a date field including time which can be used to sort the responses.

And finally, I always use an autonumber PK for my junction tables even though they rarely have child tables but in your case, you should consider adding one. Then you would have to have a unique index on the two FK fields to prevent duplication.

 

[XPS35]

It depends on whether a contact can respond to a communication more than once. I can imagine that a contact will respond to your response again. Then the model with three tables falls short.

 

[jdraw]

Margaret,
Good comments in posts 2 and 3.
Have you taken a sample from the requirements and mocked up a sample scenario? Something along the lines of
Bob (contact #7) was sent a copy of Communication #1 on Date X. He responded on Date Y and said "Text From Bob Date Y".
Now what, could there be more detailed communication to Bob based on his Response on Date Y?
If so, how do you relate these subsequent Communications?
Could Bob make a second (or third) response to Communication #1?
Is there any situation where Bob must respond (legal/financial...)?
Even better is to take some real life contact-communication examples to use as sample scenarios.

These questions/thoughts just meant to flesh out the requirement. At some point in this sort of questioning/fact finding the location of where these additional attributes belong will become clear. I think by laying out your tables and relationships and working through some sample "communication exchanges", you'll will determine where the attributes belong. Also, in your fact finding you may find additional attributes.

Good luck with your project.

 

[arnelgp]

The junctions tables I've done in the past only had two separate ID fields. This would have the two ID plus the four fields above in blue

i was thinking of 2 additional table, one for comment and the other for response.
there could be many response/comments or none at all.
both tables having date fields. and has a Foreign key linked to the Primary key of tblJctCommunications table.

 

[PSSMargaret]

Thank you all for your comments and guidance. I've currently set it up with the comments and responses going in the junction table adding a Primary Key (see image attached) and allow duplicate Contact IDs and Communication IDs should someone comment more than once. All comments will be responded to.

I will be testing it out today. This is a new set-up for me so the learning continues which is great.

Thanks again. Very appreciative. I may be back .