I have a form_fields table that represent different HTML form fields (e.g. text, textarea, checkbox, etc.) that a user needs to populate. It also contains meta data about the fields, like whether or not it is required, placeholder text, label, etc.
I also have a table that maps to the columns of form_fields to capture the data that the users inputted, which looks like this:
===================================
form_field_values
===================================
id -> auto increment integer
-----------------------------------
form_fields.id -> FK to form_fields
-----------------------------------
value -> string, user supplied data
-----------------------------------
The problem is the value column. Most of the data I'm storing there is just a string (in the case of text fields, textareas — which make up about 90% of what I'm trying to capture). In some cases, I need to store a bool value there, like whether or not a checkbox is checked.
Should I store the string "TRUE" or "FALSE" (or "1" or "0") in the value field? Then somehow cast it to a bool when retrieving the data?
Or did I design this form_field_values table wrong? Maybe I need a table that have columns matching form_fields columns, and store the data that way. Problem with this is that I would not be able to associate these columns with the columns in form_fields.
Best Answer
Use tinyint for these and use boolean in your ORM. SO it will automatically mapped from boolean to 0 and 1.
Its not good way to store boolean as string and then checking the value and assigning bool value on basis of string match.
For boolean just 0 and 1 value in your column as tinyint will solve your problem. For other column which arestring and if cardinality of the values in that column is very small as compared to overall data size then you can either use ENUM datatype or normalize table by joining it with another table containing those values.