MySQL Table Design for a Questionnaire

Question

I am fairly new to MySQL and have a project in which I need to design a database that will store responses from an online questionnaire. Reports will need to be written from the data. Does anyone have any tips on what type of fields to use? The questions will either have a Yes No answer, a choice of 4 options from very satisfied to very dis-satisfied or multipul choice. It's mainly the choice questions that I'm unsure on, as I will need to be able to product a report to show the percentage of satisfied customers. I know it's probably really basic, but I don't want to get it wrong.

Answer 1

Have you explored the option of using something like SurveyMonkey for your questionnaire?

If that doesn't suit, there's a thread on MySQL's forum which may help: Database design for multiple choice questionnaire results?

Answer 2

It's not the whole answer to your question, but this question I asked earlier might be helpful to you.

Answer 3

I hadn't to be honest, but I will look at it. I would really like to learn how to write one from scratch though. It would be a skill that I could use for other sites.

Answer 4

Thank you Ben,

I had read through your question, I have been trying to get my head around the answer. Does it mean that each radio button for each question has a differnt numerical value; i.e very satisfied = 100?

Answer 5

Thank you for the thread Galwegian, it is beginning to make more sense now :-)

Answer 6

Some of this depends on the design of the survey - is it single or multi-page; if multi-page, are you storing the data as the user moves from page to page, or just at the end, etc. I think the biggest issue is whether a single survey response is going to correspond to a single database row or whether a single survey response may be split across multiple rows. Obviously the former is the way to go if at all possible.

For the multi-choice questions specifically, I would use either INT or ENUM columns; determining the fraction of respondants answering in a particular way is a simple "SELECT ... GROUP BY " query.