CODEWITHSUNDEEP
mysqlentity-relationship
slex
slex

Reputation: 855

Questionnaire db structure

Sorry i got myself a bit confused as to how to make the db for my questionnaire; Its gonna be one of those questionnaires where a few questions are picked out at randon, displayed with the possible answers next to them where each answer has a weight that is calculated at the end.

So i got 3 tables: Question, Answer and Weight

Weight has a one-to-many with answer because each answer will have a weight to be added to the final score.

The thing thats got me confused is how to get rid of the many to many relationship between question and answer as one question will have many possible answers, and the same answer i.e. yes/no will be used in many questions.

Upvotes: 2

Views: 1007

Answers (3)

wuputah
wuputah

Reputation: 11395

I'd recommend the following structure:

  • Questions
    • has 0:n answers
    • attributes: id (int PK), question (text)
  • Answers
    • has 1 question
    • has 0:n responses
    • attributes: id (int PK), answer (text), question id (int FK)
  • Responses
    • has 1 answer
    • (has 1 question through answer)
    • has 1 responder (user)
    • attributes: id (int PK), answer id (int FK), user id (int FK)
  • Users
    • has 0:n responses
    • attributes: id (int PK) + anything else you need to store about users

(PK = primary key, FK = foreign key)

Store weight as an attribute of question or answer. You haven't really described what weight is, so it's hard to tell.

Here's the records for one question, and someone responding to that question. In this example, Jean Luc Picard is asked "How many lights?" with possible answers, "3", "4", and "5". Of course, he picks "4", for which is doesnt not receive any weight ("5" is the correct answer).

Question: (QID=1, Question="How many lights?")
Answer: (AID=1, QID=1, Answer="3", Weight=0)
Answer: (AID=2, QID=1, Answer="4", Weight=0)
Answer: (AID=3, QID=1, Answer="5", Weight=1)
Response: (RID=1, AID=2, UID=1)
User: (UID=1, Name="Jean Luc Picard", ...)

Upvotes: 4

slex
slex

Reputation: 855

@wuputah the weight is the points that a question is worth if selected.

I decided to go with a table for answers with the same weight i.e. tblBooleanAnswerYes, tblBooleanAnswerNo, tblTextAnswer1pt, tblTextAnswer2pt(id, text, weightID*)

With two question tables tblBooleanQuestion(id, booleanAnswerYesID*, booleanAnswerNoID*), tblTextQeestion(id, text, textAnswer1ptID*, textAnswer2ptID*, textAnswer3ptID* etc)

tblWeight -< tblBooleanAnswerYes -< tblBooleanQuestion
      \  \_< tblBooleanAnswerNo  ___v
       \
        \ -< tblTextAnswer1pt    -< tblTextQuestion
         \_< tblTextAnswer2pt    ___v

One weight for many answers, one answer for many questions

Upvotes: 0

DVK
DVK

Reputation: 129383

You have two options:

  1. Don't get rid of the many-to-many. It doesn't hurt you.

  2. Have "Answer per question". E.g. Answer table has columns "question_id, answer, weight". (as far as I understand, since weight is perl answer/question, you won't need a seaparate weight table). Primary key is "question_id+answer"

    Yes, this second design is de-normalized - you will store several different copies of "yes" answer.

    But it's no big loss (referential integrity in this specific case not a big concern (e.g. "keeping all "Yes" strings identical); and space concerns are minimal - big answers would tend to be unique anyway and for small ones, you don't save too much space by storing a "yes" question's ID vs the string "yes".

My recommendation is the second design - it's simpler both for writing code against and maintenance and there's no big downside despite theoretically being denormalized

Upvotes: 1

Related Questions