Storing Responses
How do you structure a table to store information on a questionnaire that has two types of responses options and checkboxes?
Sample Questions:
Type 1 Single Option
1. Which of the following systems
does your company use or plan
to use?
a. Windows 2000
i. In Use
ii. Within 12 months
iii. No plans
iv. Don't Know
Type 2 Multiple Options
2. Which of the e-mail systems
does your company use (check
all that apply)?
a. Eudora
b. AOL Mail
c. Exchange (Microsoft)
d. CC:Mail
e. Outlook Express
f. Outlook 97 or higher
g. Groupwise
h. Don't Know
Table Set Up:
A table was created with company information.
NAME: tblCompany
Fields:
ID
Name
Address
Another Table was created with the following answer information.
NAME: tblAnswers
Fields:
CompanyID
QuestionNumber
Answer
The table setup does a good job holding the company information and answer information for option button type questions but what about checkbox answers? To solve this problem we decided to store the information as integer values representing the binary notation of the numbers.
For example, if a question has 8 possible choices we would have a matrix with the following.
| Answer |
Value if Checked |
Value if not Checked |
| Checkbox 1 |
1 |
0 |
| Checkbox 2 |
2 |
0 |
| Checkbox 3 |
4 |
0 |
| Checkbox 4 |
8 |
0 |
| Checkbox 5 |
16 |
0 |
| Checkbox 6 |
32 |
0 |
| Checkbox 7 |
64 |
0 |
| Checkbox 8 |
128 |
0 |
By summing the values together you can get the integer you need to store in your database. If all 8 boxes were checked your value is 255. If only box 2 and 5 are checked the value is 18.
In my follow-up article I will demonstrate how to query for specific results.
