Survey database design... (1 Viewer)

[KKilfoil]

{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.

 

[Pat Hartman]

I think your question type isn't going to work. I think you need to define sets of valid answers for each question with the valid answer number stored with the question. Multiple-choice questions have 4 entries or whatever number you chose. Yes/No and choose a number between 1 and n are types that don't need further definition execept an indication as to what answer is correct.

 

[KKilfoil]

Perhaps I should have named the QuestionType table to be AnswerType to be more intuitive.

I DO intend to do much as you suggest. Let me put in some sample data to show what I mean:

tbl_QuestionType ( soon to be AnswerType)
1, YesNo
2, Range1to5
3, Range10to40byTens
4, WindowsOS

tbl_ValidAnswers
1,1,-1,"Yes"
2,1,0,"No"
3,2,1,"1"
4,2,2,"2"
5,2,3,"3"
6,2,4,"4"
7,3,1,"10 (Ten)"
8,3,2,"20 (Twenty)"
9,3,3,"30 (Thirty)"
10,3,4,"40 (Forty)"
11,4,1,"Windows 3.1"
12,4,2,"Windows 95"
13,4,3,"Windows 98"
.
.
.
21,4,11,"Windows Vista"
22,4,12,"I do not know"
etc.

So if a question is of AnswerType 2, a listbox can be prepopulated to show {1,2,3,4}, but if the question has AnswerType of 4, the listbox shows {Windows 3.1,....,Windows Vista,I do not know}

Does that make sense? The "AnswerType" defines an appropriate re-usable answer-set, and EACH question has an FK link to a particular answer-set.

In turn, I want each question to be re-usable on more than one survey type, in case questions are rerun. The nature of these surveys is that there can be a lot of similarity between questionaires.

So, each instance of a response will be for a particular SurveyType (which is a particular subset of available questions). The SurveyQuestionLink table is where the question set for a particular SurveyType is managed

 

[MStCyr]

Good morning

I did post a survey database last year in the Sample Database area ... the app had been developed some years ago. I realize that it is nowhere's near what you're asking for, however it may be of some use to you.

http://www.access-programmers.co.uk/forums/showthread.php?t=113955

Best regards

Maurice

 

[KKilfoil]

Perhaps I should mention that the AnswerType and ValidAnswer tables are only being used for data input convenience and validation.

The actual answers themselves for each response are stored in tbl_Answers, which effectively is a M:M linking table between tbl_Response and tbl_Question. So there needs to be a record in tblAnswers for EACH question for EACH response. tblSurveyQuestionLink, once filtered for the current surveytype, provides the recordsource for an Append Query into tbl_Answers for the 'blanks' for the user to fill in.