Bit map of yes/no answers: Should I store it as binary, blob, varchar, or int?

Question

I am developing a web app in Laravel where I have to ask users a set of Yes/No questions and save the answers in the database. I thought that I should try and save all answers in binary rather than saving them one-by-one since the number of questions vary from time to time. Say in three questions, the user answered Y-Y-N; that would convert into "110".

Now, on the database side, should I:

• save the data as is (varchar)
• use binary/blob
• convert to int and save sa int

Not sure which option is the best, considering that the system sometimes output 50 questions.

MySQL -V info:
mysql Ver 14.14 Distrib 5.5.62, for debian-linux-gnu (x86_64) using readline 6.3

Answer 1

One bit per answer

If you are willing to limit the number of questions to 64, then BIGINT UNSIGNED will work somewhat easily, but limited to 64 true/false values. Let's say q is the column being used.

q & 1<<0    -- the bit for question 1 (assuming question numbers start at 1)
q & 1<<22   -- the bit for question 23
(q & 1<<22)  = 0   "false" for q23
(q & 1<<22) != 0   "true"  for q23
(q & 0x7FF)  = 0x7ff   "true" for all of q 1-11

Etc.

The SET datatype is implemented similarly but is clumsier to use for your case since it wants you to name each bit.

If you need more than 64, then BINARY and BLOB would let you have up to 4 billion bits (in LONGBLOB), but it is much messier to extract a single bit from them.

INT UNSIGNED has only 32 bits (4 bytes), thereby not satisfying your "50".

One byte per answer

It is probably not unreasonable to have the answers in rows -- a table with (student, question_number, answer). In this case answer could be any of these. Each takes 1 byte and is about as easy to work on as any other.

CHAR(1) CHARACTER SET ASCII NOT NULL.  -- and insert "T"/"F" (or whatever)
TINYINT UNSIGNED NOT NULL   -- 0=false, 1=true
ENUM("F", "T") NOT NULL