Survey with dependable answers

[Phoinx]

Hello,

 

I am currently working on a survey script, where administrators are able to create, view and edit surveys.

 

The problem is now that when an admin is viewing the answers i want them to be able to choose everyone who answered yes to question 1 and then view what that specific group answered to all the other questions. It should also be possible to choose the ones who answered yes to question 1 and no to question 2 etc.

 

The problem is that i don't know how my database-structure should be like, and what method i should use for these dependable answers.

 

My current databasestructure is the following:

 

Survey - id, name etc

Question - survey id, question, question-type

Questiontypes - typeId, number (answer number in the type), value (ex. Yes), type (radiobuttons etc)

Answers - user, surveyid, questionid, answer

 

So a user has one databaseentry for every answered question. A single survey contains multiple questions.

 

Hope someone can help me. Please ask if there's something you don't understand, or if i'm expressing something unclearly.

[ignace]

There can be many surveys:

 

surveys (survey_id, ..)

 

Each survey can have many questions:

 

surveys_questions (question_id, survey_id, type_id, ..)

 

Each question is of a certain type (multiple choice, single choice, open, ..). Each question can have many possible answers:

 

surveys_answers (answer_id, question_id, ..)

 

The user answers questions:

 

users_surveys (user_id, question_id, answer_id)

 

It's difficult to create a DB scheme without knowing the entire project background.

[Phoinx]

It is a survey system, which will be used by many different people - therefore it have to be very customizable.

 

It is very essential for some administrators to be able to view different groups' view upon something (it could be what a specific ethnicity thinks about a certain issue) The admin shall be able to choose multiple question answers (see what a specific ethnicity, who thinks the same thing about a certain issue, thinks about other countries - and choose more answers to depend on).

 

The complete DB-structure so far (not sure if this can be used for this function, since i can't figure out a way for this to work)

 

You can create multiple surveys.

 

Table: survey

For creating a survey you need to fill name and description, and then a unique id is generated by auto_increment (primary key)

 

Table:questions

Every question has a unique id, and a reference which links it to a certain survey (field: sid, which contains the surveys unique id).

Then there is a VARCHAR field which contains the question.

The next field is qtype, which is a reference to the next table questiontypes

 

Table: questiontypes

Here we have a listId, which links all the question-type-values to ONE list (ex. 4 different entries with value red, green, blue, purple - these are linked together by having the same listId) - the listId is also the one used by qtype in the table questions.

The next field is number, which is just an integer which represents the order of the different answers for the questiontype.

The next field is value which is what the answer is (ex. red)

The last field is type, which is just a representation of how you answer (radio-button etc.)

 

Table: answers

The first field is user, which is the one that links all the answers to the same user (all answers made by the same user have the same value in this field)

The next two fields are sid (survey's id) and qid (the id of the the answered question)

The next field is answer which gets the field number from question-types (if value=red and number=1, then if answer here is 1, the user will have chosen the answer red for this question)

 

Would like to know, if I can create this function (choose specific answers) and view what this group answers from this DB-structure, or if i will have to make another structure.

 

I can't make a structure where a user has one entry which has fields like answer1, answer2, answer3 (which contains the answer to question 1,2,3) because it has to be so customizable.

 

Hope this is help enough.

[ignace]

surveys (survey_id, survey_name, survey_description)

surveys_questions (question_id, survey_id, type_id, correct_answer_id, question_label)

surveys_questions_types (type_id, ..)

surveys_answers (answer_id, question_id, answer_label, order)

surveys_users_answers (user_id, question_label, answer_label)

surveys_users_groups (group_id, group_name)

surveys_users (user_id, user_name, user_password, ..)

 

I think this should do it for you. Questions and answers can probably be edited so therefor I decided to store the question_label and the answer_label when a user answers a question. Your programming logic should know what kind of type the question was and handle it properly (ie multiple-choice stores question_label for each chosen answer)

[Phoinx]

The problem is still how I can show on/y answers from people who gave a specific answer to a specific question and be able to choose multiple questionanswers to depend on. I can ma?e a single one by inserting all users who gave a specific answer into an array and then make the other query WHERE userid = in this array, but im not sure how to do for multiple answers more

[ignace]

SELECT ..

FROM surveys_users_answers

JOIN surveys_users USING user_id

WHERE question_label = '$qlabel' AND answer_label = '$alabel'

 

For multiple correct answers:

 

SELECT ..

FROM surveys_users_answers

JOIN surveys_users USING user_id

WHERE question_label = '$qlabel' AND answer_label IN ('$alabel', ..)