0

Let's say I have:

enter image description here

and

enter image description here

For each question there will be many answers (is this OneToMany?) and answers will be chosen via single selection (multiple choice). I want to know how many times each answer has been selected for a given question, for example. A user can only answer these questions once.

I have found a few solutions (like this one What mysql database tables and relationships would support a Q&A survey with conditional questions?) but they seem overly complicated. This is a very simple survey. No subquestions, no differernt types of answers, no multiple selections.

What I'm thinking of using is an intermediary table called question_answer_user, however I am unsure of the relationships between this and the other tables (and is it a compound primary key?). I need them so I can model them on an ORM.

Sorry if this seems overly simple, but my brain is completely fried at the moment

Community
  • 1
  • 1
Nimchip
  • 1,685
  • 7
  • 25
  • 50

2 Answers2

1

With a one to many relationship you can connect question and answer by putting the primary key (id) of the question into the answer table as a foreign key.

This way you can access all of the answers to a given question by querying all answers with a certain question ID.

As for incorporating users: You say that a user can answer each question once. A user can answer many questions and a question can be answered by many users. In this case, you should have a linking table between the two that has the id of the question and the id of the user. Through this association you can see what a specific user answered on each question by traversing your other relationship.

BYUBadger
  • 53
  • 5
  • The problem is the `Answer` table simply has the given answers, not the ones the User has chosen... I should have made that easier to understand since my naming convention is terrible heh. – Nimchip Jun 05 '14 at 03:26
  • Thanks for the help I got it figured out. – Nimchip Jun 05 '14 at 04:30
  • I added a table called `Results` with a many-to-one bidirectional relation with `Question`. This table has `question_id`, `answer_id`, `user_id`, all three relationships are Many-to-One. Therefore I can query any of them to check for answers. Once again thanks! – Nimchip Jun 05 '14 at 04:31
0

If you are coding a questionnaire where you have a set of questions (for example, 'how many years experience do you have with SQL?') and a fixed set of answers (1-2,3-4,5-6,7-8) then the following would be a good table design.

questions
id - longint (primary key)
text - varchar (255)

options
id - longint (primary key)
question - longint (foreign key to 'questions')
displayorder - int 
text - varchar (255)  (the text of the option)

answers
user - longint (foreign key to users' table)
question - longint (foreign key to questions table)
option - longint (foreign key to options table)

The answers table will have one record per question per user, recording that user's answer for the given question (for example, user #3 answered question #1 with option #4).

No'am Newman
  • 6,395
  • 5
  • 38
  • 50