Database Structures with a Large Number of Bit Fields

Question

I have a class of data with a very large number of binary properties--151 (!) to be exact--and I am concerned with how to model this data structurally. Despite the internal efficiencies of storing bit-fields as bytes, my programming spidey senses are tingling at creating a table with 151 bit-fields (in addition to other properties).

There will not be a large number of rows--perhaps 1000 and once sent into production will not change very often.

I've thought of categorizing my data into disjoint subclasses and creating separate tables but splitting the properties in this manner is impracticable and even if possible certainly would not map effectively with the data subclasses. The other issue is I'd like to keep all the data together and avoid field and/or row duplication. I have also considered using some custom binary format but this is not workable as the key field in my data is used as foreign keys in other tables.

Queries will make heavy use of WHERE clauses to extract relevant data. I've considered using multiple longs or int fields but I've rejected this as unworkable since I know of no bit-wise and-operators or functions in SQL and as noted above, classification of the properties is problematic, not to mention other major software engineering issues (with this method).

I will be using PostgreSQL.

So, my question here is do I just make a table with a huge number of fields or are there other methods compatible with the relational model?

Answer 1

Why couldn't you use bit wise operators?

&   bitwise AND 91 & 15 11
|   bitwise OR 32 | 3 35
#   bitwise XOR 17 # 5 20
~   bitwise NOT ~1 -2

from: http://www.postgresql.org/docs/7.4/static/functions-math.html

I would think that you could maybe group them into smaller groups, but other than doing that I don't know of another way.

Answer 2

PostgreSQL supports bits and bit strings natively.

Answer 3

The biggest problem I see is the obvious fact that the cardinality of single-field indexes is, to say the least, low. Maybe you can describe the data a bit more and we can discuss other designs? Are all these independent of each other, for instance?

With only 1000 rows, it might be simpler to store this elsewhere than the database (although I imagine there are lots of join opportunities?) Not for query efficiency reasons, but it doesn't really look like database data.

Answer 4

Model your data most appropriate for your problem domain. You don't have much data here, in a worst case scenario, assuming each row takes up 200 bytes, you're looking at less than 200 Kb of data. It's a trivial amount even if your particular database doesn't implement Boolean properties in an efficient way.

On the other hand, having 150 boolean properties sounds somewhat suspicious, perhaps your data model can be further normalized?