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.

Answer 1

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.

Answer 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.

Answer 3

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

Answer 4

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

Answer 5

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