Imagine that your client, Fast Eddy's Auto World, asks you to build a data entry form for his inventory.

There are several models of cars that use combinations of many options. No two use the same combination. How can you efficiently design the form and database to display the correct options and store the information in a database without constantly adding and deleting fields in your data table? The answer is to use integers and bitwise operators.

We first need to understand how integers are stored in binary format. Our society teaches base 10 to manipulate numbers. We are so familiar with it that we sometimes forget that behind its familiarity lies a system for expressing numbers by using columns representing different values. For example, the number 12 looks different when the columns are exposed:

That can be read as "zero thousands + zero hundreds + one ten + two ones". Computers use base 2 (so you have two conditions: on and off). That same number 12 decimal expressed as binary numbers would be:

This can be read as "one eight + one four + zero twos + zero ones".

Binary numbers have made terrific flags for years since each place is either ON (1) or OFF (0). If you view the 1100 binary number as a series of four flags left to right, we could say:

The left-most flag is ON
The second flag is ON
The third flag is OFF
The fourth flag is OFF

Fast Eddy's form doesn't have flags. It has automobile options. If you assigned names to each flag, you could also say:

The Power windows flag is ON
The Automatic Transmission flag is ON
The Tilt Steering Wheel flag is OFF
The Leather Seats flag is OFF

Let's design two database tables to hold these bits of information (see Figure 1). These tables are tied only to each other and to your form. They aren't connected with referential integrity to your inventory table. Your integer storage fields store the sum of user-chosen bitValues.

In the BitGroups table we'll store the names of each group of information. BitGroups help you organize your collection of bits. In the Bits table we'll store the specifics of each group. Each combination of bitgroup_id and bit is unique. For instance, Fast Eddy might have these BitGroups and bitValues:

To store these bits of information in Fast Eddy's VehicleInventory table, you'd only need two fields, Interior-Options and ExteriorOptions. If either of these fields is zero, then none of that BitGroup's options are checked (all of the flags are zero). If any combination is checked, you add the bitValues raised to the power of 2. That's why we start with a bitValue of zero: 2 to the zero power is 1, or our first flag position. You can store the powers of 2 in the database Bits table but I prefer not to have ColdFusion do the math for me.

Typical 32-bit integers, like those found in SQL Server, can handle 31 bits of in-formation. This is the maximum number of bits one integer can store. To store more than that, you have to use multiple storage integers. In Fast Eddy's case we can divide his options into logical groups such as Interior and Exterior Options. If you further break each of these groups into two, you can display these options in two columns on your HTML form. Most forms won't require 31 fields in each of two columns down the page. Keeping your integers storing fewer than 31 bits of information leaves future expansion possibilities. If Fast Eddy's option lists expand so much that you have to exceed 31 bits, you'll have to split your options into smaller groups.

Let's display the checkboxes for Fast Eddy's automobiles. We could generate the powers of 2 now, but if the checkboxes aren't checked, it's a bit of wasted processing. Let's do the binary conversion after the form is submitted. Here are our checkboxes:

<input type="checkbox" name="interiorOptions_0" value="1">Leather Seats<br>
<input type="checkbox" name="interiorOptions_1 value="1">Cloth Seats <br>
<input type="checkbox" name="interiorOptions_2" value="1">Power Seats <br>
<input type="checkbox" name="interiorOptions_3" value="1">CD player <br>
<input type="checkbox" name="interiorOptions_4" value="1">AM/FM radio <br>

The ColdFusion code to generate those checkboxes would be:

<cfoutput query="getBits1">
<input type="checkbox"
name="interiorOptions_#bitValue#"
value="1">
#bitTitle#<br>
</cfoutput>

After the user selects the appropriate options, the receiving page has to add up the values before inserting or updating the vehicleInventory table. Since we haven't converted the choices to powers of 2, we'll check to see if the checkbox exists and calculate the bitValue here. This is the code to add up the checkbox values:

<cfset interiorOptionsTotal = 0>
<cfloop index="i" from="0" to="4">
<cfif IsDefined("form.interiorOptions_#i#")>
<cfset interiorOptionsTotal = interiorOptionsTotal + 2 ^ i>
</cfif>
</cfloop>

Use the interiorOptionsTotal variable to place into your record. If no checkboxes were selected, the value is zero. If any were selected, their values are added to create a unique combined number. For instance, if Power Seats (value of 2 ^ 2 = 4) and CD Player (value of 2 ^ 3 = 8) were selected, their sum (12 decimal or 1100 binary) would be inserted into the InteriorOptions field for that vehicle. Note that no other combination of options adds up to 12. It is always this exact combination.

