19

I have a bunch of booleans I need to associate with user accounts. They need to be toggled true and false, though not particularly frequently - they're going to be read more often than they're written. Most commonly they'll just be set from False to True once, but that won't always be the case.

Right now I'm thinking of appending columns to the user table like this:

UserID other_user_cols did_a did_b did_c
1 ... False True False
2 ... False False False
3 ... True False False

The thing is, there's quite a lot of these boolean columns.

Let's say you needed to add like, 100 columns. Is this really still the best way to do it?

Assume:

  • It's not going to be very sparse - most users will toggle most booleans at some point
  • The booleans aren't logically related in a way that could naturally be better organized
  • New ones may need to be added from time to time
  • the flags are directly associated with each user and no other info is needed other than the single boolean value

I feel this must be a pretty common pattern, and yet this way of just jamming more boolean columns into my table seems rather messy. Is there a more typical way of doing it that's a bit cleaner? Or is this actually the best way?

I'm using Postgres for this.

Here are some alternatives I've considered:

  • serialize into a json list of strings (= the column names). This sounds even messier than the original and also not ideal if I ever want to do any serious SQL operations on the data
  • Use numbers and bitwise operations - not sure how well this is supported and similar concerns about ever needing to do operations/lookups on individual flags
  • Have a separate table like:
UserID flag_name value
1 did_a False
1 did_b False
3 did_a True

That seems most reasonable to me as an alternative but not sure if it's really worth the trouble or if there's a better way.

3
  • How many rows (users) do you expect? – Colin 't Hart Jun 11 at 7:51
  • 2
    What is the expected read pattern for this data? Is it "check a single "flag" for a single user"? Or "check all users where flag x = ..."? What is meaning of those flags for/to the application/end-user? What structure does the data have in the application that sets the flags? Are the flags manipulated individually or as a whole per user? – Lars Br. Jun 12 at 3:45
  • 1
    Closely related answer discussing boolean, integer, and varbit for the purpose: stackoverflow.com/a/9993552 – Erwin Brandstetter Jun 12 at 14:05
28

I can't see any use case where there are 100 boolean variables that are directly related to the user, and thus belongs to the user table. My guess is that these booleans are for training, certifications or similar. I'll assume this for the rest of my answer.

A separate table "user_training" is the way to do this. In this table you have the "user_id" and "training_id". You don't need a boolean, if the row is in the database the training is completed.

New ones may need to be added from time to time

Using this architecture no new columns need to be added to the database. Only a new row. Or, if the database contains a table for the training, the training is added to that table and the "training_id" refers that.

The booleans aren't logically related in a way that could naturally be better organized

If there are several contexts for the booleans several tables can be used. Think "user_training", "user_certification", "user_something", "user_whatever" etc. It will take a little longer to do, but not that much time. And your future self will thank you for it, since it will be a mess fixing unrelated data when it's needed.

If the contexts really are separate you can do it with only one table, like "user_action" or "user_properties".

Please note that there are still a few things you need to decide. Should you have a "training" table with info about the training name? If so you can add additional trainings without having to change the application or database structure. Do you want to have true as default for some "training_id"'s? If so, how do you know what is the default for each of the trainings? Should the default value be in the "training" table?

0
14

I recommend using varbit, mostly because it is space efficient. Sure, storage space is cheap, but you have to cache in RAM, which is not so cheap, and manipulating smaller rows performs much better.

It is also easy to manipulate:

CREATE TABLE mytable (id bigint PRIMARY KEY, flags varbit);

INSERT INTO mytable VALUES (1, b'11010001000110100');

To get the value of the tenth flag:

SELECT get_bit(flags, 9) FROM mytable WHERE id = 1;

 get_bit 
═════════
       0
(1 row)

To change the tenth flag:

UPDATE mytable SET flags = set_bit(flags, 9, 1) WHERE id = 1;

SELECT * FROM mytable WHERE id = 1;

 id │       flags       
════╪═══════════════════
  1 │ 11010001010110100
(1 row)

To add a new bit at the end:

