Storing Multiple Statuses Using an Integer Column

How many applications do you have that capture a status field for one
reason or another? Do these status fields support only a single status
value at a time? Or does the single status field support multiple different
statuses being associated with a given record at the same time? This article
will discuss how to use an integer field to support having multiple statuses assigned
to records at the same time.

An integer field is just a number,
but how is that number stored in a computer? Numbers are stored as a series of
ones and zeros, right? Remember your binary math from your Computer Science
101 class, where you learned that a byte of information is made up of 8 bits,
where each bit is either a one or a zero. Knowing this we can utilize each bit
of an integer column to represent a different status code. Therefore, a single
integer field can maintain multiple statuses at the same time for a single row,
where each status is represented by using a single bit of the integer column.
Now to better understand how to utilize the integer column for supporting
multiple statuses let me go into a little more detail.

Say I needed to have a table
where each record contains information about students. In this table, I wanted
to track the different activities that are associated with each student. The
different activities I wanted to track consist of the following different
statuses: late arrival, early dismissal, honor society member, member of the band,
member of the choir, teachers aid, student mentor, and running start, where any
given student could perform one or more of these activities or none at all.

Now to keep track of each
one of these statuses for a given student I do not want to have a different
column in my table for each one of these statuses, since most students only
perform one, or none of these activities. Therefore, I decided that I want to use
a different bit of a 4-byte integer column to represent each of these statuses.
For example, say a student had late arrival, was in the honor society, and was
a member of the band, then a binary value of 00001101 would represent the
status of this student, where the first bit tells whether they had late
arrival, the second bit identifies whether they had early dismissal, the third
bit whether they where in the honor society, the forth bit whether they where a
member of the band, and so on. When I store this binary value in my table, the
integer field will have the value 13. The following Table shows the different
integer values that represent each of the different student statuses I want to
track:

Status

Integer Value

Late Arrival

1

Early Dismissal

2

Honor Society Member

4

Member of the Band

8

Member of the Choir

16

Teachers Aid

32

Student Mentor

64

Running Start

128

To identify the different
integer values associated with different combinations of statuses becomes a
little mind-boggling. But rest assured there is no need for you to remember
that the integer value 13 means the student has statuses of “Late Arrival,
Honor Society Member and is a Member of the Band”, because there is a way
programmatically to handle this in a way that is easy to use and code. This easy
way is by using the OR and the AND bitwise operators. Using these operators
lets you easily set and retrieve the statuses from this single integer column.
Let’s go through an example to demonstrate. Say I have five students with the
different statuses indicated in the following matrix:

Student

Status

Fred

late arrival, early
dismissal

Barney

honor society member, member
of the band, Running Start

Wilma

member of the choir,
teachers aid, student mentor

Dick

member of band

Jane

As you can see, each student
has one or more statuses associated with him or her, except for Jane, who has
no status at all. So how do I populate a status column programmatically with
T-SQL code? To demonstrate how to set at integer status lets go through an
example. But first I need to create a student table that can be populated with
records, where one of the columns will be the integer “Status” column. Here is
a stripped down table definition that only has two columns, which will work for
my example:

create table Student (StudentName varchar(10), Status int)

Now that we have the table,
we need to populate the table with the first set of statuses identified in the
above matrix for Student “Fred”. The student “Fred” has a status of 1 (Late
Arrival) and 2 (Early Dismissal). To insert Fred’s record into my Student table
I use the following command:

insert into Student values ('Fred',1|2)

As you can see, I use the
notation “1|2” to populate my “Status” column. In case you are not familiar
with this logic I am using the OR bitwise operator (the vertical bar “|”). Let’s
review how this works. If you were to think of the binary representation for
the status value for “Late Arrival” (integer value 1) it would be “00000001”,
where as the binary value “00000010” represents a status of “Early Dismissal” (integer
value 2). When these two binary numbers are OR’d together you get the binary
value of “00000011” or and integer value of 3. Here are the insert statements
needed to populate all the Student records:


insert into Student values (‘Fred’,1|2)
insert into Student values (‘Barney’,4|8|128)
insert into Student values (‘Wilma’,16|32|64)
insert into Student values (‘Dick’,8)
insert into Student values (‘Jane’,0)

If I return all the records from
my Student table, the results look like this:


Student Status
——- ——
Fred 3
Barney 140
Wilma 112
Dick 8
Jane 0

As you can see, when
multiple statuses are associated with a Student it becomes hard to determine
which individual statuses are set when reviewing the value of the Status column.
To make this kind of status field work for an application there needs to be a
way to easily determine whether a particular student has an individual status
set. To do this I can use the bitwise AND operator (&). Let’s look at an
example of how this works. Here is some code that returns all the Student
records where the student has the “Member of the Band” status set, as one of
their status settings.

select * from Student where Status&8 = 8

The following records where
returned when I ran this query:


Student Status
——- ——
Barney 140
Dick 8

In the code above I used the
coding logic “StudentStatus&8 = 8” to identify the students that are
members of the band. How does this work? First, remember the integer status
value for members of the band is 8. So by using the bitwise AND operator
(&), I took the Status value and AND’d it with the value 8. Now to review
this in terms of the binary AND operation it would look like this for the
Student record where the StudentName column is set to “Barney”:


10001100
& 00001000
00001000

By doing this AND operation
only two possible values could be returned, either 0, or 8. If the value 8 is
returned from this AND operation then the Student is a member of the band,
where as if 0 is returned the student is not a member of the band.

Your application might have
the requirement to return records where more than one status is set. If you
need to search for students that have multiple statuses set, then you need to
use both the OR and the AND operators. Here is a SELECT statement that will
find all the Student records where the student is both in the band (status = 8)
and is participating in running start (status = 128):

 select * from Student where Status&(8|128) = 8|128 

Here I have OR’d together
the value 8 and 128. By doing the OR operation I calculated an integer value
of 136, which represents a student that is both in the band and in running
start. This value is then AND’d with the Status column to determine which Students
are members of the band and participating in the running start program. This ANDing
operation works the same as my prior example but now two different bits are
checked at the same time instead of one.

One of the advantages of
using an integer value to hold different statuses is that you can add new
statuses with out modifying your table definition. All that is needed to add
a new status is to use the next available power of 2 value for each new status.
Another advantage of using this method to store multiple statuses is that this
method takes less disk space then character data to store the same number of
statuses. A single integer column can store 31 different status values and
only takes up 4 bytes.

Storing a number of different
statuses in an integer column may seem a little strange at first, but once you
get use to it, you learn how easy it really is. This article has given you the
basic information needed to exploit an integer column for storing multiple
statuses. Next time your application needs more than just a few statuses for a
single record, you might consider using an integer column to support storing those
multiple statuses for your application.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles