When designing a table to store information from a questionnaire that includes both single option and checkbox responses, it is important to consider the structure of the table. In this article, we will discuss how to effectively store checkbox responses in SQL Server.
Sample Questions
Let’s consider a questionnaire with two types of questions:
Type 1: Single Option
- Which of the following systems does your company use or plan to use?
- Windows 2000
- In Use
- Within 12 months
- No plans
- Don’t Know
- Windows 2000
Type 2: Multiple Options
- Which of the e-mail systems does your company use (check all that apply)?
- Eudora
- AOL Mail
- Exchange (Microsoft)
- CC:Mail
- Outlook Express
- Outlook 97 or higher
- Groupwise
- Don’t Know
Table Setup
To store the questionnaire responses, we can create two tables:
Table 1: tblCompany
- ID
- Name
- Address
Table 2: tblAnswers
- CompanyID
- QuestionNumber
- Answer
The tblCompany table holds the company information, while the tblAnswers table stores the answer information for the questionnaire. This setup works well for single option questions, but what about checkbox answers?
Storing Checkbox Answers
To handle checkbox responses, we can 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:
| Checkbox | 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 obtain the integer value that needs to be stored in the database. For example, if all 8 checkboxes were checked, the value would be 255. If only checkboxes 2 and 5 are checked, the value would be 18.
In a follow-up article, we will demonstrate how to query the database for specific results based on these stored checkbox responses.