A
Amit
Hi,
I regularly design and develop small databases (20-300 records) that store
data from surveys. I will appreciate it if I can get some advice on designing
the tables, as I have a feeling it could be improved upon.
Generally, there are 2 kinds of questions on a survey:
1. Question that has one answer to be checked from given (mutually
exclusive) options, and sometimes has a descriptive/qualitative answer.
e.g. What region is your program located in? (check one)
(assuming that a program can have only one location)
a. Central
b. Northeast
c. Western
d. Southeast
e. Don't Know
f. Other
Describe Other ____________
Also implicit in this question are 2 other answers: "Did not answer" and
"N/A" , which are not on the actual survey, but need to be coded in the
database/table (possibly based on the answer to a previous question, the
respondent is not required to answer this question, hence N/A)
2. Question that has multiple answers from given options.
e.g. What age-groups does your program serve? (check all)
a. Preschool ( < 4)
b. Children (5 - 12)
c. Teenagers (13 - 17)
d. Adults (18-64)
e. Seniors (>65)
The way I've been designing the tables is to have a table- tblSurveyData,
with the following fields:
-- SurveyID* (Autonumber)
-- Q1_Region (Number) [Combo-box in Form]
-- Q1_OtherDescr (Text) [Textbox in Form]
-- Q2_NoAnswer (Yes/No) [Checkbox in Form]
-- Q2_NotApplicable (Yes/No)
-- Q2_Preschool (Yes/No)
-- Q2_Children (Yes/No)
-- Q2_Teenagers (Yes/No)
-- Q2_Adults (Yes/No)
-- Q2_Seniors (Yes/No)
and the lookup table, tlkpQ1, with the following values:
Q1_ID Q1_Descr
====================
1 No Answer
2 Not Applicable
3 Central
4 Northeast
5 Western
6 Southeast
7 Don't Know
8 Other
=====================
Based on the answers to 'Other', new Regions could be added to the list, and
the answers recoded.
~
Is this the most efficient/elegant way to design tables for such instances?
Are there any pitfalls in this design that I should be aware of? In instances
of Q2, would it be better to have a separate table, instead of storing values
in the main table? Maybe tlkpQ2 (Q2_ID*, Q2_Descr), and tblQ2 with SurveyID
and Q2_ID as foreign keys in it??
How would I implement Q2 in a form if there is a separate table for the
answers?
We get a bunch of filled out, anonymous surveys, and I design the database,
then the data is entered, and I design+run queries for some basic data
analysis (frequencies, simple cross-tabs).
Will appreciate any thoughts on this.
Thanks!
-Amit
I regularly design and develop small databases (20-300 records) that store
data from surveys. I will appreciate it if I can get some advice on designing
the tables, as I have a feeling it could be improved upon.
Generally, there are 2 kinds of questions on a survey:
1. Question that has one answer to be checked from given (mutually
exclusive) options, and sometimes has a descriptive/qualitative answer.
e.g. What region is your program located in? (check one)
(assuming that a program can have only one location)
a. Central
b. Northeast
c. Western
d. Southeast
e. Don't Know
f. Other
Describe Other ____________
Also implicit in this question are 2 other answers: "Did not answer" and
"N/A" , which are not on the actual survey, but need to be coded in the
database/table (possibly based on the answer to a previous question, the
respondent is not required to answer this question, hence N/A)
2. Question that has multiple answers from given options.
e.g. What age-groups does your program serve? (check all)
a. Preschool ( < 4)
b. Children (5 - 12)
c. Teenagers (13 - 17)
d. Adults (18-64)
e. Seniors (>65)
The way I've been designing the tables is to have a table- tblSurveyData,
with the following fields:
-- SurveyID* (Autonumber)
-- Q1_Region (Number) [Combo-box in Form]
-- Q1_OtherDescr (Text) [Textbox in Form]
-- Q2_NoAnswer (Yes/No) [Checkbox in Form]
-- Q2_NotApplicable (Yes/No)
-- Q2_Preschool (Yes/No)
-- Q2_Children (Yes/No)
-- Q2_Teenagers (Yes/No)
-- Q2_Adults (Yes/No)
-- Q2_Seniors (Yes/No)
and the lookup table, tlkpQ1, with the following values:
Q1_ID Q1_Descr
====================
1 No Answer
2 Not Applicable
3 Central
4 Northeast
5 Western
6 Southeast
7 Don't Know
8 Other
=====================
Based on the answers to 'Other', new Regions could be added to the list, and
the answers recoded.
~
Is this the most efficient/elegant way to design tables for such instances?
Are there any pitfalls in this design that I should be aware of? In instances
of Q2, would it be better to have a separate table, instead of storing values
in the main table? Maybe tlkpQ2 (Q2_ID*, Q2_Descr), and tblQ2 with SurveyID
and Q2_ID as foreign keys in it??
How would I implement Q2 in a form if there is a separate table for the
answers?
We get a bunch of filled out, anonymous surveys, and I design the database,
then the data is entered, and I design+run queries for some basic data
analysis (frequencies, simple cross-tabs).
Will appreciate any thoughts on this.
Thanks!
-Amit