Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Storing Checkbox Responses as Integers

By Bruce Szabo,

Storing Checkbox Responses as Integers

Storing Checkbox Responses as Integers

In the early part of my career I was in places where I did not do a lot of database database. The database had already been designed or was part of a third party package (like SMS) and there was little design which could be done. As I have begun to work on the web I have been required to design a number of databases. Getting the database design just right is a high priority. It has been my experience that a poorly designed database will lead to a useless database in a short amount of time. A well designed database on the other hand is timeless.

Below is a method we used to store data for a questionaire. If you have any thoughts or feelings about the design do not hesitate to write me.

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.
Total article views: 4172 | Views in the last 30 days: 2
 
Related Articles
FORUM

Database design

Very Large database design

FORUM

Database Design

Designing problem to store Geaometric shapes

FORUM

Database Design Help

database design help schema

FORUM

Database Design Question

Database Design Question

FORUM

Database Design

Database Design for dynamic fields

Tags
basics    
database design    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones