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.
boolean
,integer
, andvarbit
for the purpose: stackoverflow.com/a/9993552