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 |
|
uberman
Posting Yak Master
159 Posts |
 Posted - 2004-03-26 : 05:12:09
|
| I am stuggling to work out how best to design the db structure for a large questionnaire based systemThe questionnaire is pretty long (14+ sections, 6+ questions per section, often with multiple options per question), plus there are two supplimentary questionnaires of approximately the same size (I have included information about the sort of answers I have to handle at the end).The whole system is based around this questionnaire (with other bits like security and access levels, but I can cope with them).I can't seem to get a handle on the best way to start thinking about this.I have currently come up with two approaches1) One table per section or question to hold all the answers2) Working out the different types of answers, and coming up with a system which includes tables likeQuestionsQuestion OptionsQuestionAnswers (x N, which N is the number of different types of questions, for some of the more complex answers may need additional tables)ThoughtsI think 2) is to complex for what is required, if this was a dynamic, lets create lots of questionnaires then I might take this route, but it is a one off built around a specific instance;butIntially I didn't like 1) as there would be a lot of reptition (plus creating the output is going to be bit of pain, but given the complex/different nature of the answers it is going to be a pain regardless of which approach I take); but I am now coming to think that 1) would actually be the best approach.I would appreciate any comments/help/pointers/advice to help me out.More InformationThe answer options take numerous forms, the following are some examplesPlain text answerYes/NoYes/No - if yes supply detailsSingle Option (Pick A/B/C/D)If B supply details and attach the following documentsMulitple Option (Select all that apply A/B/C/D)If A or B or C supply details for each plus documents for B |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-26 : 08:07:37
|
| Have you thought about something like:Section-------SectionNameSectionDescriptionQuestions---------QuestionIDQuestionIDParentSectionNameQuestionQuestionOrderInQuestionYesNoMatrixInQuestionPlainTextMatrixInQuestionSingleOptionMatrixInQuestionMultipOptionMatrix--This would let you like options or sections of the question together. You could also have an order by to order the questions per section.This table would also let you drive one QuestionAnswer query based on the fact you now know from this one table the answer exists in one or more answer tables.You can have an answer table for each type of answer giving you the possible answers. They can either just be straight answers, or they can be particular to the question by throwing the QuestionID in there.You then join the answer tables to the question tables in the matrix specified in your Question table to give you the possible joins.The last thing you will need is a QuestionAnswer history which is another matrix table of what they actually chose.This might at least give you some ideas.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
|
|
|
|
|
Design Help - Questionnaire