0

I have to create a survey app for which answers should be stored in the database. I created an html form containing some questions (about 30) with various types of answers (check boxes, radio buttons, auto-complete, etc). Now, I am going to design my database.

First, I thought of only 3 tables : Questions, Answers, Users . However after I searched some similar questions such as :Database design for a survey, db design - survey/quiz with questions and answers [closed] I found some other design models e.g creating 4 tables : Questions, PossibleAnswers, UsersAnswers and Users. Now, I have these questions:

  • Should I create a survey table too ? (at this moment,there is only one survey for my app, but not sure about the future.)

  • Which design model would be better in my case?

  • In case of using the first model, should I create specific column for each types of answer? (for instance, consider this question: which are your favorite movie genres? and it has more than 10 possible answers! then if I want to create a column for each possible answers of all questions, it would be lots of columns!)
  • In case of the second model, is it correct? (this is what I understood from other similar questions, but still I am not sure if it is useful form me and if yes, what is the advantage of this model?)

    Questions: questionId, question

    PossibleAnswers: possibleanswerId, questionId, possibleanswer

    UsersAnswers: useranswerId, possibleanswerId, userId, answer

    Users: userId, username, password

I really appreciate all your ideas and suggestions

0

I think you should have the Survey table as it will be much easier to add it now than later on when your app is live.

The second model is much better as it provides the flexibility to have multiple possible answers and user answers for a single question in a normalised fashion. Having a column for each answer is not a good idea.

  • thanks James, so in case of the second model, my question is that what could be the possible columns for "PossibleAnswers" table? since for instance my first question if of type checkbox and it has about 10 possible answer.. how could I manage it for all the questions? – monamona May 20 '14 at 12:15
  • Something like: possible_answer_id, question_id, answer_text, display_order. The last one would be used to order the possible selection for a radio group question – James Anderson May 20 '14 at 13:02
  • Thanks a lot, I just didn't get what exactly the last column means, I mean whay I need order for radio button answers? :( – monamona May 20 '14 at 13:58
  • If you had the following values for your answers it would make sense: 0, 1-5, 6-10, 11-15 – James Anderson May 20 '14 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.