When we edit the form with existing data, we have to figure out which bits were checked when we display the form. Here's a revised version of our code to generate the checked="checked" in the proper checkboxes. getDetails is the query containing our vehicleInventory data:

<cfoutput query="getBits1">
<input type="checkbox"
name="interiorOptions_#bitValue#"
value="1"
<cfif IsNumeric(getDetails.interiorOptions)
AND BitAnd(getDetails.interiorOptions, 2 ^ bitValue) GT 0>
checked="checked"
</cfif>>#bitTitle#<br>
</cfoutput>

BitAnd function compares the particular bit to the value in the database. If the database flag is set, bitwise AND will be true (GT 0) and checked="checked" will be displayed in the form. IsNumeric is used to check for NULL values. If you don't allow NULLS and define a default value of zero, the IsNumeric() function isn't needed.

The preceding code will generate this HTML, assuming interiorOptions is set to 12 (01100 binary):

<input type="checkbox" name="interiorOptions_0"
value="1">Leather Seats<br>
<input type="checkbox" name="interiorOptions_1"
value="1">Cloth Seats<br>
<input type="checkbox" name="interiorOptions_2"
value="1" checked="checked">Power Seats<br>
<input type="checkbox" name="interiorOptions_3"
value="1" checked="checked">CD player<br>
<input type="checkbox" name="interiorOptions_4"
value="1">AM/FM radio<br>

So "Power Seats" and "CD Player" are selected as we'd expect. Fast Eddy is impressed. He wonders how you're going to handle different models of automobiles, though. He needs a different form for each model in his inventory. You're thinking what a maintenance nightmare it is to maintain that many similar forms. One change has to be replicated in many places. Unless there's a way to exclude particular fields on a particular form. You can build Fast Eddy's forms from one form template by associating the model with a list of options that model doesn't have. If Fast Eddy's cars were wildly different, you could approach it by including only the designated options. We'll assume that most cars are about the same and deal with excluding the different options from each model.

Let's modify the VehicleModels table and add a field called BitExcludeValue with a default value of zero (see Figure 2).

If you want to exclude a bit from one vehicleModel, set the BitExcludeValue to the sum of all bitValues for the particular form that Fast Eddy wants to exclude. For instance, to exclude "Power Seats" (value 2 ^ 2 = 4) and "AM/FM Radio" (value 2 ^ 4 = 16):

Fast Eddy would store VehicleModel information for each model that includes the BitExcludeValue for that model. When you display the form for that model, you'd query the database to retrieve the BitExcludeValue and use it in the form display code like this:

<cfoutput query="getBits1">
<cfif BitAnd(getDetails.BitExcludeValue, 2 ^ bitValue) IS 0>
<input type="checkbox"
name="interiorOptions_#bitValue#"
value="1"
<cfif IsNumeric(getDetails.interiorOptions)
AND BitAnd(getDetails.interiorOptions, 2 ^ bitValue) GT 0>
checked="checked"
</cfif>>#bitTitle#<br>
</cfif>
</cfoutput>

BitAnd returns logical TRUE when both bits are TRUE (1). If the b itValue matches the BitExcludeValue, they're both TRUE and BitAnd would be TRUE (1) so we won't display the checkbox for that option. If the BitExcludeValue is zero, BitAnd will be FALSE (0) and we'll display the checkbox for that option.

When we display a form with the BitExcludeValue of 20 (10100 binary), we see this:

<input type="checkbox" name="interiorOptions_0" value="1">Leather Seats<br>
<input type="checkbox" name="interiorOptions_1" value="1">Cloth Seats<br>
<input type="checkbox" name="interiorOptions_3" value="1">CD player<br>

Checkboxes in HTML are passed only if they're checked. They are not passed if they aren't checked or if they don't exist on the form. BitValues of 2 and 4 will never appear on our action form since they don't exist on this form with this exclude combination. We're safe in using this form with any combination of checkboxes on any of Fast Eddy's forms.

When Fast Eddy tells you a new option is available, just add it to the bits table. You don't have to redesign your database tables by adding or deleting fields as options change over time. If an option changes its name (Leather Seats changes to Corinthian Leather Seats), just update your Bits table.

You aren't limited to storing form checkboxes with this type of database storage method, of course. Hal Helms's bitwise security model uses this same idea to store user permissions. It's a great way to store a large number of flags in a compact space.

• Figure 1
• Figure 2