You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

Using Integers to Store Bits of Information

Using Integers to Store Bits of Information

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:

1Interior Options
2Exterior Options

10Leather Seats
11Cloth Seats
12Power Seats
13CD Player
14AM/FM Radio
20Power Windows
21Rear Spoiler
22Fog Lights
23Chrome Rims
24Heated Rear View Mirrors

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"

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>

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"
<cfif IsNumeric(getDetails.interiorOptions)
AND BitAnd(getDetails.interiorOptions, 2 ^ bitValue) GT 0>

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"
<cfif IsNumeric(getDetails.interiorOptions)
AND BitAnd(getDetails.interiorOptions, 2 ^ bitValue) GT 0>

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.

More Stories By Tom Nunamaker

Major Tom Nunamaker, is a USAF T-37 instructor pilot and has been programming since 1974 in various languages. He started ColdFusion programming in 1996 and is a Certified Advanced ColdFusion 5.0 Developer. His works include integrating pilot flight plans to the European Air Traffic Control System for the USAF, www.allpme.com and www.morervs.com. He has posted several custom tags at www.toshop.com. His main hobby is playing the viola and violin.

Comments (2) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

Most Recent Comments
Kevin Nechodom 10/21/02 04:06:00 PM EDT

Like anything else, there are tradeoffs. And if I was up against space considerations, then bit flags will optimally use space.

However, storing bits in ints goes against the grain of DBMS design. Every field is supposed to be atomic. It can't be decomposed into other fields. Storing a field that has to be split into sub-fields is not an optimal design. The question before was one of querying. If you have to pull the data into the application to pull apart the field in order to decide if you want the record makes for inefficency.

Now, the good news. SQL Server DOES have bit fields, and you can gain the advantages of space and still keep stay within the constraints of queryability and atomicity. However, that does leave us Oracle guys out in the cold.

Like I started, it is all tradeoffs. However, when I have let the DBMS design rules slide, I have almost ALWAYS gotten 'bit'.

Andrew McKellar 10/17/02 08:59:00 PM EDT

Very good and interesting article.

One question though, if for example I just want to pull out of the DB all cars that have 'fog lights' for example, how do I do that with a query seeing as the value is within an integer field in the DB?

IoT & Smart Cities Stories
If a machine can invent, does this mean the end of the patent system as we know it? The patent system, both in the US and Europe, allows companies to protect their inventions and helps foster innovation. However, Artificial Intelligence (AI) could be set to disrupt the patent system as we know it. This talk will examine how AI may change the patent landscape in the years to come. Furthermore, ways in which companies can best protect their AI related inventions will be examined from both a US and...
The challenges of aggregating data from consumer-oriented devices, such as wearable technologies and smart thermostats, are fairly well-understood. However, there are a new set of challenges for IoT devices that generate megabytes or gigabytes of data per second. Certainly, the infrastructure will have to change, as those volumes of data will likely overwhelm the available bandwidth for aggregating the data into a central repository. Ochandarena discusses a whole new way to think about your next...
Charles Araujo is an industry analyst, internationally recognized authority on the Digital Enterprise and author of The Quantum Age of IT: Why Everything You Know About IT is About to Change. As Principal Analyst with Intellyx, he writes, speaks and advises organizations on how to navigate through this time of disruption. He is also the founder of The Institute for Digital Transformation and a sought after keynote speaker. He has been a regular contributor to both InformationWeek and CIO Insight...
Bill Schmarzo, Tech Chair of "Big Data | Analytics" of upcoming CloudEXPO | DXWorldEXPO New York (November 12-13, 2018, New York City) today announced the outline and schedule of the track. "The track has been designed in experience/degree order," said Schmarzo. "So, that folks who attend the entire track can leave the conference with some of the skills necessary to get their work done when they get back to their offices. It actually ties back to some work that I'm doing at the University of ...
DXWorldEXPO LLC, the producer of the world's most influential technology conferences and trade shows has announced the 22nd International CloudEXPO | DXWorldEXPO "Early Bird Registration" is now open. Register for Full Conference "Gold Pass" ▸ Here (Expo Hall ▸ Here)
@DevOpsSummit at Cloud Expo, taking place November 12-13 in New York City, NY, is co-located with 22nd international CloudEXPO | first international DXWorldEXPO and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time t...
CloudEXPO New York 2018, colocated with DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.
The best way to leverage your Cloud Expo presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering Cloud Expo and @ThingsExpo will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at Cloud Expo. Product announcements during our show provide your company with the most reach through our targeted audiences.
The Internet of Things will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform and how we integrate our thinking to solve complicated problems. In his session at 19th Cloud Expo, Craig Sproule, CEO of Metavine, demonstrated how to move beyond today's coding paradigm and sh...
What are the new priorities for the connected business? First: businesses need to think differently about the types of connections they will need to make – these span well beyond the traditional app to app into more modern forms of integration including SaaS integrations, mobile integrations, APIs, device integration and Big Data integration. It’s important these are unified together vs. doing them all piecemeal. Second, these types of connections need to be simple to design, adapt and configure...