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