Storing Multiple Booleans in a Single Integer Column

Fewer columns, you say?!

Multiple boolean columns can be condensed into a single integer column even when they are not mutually exclusive. Say we’ve asked two applicants which days of the week they are available and stored their responses in the database. The range of possible responses to this question include as many as seven days and as few as none. Let’s take a look at the responses we’ve recorded:

Hm. Which days are our two candidates available? Who has the greater availability? The answers to these questions are not immediately obvious. When we take a look at these values in binary the answers become a bit clearer:

       S M T W T F S
Alice  1 0 0 0 0 0 0  = 64
Bob    0 1 1 1 1 1 1  = 63

Even though Alice has the largest number, rendered as 64 as a decimal number or 1000000 in binary, her availability is limited to Sundays only, meanwhile Bob with his 63 is available every day except Sundays. Availability on each particular day of the week can be thought of as a separate boolean value:

      S M T W T F S
Alice T F F F F F F
Bob   F T T T T T T

The boolean nature of a candidate’s availability on a particular day of the week is readily translated into a binary 0 or 1 which means we can chain their entire weekly availability into a single seven-digit binary number. The simplest example here would be a candidate who is available only on Saturdays. This could be thought of as 6 falses followed by a true, or 0000001 in binary, or simply 1 in our integer column in the database. Another candidate available only on Fridays would be stored as 0000010 or 2 in our integer column. Combining Saturday + Friday yields the unique outcome of 3, usefully distinct from Thursday’s 4:

Sat         0000001   1
Fri         0000010   2
Sat + Fri   0000011   3
Thur        0000100   4

This is all well and good for dense storage, but how can we quickly access those candidates who are available on a particular day? SQL has a tool just for this: the bitwise “&” operator. Thursday is 4, so all we need do to retrieve a list of candidates available on Thursday is this query:

SELECT * FROM candidates WHERE days&4=4

If we had instead queried “WHERE days=4” we would have a list of candidates who are only available on Thursday, but the use of the bitwise “&” operator allows our query to retrieve candidates who are available on Thursday regardless of their availability on other days.

Pretty handy for finding all the monsters in your database that are immune to a certain type of attack: