What is an efficient way to store a bunch of booleans?

ten9

Hi everyone!
I have a form on the front end that push some data and a bunch of booleans in it. The data structure looks like this: base data (user, role, date, etc.) and around 70 booleans from checkboxes.

What is the best way to store data like this?
I need to be able to query data and make aggregate functions by these checkboxes. That's why storing booleans as raw JSON in the table is not an option.

I was thinking to create two tables, one will store all base data, and the second store only booleans. When I need to query or aggregate they simply connect with a joint. I hope to avoid a mess in such a way and if the checkbox amount will expand easy adapt to changes.

But I'm not sure of the best way to do this and would appreciate any advice.

arturosanz

There is a better way to handle a large number of booleans, and it was widely used in the past century when computers had little power and memory. However, the level of efficiency obtained depends on how Xano handles internally the integer and boolean field types. More precisely, how many bytes needs to store each field type.

Booleans are two values fields, true or false. But also could be represented by 0 and 1 values. Now you have to switch our common way of treating numbers in base 10 to base 2 (binary). For instance the integer 1234567890 in base 10 is equivalent to 1001001100101100000001011010010 in base 2.

As you can see, a simple integer field can store many "booleans" in sequence, which in theory should save a lot of storage space. I didn't see the limit Xano's integer field type in the documentation, but probably uses 4 bytes (32 bits) to store an integer. The bit position 32 is usually reserved for the integer sign, if it is 0 it means it's a positive number and if it is 1 it means it's a negative number. So with just 3 integer fields you could handle almost 100 booleans if you know how to do math in base 2 (binary).

Xano has built-in bitwise math operations which allow you to easily compare integers represented in base 2 (binary), but often you don't even need to use these bitwise operations if you know the conversion rules between base 10 and base 2 numbers. You can actually perform aggregations operating with base 10 numbers and extrapolate their meaning in base 2 (binary) if you know the rules.

It's not intuitive, but it's feasible. You have to decide wether it's worthwhile or not to do the "math effort" depending on how many records with a large number of booleans you expect to handle.