SQL Bitmask
The Boolean is the simplest of the data types, until your working with millions of records and need to be able to add booleans easily. Our geek ancestors solved this problem by using the low level value of Integers as an array of true/false flags rather then a binary representation of a number.
Lets say we have three possible options each having a binary value:
CHEDDAR = 0001
SWISS = 0010
CURD = 0100
GRUYERE = 1000
Lets plugin a little boolean algebra and order a couple burgers:
new_burger(CHEDDAR | SWIS)
0001 Cheddar
| 0010 Swiss
0011 Cheddar & Swiss
new_burger(SWISS | GRUYERE)
0010 Swiss
| 1000 Gruyere
1010 Swiss & Gruyere
new_burger(CHEDDAR | SWISS | CURD | GRUYERE)
0001 Cheddar
0010 Swiss
0100 Curd
| 1000 Gruyere
1111 Cheddar & Swiss & Curd & Guryere
Now testing for a value is a simple comparision. Lets test if we asked for cheddar or gruyere on the first burger.
0011 Cheddar & Swiss
& 0001 Cheddar
0001 > 0 == true
0011 Cheddar & Swiss
& 1000 Gryuyere
0000 > 0 = false
We can also perform the same comparisons in SQL which we can use to build a Sphinx index. You can also query by the comparison, however I can’t imagine it would be very effective on a large scale.
sqlite> CREATE TABLE burgers (cheese_bitmask smallint);
Add Swiss & Gruyere burger
sqlite> INSERT INTO burgers VALUES (10);
Is there a burger with Swiss?
sqlite> SELECT COUNT (*) FROM burgers WHERE (cheese_bitmask & 2) > 0;
1
Is there a burger with curd?
sqlite> SELECT COUNT (*) FROM burgers WHERE (cheese_bitmask & 4) > 0;
0
And there you have it, bitmasks in your SQL. I have only tested this in MySQL and sqlite3, I would think all databases support bitwise operations, although the & (and) and | (or) operators may be different.