0

Lets say you have the following questions on a survey that you need to transpose into a database then use in a webapp:

During the last weekend, which of the following did you eat?

  • ice cream
  • salad
  • fried chicken

In the above we make the connections automatically. But in order to have the questions make sense in the database should they be stored like this?:

  • During the last weekend, did you eat ice cream?
  • During the last weekend, did you eat salad?
  • During the last weekend, did you eat fried check?

Say you're using a design like Micheal's, it would seem reasonable to store them in tables like so: 

[During the last weekend, did you eat ice cream] -> questions_table.question_name
[Yes/No] -> Option_group_table.option_group_name
[Yes][No] -> option_choices_table.option_choice_name
[True or False] -> answers_table.answers_yn

yet if wanted you wanted to build a wepage from this data it would be cluttered to display:

  • During the last weekend, did you eat ice cream?
  • During the last weekend, did you eat salad?
  • During the last weekend, did you eat fried check?

Should the front end webapp take care of this? Through some means like regex?

Function_to_help_properly_display_string("String")

The con's to this approach seem to be that different questions.question_names (strings) couldn't be handled the same way. For example:

Which of the following did you goto on the weekend?

  • Germany
  • France

Couldn't be handled the same way as our original question.

Or should the database account for this possible having more columns?

[Question table]
[question.heading] -> During the last weekend, which of the following did you eat?:
[question.list] -> ice cream
[question.full] -> During the last weekend, did you eat ice cream?

The cons to doing something like this is it seems repetitive and cluttered.

Most likely their is a third option that i'm not presenting, feel free to share it! Thanks in advance,

Community
  • 1
  • 1
Drew Verlee
  • 1,880
  • 5
  • 21
  • 30

1 Answers1

1

I've done database design/UI design for surveys several times and here is what I've used:

Table: questions

Fields: question_id, question_text, display_order, question_type, required

Note that the display_order, question_type and required field are all hints to the UI. We haven't stored the question and answer together in text, but we've given the web developer hints on how to display the questions in the UI. We tell them what order they should appear, what kind of question it is (checkbox/multiple choice -or- radio button/one choice), and whether or not a question is required.

Table: question_options

Fields: question_option_id, question_id, option_text, display_order

Same thing here. We have some UI help with the display order and the option text. Note that with our options, we aren't dictating what happens in the UI. That is held at the question level. This means you can easily change how the options are display (a dropdown, radio buttons, checkboxes, etc.)

Also note we leave the ability to join to the question.

Table: answers

Fields: user_id, question_id, question_option_id

This simply tell us for a given user, and a given question, what the user answered.

All three of these structures are a good start to making a survey in the database. The UI flows fairly naturally from this. You'll have to write queries/stored procedures to return the data to the web application, but it isn't hard to do.

(In general I think it's unwise to try and do string parsing as part of the display of data from your database. In other words I think it's unwise to try and use regular expressions to compose a question/answer to display in the UI. Model the database right and you'll find the rest of the application won't be hard to code.)

ryan1234
  • 7,237
  • 6
  • 25
  • 36