Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hasanali00
Posting Yak Master
207 Posts |
 Posted - 2004-10-18 : 05:15:25
|
| Has anyone ever designed a database to store questionnaire response?I am wondering what can be the easiest way to do so. I can have a table which includes a userID, questionID and answer. But the problem lies in storing Multiple Answers to a question.I have found one article at http://www.databasejournal.com/features/mssql/article.php/1441831Any suggestions on storing and querying questionnaire results?regards |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-18 : 14:24:56
|
| We use three tables to identify the questions, and one to identify the answers.The question tables are:Questionnaire Header (Questionnaire ID / Description of questionnaire)Question Items (Question text, data type for answer)Question Choices (for multiple-choice questions)In the answer table we store UserID, QuestionnaireID, QuestionID, AnswerFor multiple choice questions that allow multiple answers we store them as a comma delimited list.Kristen |
 |
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2004-10-19 : 04:42:30
|
| Thanks Kristen.When u say:' we store them as a comma delimited list'I believe u mean as: "answer1, answer2,..."If I store the answers like this, how easy is it to query the result afterwards using SQL. regards |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-10-19 : 12:33:40
|
| it shouldn't be to difficult but you will have to use some inner joins or similar logics like exists orimplicit joins like from tableA, tableB Where tableA.fk =tableB.pk*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-19 : 14:13:56
|
| "If I store the answers like this, how easy is it to query the result afterwards using SQL"Well, it depends a bit what you want to use them for. In our case the questionnaires tend to be for ad-hoc thigs that the client wants to ask customers - i.e. they have no useful data within our systems that they can JOIN too."What do you think of our website?"Fan***kingtasticVery***kingtasticModerately***kingtasticBrilliant***kingAwfullActually, I'm not quite sure why that particular data-set would allow multiple selections, but Heh! that's presumably what the client said he wanted ...There are a number of Splitter functions available on this forum; they allow you to have a parameter such as "a,b,c" and split that into multple rows of "a", "b" and "c" which can then be joined to otherthings.But, if you are required to put the data in, say, Excel, then you aint got nothing to worry about!SELECT QuestionID, Answer from MyQuestionnairegives: "1", "Cool,VeryCool"Kristen |
|
|
|
hasanali00
Posting Yak Master
207 Posts |
Posted - 2004-10-19 : 18:31:14
|
| thanks |
|
|
|
|
|
|
|
|
Designing database for Questionnaire responses