I'm trying to create a many to many relationship while storing some information about that particular relation. For example:
A SurveyQuestion has many AuthoritativeSources. When an authoritative source is chosen for a question, the rationale (or justification) for the selection should be documented in a note.
An AuthoritativeSource can be used in many SurveyQuestions.
In a database this would be represented as:
SurveyQuestion >-< AuthoritativeSource
SurveyQuestion
- ID
- QuestionText
AuthoritativeSource
- ID
- Name
SurveyQuestion_AuthoritativeSource
- SurveyQuestionID
- AuthoritativeSourceID
- Rationale (Single line field)
I can create a multi-select list box in the SurveyQuestion to represent the relationship but where do I put the rationale for the selection?
We want to create two reports:
1. Each survey question with its authoritative source and the reason why that source makes sense (rationale).
2. The questions that refer to a particular authoritative source with the rationale.