Set-up Junction Table with Additional Fields

[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.

 

[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 .