{moved from Theory... forum, as I think this is more of a 'general' question}
I am trying to set up a database that manages responses to a variety of survey questionaires, and I want to know if I am on the right track before I proceed. Sorry if this gets a bit verbose:
Here's the general 'business rules':
1) There are many different historic survey types. Some have also not yet been defined.
2) A specific question may appear on more than one survey type.
3) Each survey type can have a different number of questions.
4) The 'valid' answers to a survey question fall into two general groupings:
- multiple choice (variable number of choices)
- freeform text (and "other__________ " could be a multiple choice response)
5) There will be a lot of repetition in the multiple-choice choices. For example, there will be lots of "TRUE/FALSE" or "YES/NO" questions, lots of 'Rate the following on a scale of 1 to 5' type questions, etc
6) Some of the multiple choice valid-answer-sets may be more unique, such as '200,400,600,1000,other'.
7) We (sometimes) want to capture such information as 'don't know', or 'answer illegible' or 'not answered' or 'don't care', but these can just be more 'pre-set' choices in the db that do not appear on the paper forms
After some whiteboarding, I arrived at the attached db structure.
Essentially, each actual survey questionaire, once completed, becomes a response. Each response is to a specific survey type (1:M), and will have answers to 'many' questions per response (M:M). Each specific 're-usable' question will have answers of a particular type (i.e unique valid-answer-set, 1:M), and SOME of the questions will use the 'QuestionAllowFreeForm' boolean field to allow users to input freeform text.
The SurveyQuestionLink table manages the M:M relationship between SurveyType and the Questions that allows for flexible survey defining, and will act as the 'template' to append-query generate the Answer records for data input.
Does anyone see anything that I am missing? Do I need to explain this better? This is part of a larger database with Privacy Act issues, so I cannot easily furnish the actula db.
I am trying to set up a database that manages responses to a variety of survey questionaires, and I want to know if I am on the right track before I proceed. Sorry if this gets a bit verbose:
Here's the general 'business rules':
1) There are many different historic survey types. Some have also not yet been defined.
2) A specific question may appear on more than one survey type.
3) Each survey type can have a different number of questions.
4) The 'valid' answers to a survey question fall into two general groupings:
- multiple choice (variable number of choices)
- freeform text (and "other__________ " could be a multiple choice response)
5) There will be a lot of repetition in the multiple-choice choices. For example, there will be lots of "TRUE/FALSE" or "YES/NO" questions, lots of 'Rate the following on a scale of 1 to 5' type questions, etc
6) Some of the multiple choice valid-answer-sets may be more unique, such as '200,400,600,1000,other'.
7) We (sometimes) want to capture such information as 'don't know', or 'answer illegible' or 'not answered' or 'don't care', but these can just be more 'pre-set' choices in the db that do not appear on the paper forms
After some whiteboarding, I arrived at the attached db structure.
Essentially, each actual survey questionaire, once completed, becomes a response. Each response is to a specific survey type (1:M), and will have answers to 'many' questions per response (M:M). Each specific 're-usable' question will have answers of a particular type (i.e unique valid-answer-set, 1:M), and SOME of the questions will use the 'QuestionAllowFreeForm' boolean field to allow users to input freeform text.
The SurveyQuestionLink table manages the M:M relationship between SurveyType and the Questions that allows for flexible survey defining, and will act as the 'template' to append-query generate the Answer records for data input.
Does anyone see anything that I am missing? Do I need to explain this better? This is part of a larger database with Privacy Act issues, so I cannot easily furnish the actula db.