0

I have an User model with statuses that represent yes/no flags for the following states

  • active
  • email verified
  • phone verified
  • KYC passed
  • MFA set up
  • may purchase
  • account suspended
  • account closed

Most developers will store this state into separate database fields. I'd like to store this in a single 8bit integer so I can do things like:

status & 0b10000000 == 128 # email verified
status & 0b01000000 == 64  # phone verified
status & 0b00100000 == 32  # KYC passed
status & 0b00010000 == 16  # MFA set up
status & 0b00001000 == 8   # may transact
status & 0b00000100 == 4   # active
status & 0b00000010 == 2   # suspended
status & 0b00000001 == 1   # closed

The advantages I can think of would be:

  • less DB clutter (1 field instead of 8+)
  • easier querying (just a comparison between 2 ints rather than a .where longer than my keyboard cable)
  • single index for all this and it will be fast
  • can add more information as a 2nd byte (and so on) if needed

Before actually implementing this I'd just like to hear from more experienced developers if this is a good idea (yeah let's have a laugh: rate it 0 to 10) or a big no-no and in that case why not rather than plain simple "no" or vote to close/flag simply because it doesn't fit opinionated simple active/inactive state scenarios.

Kimmo Lehto
  • 5,910
  • 1
  • 23
  • 32
Nick M
  • 2,424
  • 5
  • 34
  • 57
  • I don't see why you would need to query any of these, except maybe `active` in some cases (`user_id = X AND active = 1 AND pass_digest = 'XyZzZ'` in which case you could not display something like `your account has been suspended`). I don't know if the databases can do "bitwise indexing". A regular index for this is worthless. It also makes the data worthless to anyone without knowledge of your code. – Kimmo Lehto Apr 15 '19 at 10:54
  • Also, this has very little to do with Ruby and should instead be tagged with something like `sql` or `database`. – Kimmo Lehto Apr 15 '19 at 11:00
  • The use cases for this may be a bit beyond typical active/inactive states and might have some interesting implications on how features such as reports are implemented on both application and DB layers. Databases don't need to do bitwise indexing since the field would be an integer. Ruby is my chosen language and gave the code samples in ruby but I see how this may fit under SQL/DB as well. Will leave it as it is for now, perhaps someone else with a non-trivial business logic may consider this strategy useful and/or comment. – Nick M Apr 15 '19 at 11:03
  • 1
    Ok, what kind of `WHERE` clause would you use to find users that are 1: active 2: have not verified their phone number 3: but may have verified their email 4: without including users that have been suspeded or closed? To utilize the index i think it has to run AND through the whole index for every row, it can't do something like `(status >= 4 && status <= 63) || (status >= 132 && status <= 191) ...` because it will still then include the closed / suspended accounts. – Kimmo Lehto Apr 15 '19 at 13:10
  • .where(:status => [68,196] ) # ( # 0100 0100 active with phone verified # 1100 0100 active with phone AND email verified). Will hit index balls deep altho not so dev-friendly but can always add more shortcuts – Nick M Apr 15 '19 at 13:27
  • .where(:status => [132,196]) # active with email verified => 132, active with phone and email verified => 196 if I understand your req correctly. Just flip bits => statuses. It will always hit the index, no scans. Definitely need a DSL of some sort. – Nick M Apr 15 '19 at 13:37
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191852/discussion-between-nick-m-and-kimmo-lehto). – Nick M Apr 15 '19 at 13:40
  • 1
    Necessary concepts are not "clutter". Adding a concept & mappings is "obfuscation" unless you justify that the new structure saves complexity in queries. You don't seem to realize that you can update multiple columns of a row at a time. For fifo a stack beats an array. Benefits here are not demonstrated. And you do not show indexing or speed is improved. "Premature optimization is the root of all evil."--Knuth – philipxy Apr 15 '19 at 20:57
  • 1
    This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Apr 15 '19 at 21:00
  • 2
    Possible duplicate of [When is it better to store flags as a bitmask rather than using an associative table?](https://stackoverflow.com/questions/5708239/when-is-it-better-to-store-flags-as-a-bitmask-rather-than-using-an-associative-t) – philipxy Apr 15 '19 at 21:05
  • Active with phone verified won't be `68,196` if MFA is set up or "may transact" is 1. You also need to update closed and suspended to 0 when active goes to 1 etc. Easier to have a dedicated field for that, something like `state = 'active/closed/suspended/pending/etc'` – Kimmo Lehto Apr 16 '19 at 07:46
  • Of course you can do `WHERE (status & 0b01000000 + status & 0b10000000 + status & 0b00000100) IN (68,132,196)` but that will run a bitwise `AND` on the whole table 3 times. The linked duplicate question contains the same answers. – Kimmo Lehto Apr 16 '19 at 08:04

0 Answers0