Let's say I have a survey that will be handed out to many people. I have a hypothetical diagram with User, Question, and Response entities. Should my relationship be one-to-one from question to answer? My thinking is that each question can only have one response. I could argue, however, that since this is a survey with many users, each question can have multiple Responses.
How do I approach this situation, and where is my confusion stemming from?
Best Answer
Table 1 - Questions
Table 2 - Users
Table 3 - UserResponses
If your answers could be more than just yes or no 1 or 0, you might also want a question_answers as a 4th table. With this you could then use multiple choice with more than 2 choices.
UserResponses will be a many to many relationship or a junction table because a user might answer more than one question and a question will be answered by more than one user.
The UserResponses table will reference the Users table primary key and the Questions table primary key as foreign keys.
In the case where you want multiple choice, not just yes/no, the UserResponses table will also have a foreign key relationship to the Question_Answers table.