What mysql database tables and relationships would support a Q&A survey with conditional questions?

kender_4172 0
Viewed 41059

I'm working on a fairly simple survey system right now. The database schema is going to be simple: a Survey table, in a one-to-many relation with Question table, which is in a one-to-many relation with the Answer table and with the PossibleAnswers table.

Recently the customer realised she wants the ability to show certain questions only to people who gave one particular answer to some previous question (eg. Do you buy cigarettes? would be followed by What's your favourite cigarette brand?, there's no point of asking the second question to a non-smoker).

Now I started to wonder what would be the best way to implement this conditional questions in terms of my database schema? If question A has 2 possible answers: A and B, and question B should only appear to a user if the answer was A?

Edit: What I'm looking for is a way to store those information about requirements in a database. The handling of the data will be probably done on application side, as my SQL skills suck ;)

data-modeling erd database-schema database-design sql
4 Answers

You could also think about complex rules, and have a string based condition field in your Questions table, accepting/parsing any of these:

  • A(1)=3
  • ( (A(1)=3) and (A(2)=4) )
  • A(3)>2
  • (A(3)=1) and (A(17)!=2) and C(1)

Where A(x)=y means "Answer of question x is y" and C(x) means the condition of question x (default is true)...

The questions have an order field, and you would go through them one-by one, skipping questions where the condition is FALSE.

This should allow surveys of any complexity you want, your GUI could automatically create these in "Simple mode" and allow for and "Advanced mode" where a user can enter the equations directly.

Osama_ALASSIRY_255440

one way is to add a table 'question requirements' with fields:

  • question_id (link to the "which brand?" question)
  • required_question_id (link to the "do you smoke?" question)
  • required_answer_id (link to the "yes" answer)

In the application you check this table before you pose a certain question. With a seperate table, it's easy adding required answers (adding another row for the "sometimes" answer etc...)

tehvan_618480

Personally, in this case, I would use the structure you described and use the database as a dumb storage mechanism. I'm fan of putting these complex and dependend constraints into the application layer.

I think the only way to enforce these constraints without building new tables for every question with foreign keys to others, is to use the T-SQL stuff or other vendor specific mechanisms to build database triggers to enforce these constraints.

At an application level you got so much more possibilities and it is easier to port, so I would prefer that option.

I hope this will help you in finding a strategy for your app.

Tomh_531700
Related