How to structure survey data?

Question

Note: this is NOT like this question

I've been tasked to construct a survey that our customer service people can use to get info from our customers about our service etc.

My question is how to store the questions/answers in the db where the questions are stored like so:

• boolean question1
  • if (false): string question2 (typically the reason why question1 was false)

This would be easy, however the questions can be nested multiple levels:

• boolean question1
  • if (false) boolean question2
    • if (true) string question3
    • if (false) string question4

If I didn't have to store it in the db, I would represent the questions (and indeed the whole survey) as a composite, but I don't know how I'd store something like that.

Answer 1

I think I would do something like this:

Table: Questions
int id
string question

Table: Question Answer Map
int questionId
bool answer
int nextQuestion

Instead of a bool, you could use an int, char, or string for your answer if the questions are multiple choice.

Answer 2

If your questions form a tree, and not a graph, there are two common ways to represent hierarchical data in sql.

The Adjacency List Model model requires repeated self-joins to look up children (of children ... of children). If you have an ORM, like Hibernate, the ORM will take care of doing sufficient self-joins to bring back a question and its child answers. Without an ORM to do that, you'll have to either dynamically add self-joins to a query, or do multiple queries, one for each row in the previous result set.

The Nested Set Model, usually attributed to Joe Celko, allows you to get a whole tree in a single select. But it means that adding and deleting nodes is more complicated, requiring updates to all rows.

In Adjacency List, you could have a question table like this (id, question text, id_next_if_true, id_next_if_false). This departs from the classical Adjacency List, in that instead of children holding the parent_id, the parent holds two children ids, or null.