Search code examples
mysqlentity-relationship

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.

Solution

  • 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", ...)