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:

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:

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