Table Structure for questionairre application 1

[checkai]

I am in the beginning stages of building a questionairre type application.

There will be multiple questions that need to be tracked (yes/No), and depending on some of the answers, other questions need to be asked...

I am wondering what type of database table structure I should have for an application like this...

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[jbenson001]

I would create a table containg the quesions you need to ask. Have an identiy column as a primary key. Then have another table that holds the results including the question ID and the response.

 

[checkai]

what about the if Yes goto, if no, stay on track part of it...

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[TJRTech]

- QUESTION_TYPE table: Assuming you have more than just Yes/No types of question. This would be a type table with PK/Identify column and be filled statically with the types of questions (Yes/No, multiple choice, True/False, etc). This is not necessarily needed, but might be nice to have.

- QUESTION table: This is the main question PK/Identity column, and QuestionText varchar column, along with FK into QUESTION_TYPE table. Also, a question number, and sub-question might be useful as columns (number=1, sub=part = A, or B or NULL).

- POSSIBLE_ANSWER table: This table would have PK/IDENTITY column, an AnswerText varchar column, and would house all the possible answers to all the questions. Could include "YES", "NO", "TRUE" and "FALSE", along with all the various multiple choice answers.

- QUESTION_TO_POSSIBLE_ANSWER table: This would be an associative (e.g. a relationship) table that hold FKs for QUESTION and POSSIBLE_ANSWER table. For example, a TRUE/FALSE question would have two entries in this table, one for True, sne for False each pointing to the FK of the QUESTION.

- QUESTION_ANSWER_FOLLOWUPS table: You said that based on the answer of one question you might follow-up with another question. To allow for that FLOW you might want to populate a static table that has three FK columns: QUESTION_ID, POSSIBLE_ANSWER_ID, NEXT_QUESTION_ID. In this way, given a question (QUESTION_ID), and an answer (POSSIBLE_ANSWER_ID) you have the next question to present to the user (NEXT_QUESTION_ID).

- ANSWERS table: You also need a table for recording answers supplied by users taking the questionares. I suspect an ANSWERS table, with FK into QUESTION and POSSIBLE_ANSWERS, and a FK into a QUESTIONED_USER table is all you need.



Of course, you probably want a good administrative UI for setting up all the questions, possible answers, and follow-ups.

TJR

 

[checkai]

thanks for the great input! (Y)

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[sebbas]

Hi Checkai,

We make (and sell) com objects to make websurvey's. We support 7 questiontypes, question pages, grid/matrixes and routing (when question 1 answer 1 then skip question 3). You can check a example of a websurvey here:

http://demo-isiz05.com4.variabele.nl/

The websurvey application consists of 3 com objects (a stream, logic and a web object), a sql schema, and a asp script. Of course there are programs to make the questionairre and export the questionairre to spss.

When you still want to make your own websurvey, we use the following tables
Questiontype - all questiontypes
Page - pages wich contain the questions and intro/end text
Question - The questions
Grid - Table to group questions in a grid or matrix
Answer - answers for the multiple choice questions
Routing - the routing in the questionairre
trigger - conditions when the routing must apply
action - actions of the routing
Survey - Filled in survey's (start time, completion time, ...)
Sur2Ans - answers on muiltiple choice questions for a filled in survey
Sur2Str - open answers
...

hope this helps
Sebastiaan te Boekhorst
ISIZ