UPDATE mytable SET flags = flags || b'1' WHERE id = 1;
2
  • 26
    I wouldn't do it this way. It looks elegant, but it's a horrible pattern to actually use. You need to reference a data dictionary to determine which bit is which, it's nearly impossible to tell at a glance what is going on with the data, it usually can't be meaningfully indexed, adding columns is difficult, removing columns is very difficult, bitmask operations are easy to get the logic backwards (flags & '1010' = flags vs flags & '1010' = '1010'), etc. – Bacon Bits Jun 11 at 15:10
  • @BaconBits Sure it is ugly, but not much uglier than an array and a JSON. But I can accept that opinions differ on that. – Laurenz Albe Jun 11 at 20:08
3

There are four mode: column, composite_type, and array, json

Array is my favorite mode, Because,

  1. there is no column quantity limitation
  2. plenty of agg functions
  3. easy operate compare to json

Here is example of column, composite and array;

drop table if exists  t_manycolumns ;
drop table if exists t_composite ;
drop table if exists t_arry ;
drop type if exists type_bits;
create table t_manycolumns
(id int,
is_1 boolean,
is_2 boolean,
is_3 boolean,
is_4 boolean,
is_5 boolean,
is_6 boolean,
is_7 boolean,
is_8 boolean
);
create type type_bits as (
is_1 boolean,
is_2 boolean,
is_3 boolean,
is_4 boolean,
is_5 boolean,
is_6 boolean,
is_7 boolean,
is_8 boolean);

create table t_composite( 
id int,
is_bits type_bits
);

create table t_arry(
id int,
is_bits boolean[]
);


-- column
insert into t_manycolumns (id, is_1, is_2, is_3, is_4, is_5, is_6, is_7, is_8)
select id, false, false, false, false, false, false, false, false from generate_series(1,100) id;
--composite type
insert into t_composite (id,is_bits)
select id, '(false, false, false, false, false, false, false, false)' from generate_series(1,100) id;

--array type
insert into t_arry (id,is_bits)
select id, array[false, false, false, false, false, false, false, false] from generate_series(1,100) id;


-- demo show search bit true in array

update  t_arry 
set is_bits[1]=true, is_bits[4]=true 
where id in (1,3);

select id, is_bits 
from t_arry
where is_bits[1]=true and is_bits[4] =true
order by id

The two importants things are :

  1. array type can by pass the column quantity limitation.
  2. the performance need be further study. Array performance should be high but it is bit of like you are programming instead of run sql on relation database.

Hope you like it.

0
0

Despite this question being well answered, just wanted to give my two cents that I'm personally a fan of the third implementation you mentioned, and have used it successfully for similar reasons before, since no one else has spoken up on it yet.

Relational Database Management Systems, especially ones naturally of the rowstore kind, can work well following this pattern when having to support a large number or highly variable columns, as long as you structure your consuming code appropriately. This generic type of table allows for much flexibility, and can even be indexed easily, as needed.

4
  • 2
    I like this approach as well. It works well enough until you have to filter on multiple flags at once, which is rather cumbersome. Might not be an issue in the OP's specific scenario (one flag per user, as I understood) but is still worth mentioning as a serious enough drawback in general, to give the reader a balanced view on this approach. – Andriy M Jun 12 at 7:01
  • Agreed that of the three that is the most "normal" form for an RDBMS. The one issue with it as written is that is relies on a flag column that is actually three states - Truthy, Falsy and non-existent row. @Polygorial's answer addresses this and to the point of the OP's question is very much a common (perhaps even canonical) pattern for this data. – 640KB Jun 13 at 16:17
  • @640KB Could you elaborate on how that three states is an issue though? (I'm sure in some use cases it can be, but don't have any off the top of my head since in my prior use cases it wasn't.) – J.D. Jun 13 at 17:00
  • For one, there are logically only two states (boolean), so design-wise that's already a mismatch. Second the application that's utilizing the data would need to be built to handle two cases for the same value (if Falsy or if null). Third if you wanted to toggle the flag you'd have to do multiple operations (1: check if row exists, 2: if not, do an insert, otherwise do an update). The alternative to that would be to need to always insert 100 or so default rows when a user is created and insert a corresponding row to each user when a new flag is added to ensure there's always a value. – 640KB Jun 13 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.