Database design for a survey system

Question

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 Anwser table and with the PossibleAnwsers table.

Recently the customer realised she wants the ability to show certain questions only to people who gave one particular anwser to some previous question (eg. Do you buy cigaretes? would be followed by What's your favourite cigarete 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 anwsers: A and B, and question B should only appear to a user if the anwser 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 ;)

Answer 1

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...)

Answer 2

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.

Answer 3

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.

Answer 4

You're really inventing the wheel here. These are called CAWI systems (Computer Assisted Web Interviewing) or CATI/CAPI/CAMI, respectively. There are many tools already out there (Sawtooth, Voxco, etc) which will allow you to create surveys. At the very least you can look at their structures to see how you might design yours. Usually these systems are implemented using "skip patterns" which allow you to skip over the next question(s), to a future one.

This might be how I design my database:

Questions

• ID
• Text
• Answer set ID

Answers

• ID
• Answer set ID
• Item ID (to identify it between the other answers in the set)

Skips

• ID
• SkipFrom
• SkipTo
• AnswerID (put a composite unique constraint on this and SkipFrom)

Something like this should give you plenty of flexibility I'd think.