Storing on/off switches through binary representation

As part of the Nightlife Project, you will have to store some data through binary representation in the payment_rates table. While doing the writeup on the MySQL schema, I decided that this section was too large to easily fit inside of that article and it should be its own article. The goal of this article is to explain how database schemas employ the principles of binary notation to store a sequence of on/off flags in a single field.

Imagine this scenario: You are the head of maintenance at an office and are trying to determine which lightbulbs get the most usage and should be replaced with high efficiency bulbs. In order to determine which gets the most usage, you walk through the building in the morning, at lunch and again at night and make a note of which bulbs are on and which are off. Once you’ve gathered your data, you store the information in a database and after 2 months, you will view the results and determine the top used bulbs and replace them with higher efficiency bulbs to save electricity.

After each walk through the building, you have a list of lights and a corresponding on/off value for each one. Your list may look something like this:

Reception Break Room Bathroom Office 1 Office 2 Office 3 Office 4 Hallway Copy Room
On Off Off Off On On Off On Off

The obvious way to store this data is to create a table called ‘lights’ and give it 10 fields – a timestamp (we need to know when this walk through occurred, after all) and a field for each of the 9 rooms. However, this obvious way has a few nasty shortcomings. What if you decide to add additional lights to be checked, such as desk lamps or the front walkway? Adding additional fields rarely ends well. This is also extremely inefficient, as you now have an 10 field table when a 2 field table would suffice.

Instead of storing it through this obvious, but inefficient method, instead you can convert that list of on/off switches into a binary string. Using the same data above, the binary string would look like this: 100011010. Each of the lights that are on is a 1 and each light that is off is a 0. Each character represents a room – the first position is reception, the second position is break room, etc. Converting this binary string to decimal, 100011010 becomes 282 (why?).

Reception Break Room Bathroom Office 1 Office 2 Office 3 Office 4 Hallway Copy Room
1 0 0 0 1 1 0 1 0

The key thing behind storing binary information in a database is this: no other combination of binary will equal that number. There is simply no other set of on/off values that will add up to 282. This lets us store an infinite amount of on/off values in a single integer slot, without ambiguity and always available for reading. As far as how you will read it, look into your language’s “bitwise and” and “bitwise or” operators. An example in PHP would be:

$user_permission = 6; // binary 0110

$view            = 1; // binary 0001
$edit            = 2; // binary 0010
$create          = 4; // binary 0100
$delete          = 8; // binary 1000

if($user_permission & $view)
    echo 'user can view';

if($user_permission & $edit)
    echo 'user can edit';

if($user_permission & $create)
    echo 'user can create';

if($user_permission & $delete)
    echo 'user can delete';

You can use a system like this for storing user permissions(which is how *nix permissions work), which days of the week an event occurs(the nightlife project will be doing this for storing payment rates), or any other piece of information that can be summed up in a series of on/off switches. Using binary storage is extremely efficient and flexible. Remember, however, that any additional fields to be added should be added to the left side of the string to preserve old data.

, , ,

  1. #1 by Tinus on June 14, 2010 - 9:05 am

    Ah, right. Makes sense. Should have thought of that. Either way, nice article, keep it up!

  2. #2 by visual77 on June 14, 2010 - 8:39 am

    Good point, and this is a concept I should elaborate upon in this article. MySQL has bitwise operators just as PHP does. To get all records with a particular light set, you would structure the SQL query like this:

    “SELECT * FROM `light_walkthroughs` WHERE `light` & 256 > 0″

    256 is the reception listing, as can be inferred through the earlier table listing each of the areas. “`light` & 256 > 0″ is a bitwise operation that will find all entries where the 256 flag (2^8) are enabled.

  3. #3 by Tinus on June 14, 2010 - 6:48 am

    No offence, but doesn’t this defeat the purpose of having a table for the lights in the first place? For example, how would you query the table to return all records where the “Reception” lights are set?

(will not be published)