What is the best way to insert many checkbox inputs into database?

Question

Greetings,

I have a form which it has almost 150 checkbox, but they are not in one place, the form is divided into divisions and each division has blocks, the checkboxes are put in these blocks depending on their group where you can find four checkbox in this place and eight in other and 30 in some other and so go on.
So I made a two table’s one to store the groups and other to store checkboxes and to which group they belong to.

FormBlocks
    --------------------------
    |  ID  |       key       |
    --------------------------
    |   1  |  property_type  |
    |   2  |    providors    |
    --------------------------

BlocksCheckBoxes
    ------------------------------------------------
    |  ID  |     Label    |   Key  |  FormBlockId  |
    ------------------------------------------------
    |   1  |  Sweet Home  |  home  |       1       |
    |   2  |     Farm     |  farm  |       1       |
    |   3  |  Invst Bank  |  bank  |       2       |
    |   4  |  Constructor | constr |       2       |
    ------------------------------------------------

I have two approaches to store their values:
The first one store the checkbox values as concatenated string of their keys and insert it directly to the designated table, eg:

UserProperties
    ----------------------------------------
    |  ID  |  Customer  |  PropertiesType  | 
    ----------------------------------------
    |   1  |    Cat     |     home,farm    |
    ----------------------------------------

pros:Very easy to implement and all I need is just to add a column and you are ready to go, and theoretically it is faster
cons: Will lose db level of data integrity and it will depend on the application level (I think I can live that)

The second way I create tables for each block, eg:

UserProperties
    ---------------------
    |  ID  |  Customer  |
    ---------------------
    |   1  |    Cat     |
    ---------------------

UserPropertiesTypes
    --------------------------------------
    |  ID  |  CustomerId  |  CheckBoxId  | 
    --------------------------------------
    |   1  |       1      |       1      |
    --------------------------------------
    |   1  |       1      |       2      |
    --------------------------------------

pros: I see it as the professional way and will insure the data integrity at db level
cons: I need to create table for each part that has checkbox group which is a headache in development specially if you have many groups in the form.

I didn't consider bitwise because I will have to create a column for each checkbox and I see it is not efficient for big groups who will have more than 30 checkbox. so I preferred to make a way that will be implemented in the application

If you have any suggestions or better way to do it please don’t hesitate to tell. Thank you very much

Answer 1

Judging by

BlocksCheckBoxes
------------------------------------------------
|  ID  |     Label    |   Key  |  FormBlockId  |
------------------------------------------------
|   1  |  Sweet Home  |  home  |       1       |
|   2  |     Farm     |  farm  |       1       |
|   3  |  Invst Bank  |  bank  |       2       |
|   4  |  Constructor | constr |       2       |
------------------------------------------------

It would suggest only one option can be picked, so this block of checkboxes could be expressed as a single enumerator value (1 for 'Sweet Home', 2 for 'Farm', etc).

Then the multiple choices checkboxes can be done using bitmasks (you do not have to use the inbuilt database ones if there are too many values).

If you pick a large enough enumerator/bitmask value (an unsigned 32-bit int will do 4 million+ enumerators and 32 bitmasks). Each 'Division' for each filled in 'Form' can then be a separate row - but with the same value column for all divisions.

Surveys
------------------------------------------
|  ID  | User | Division |  Value        |
------------------------------------------
|   1  | 1    | 1        |       7       |
|   2  | 1    | 2        |    234234     |
|   3  | 2    | 1        |       7       |
|   4  | 2    | 2        |  23491032     |
------------------------------------------

So in this example User is for each run of the form being filled in, Division is for each division/group/block and Value will either be the enumerator for the mutually exclusive value selected or a bitswap for multiple choice.

With some clever OOP wrapping you can make this work to your advantage with a nice API interface and a single 4 column table representing all your data. If you prefer the ID column can be dropped and a composite key of User and Division used instead.