Drawbacks of using an integer as a bitfield?

Question

I have a bunch of boolean options for things like "acceptable payment types" which can include things like cash, credit card, cheque, paypal, etc. Rather than having a half dozen booleans in my DB, I can just use an integer and assign each payment method an integer, like so

PAYMENT_METHODS = (
    (1<<0, 'Cash'),
    (1<<1, 'Credit Card'),
    (1<<2, 'Cheque'),
    (1<<3, 'Other'),
)

and then query the specific bit in python to retrieve the flag. I know this means the database can't index by specific flags, but are there any other drawbacks?

Why I'm doing this: I have about 15 booleans already, split into 3 different logical "sets". That's already a lot of fields, and using 3 many-to-many tables to save a bunch of data that will rarely change seems inefficient. Using integers allows me to add up to 32 flags to each field without having to modify the DB at all.

score 6 · Answer 1 · answered Jun 16 '10 at 23:17

6

The main drawback that I can think of is maintainability. Someone writing a query against the database has to understand the bit convention rather than being able to go after a more human readable set of columns. Also, if one of the "accepted payment types" is removed, the data itself has to be migrated rather than just dropping the a column in the table.

answered Jun 16 '10 at 23:17

btreat

1,554
8
10

Why would the data have to be migrated? You'd just have an unused bit that you wouldn't access anymore. – mpen Jul 10 '10 at 02:15
1

The motiviation for removing the unused field (whether it's represented as a bit or a db column) is to simplify the application and thus improve maintainability. However, you're quite right. The application would continue to function correctly even if the bit were not removed. – btreat Jul 10 '10 at 21:41

score 2 · Answer 2 · answered Jun 16 '10 at 23:19

2

This isn't the worst, but there might be a better way.

Define table called PaymentTypes

id, paymentId, key (string), value (boolean)

Now you just populate this table with whatever you want. No long column of booleans, and you can dynamically add new types. The drawback to this is that default of all booleans is NULL or false.

answered Jun 16 '10 at 23:19

Byron Whitlock

52,691
28
123
168

You mean add a many-to-many table for payment types? I thought about that... but it's an extra layer that doesn't really seem necessary. – mpen Jun 16 '10 at 23:22
@Mark, One to Many actually, but it is a bit of a hassle. You could also have a a lot of boolean columns in a table with a foreign key to Payments. – Byron Whitlock Jun 18 '10 at 20:47
1

@Bryon: Why would it be one to many? Each shipment can accept multiple payment types, and each payment type can belong to many shipments = m2m. Hence the bitfield rather than a simple enum. – mpen Jun 18 '10 at 23:33

score 2 · Answer 3 · answered Jun 18 '10 at 19:35

2

Not sure what database you're using, but MySQL has a set type.

answered Jun 18 '10 at 19:35

Justin K

2,664
1
19
16

1

Didn't know this type existed. Cool! I don't think Django supports it though. – mpen Jun 18 '10 at 23:34

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

If you could limit your use case to one or more sets of values that can only have one bit true at a time, perhaps you could use enums in your database. You would get the best of both worlds, maintainable like btreat notes, and still smaller (and simpler) than several booleans.

Since that's not possible, I'd agree with your initial assment and go with a bitfield. I would use/create a bitfield wrapper however, so that in your code you don't deal with flipping and shifting bits directly - that becomes difficult to maintain and debug, as btreat says - but instead deal with it like a list or dictionary and convert to/from a bitfield when needed.

Some commentary on enums/bitfields in Django

edited May 23 '17 at 11:43

Community

1
1

answered Jun 16 '10 at 23:51

dimo414

47,227
18
148
244

(1) How could I use an enum? An enum stores *one* of several options (think radio buttons), not several flags (think checkboxes). (2) Django tag == not much control over what data type is used. – mpen Jun 17 '10 at 02:09
Apologies, I didn't realize you needed that extra control. Your example (payment type) is a radio-button-type usage, and I jumped on that. If you need multiple flags true at a time, enums will indeed not work. I have re-written my answer. – dimo414 Jun 18 '10 at 19:16
Yeah...I guess it was a bad example to use. In my case, users *can* check off more than one payment type though. I'm not sure a bitfield wrapper is really necessary though. I find it pretty easy to work with bits if you just do a bit of shifting. What would be nice though, is if I could give names to the different flags rather than having to memorize integers. – mpen Jul 10 '10 at 02:31
I'm giving you the check because you're the only one that said "go with the bitfield" which is what I ultimately decided on... although I still think it's a toss-up between bitfields and separate boolean columns. Actually... booleans would probably make everyone's lives easier and be easier to maintain. But oh well. I don't care anymore :'( – mpen Jul 12 '10 at 00:00

score 1 · Answer 5 · answered Jun 17 '10 at 01:34

I think the previous posters were both correct. The cleanest way to do it in a "relational" database would be to define a new relation table that stores payment types. In practice though, this is usually more hassle than it's worth.

Using enums in your code and using something similar in the DB (check constraints in Oracle, AFAIK) should help keep it maintainable, and obvious to the poor soul who's job it will be to add a new type, many many years after you've left

Drawbacks of using an integer as a bitfield?

5 Answers5