1

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.

  • 1
    Maybe this is a stupid question but just to make it clear, how much data do you expect to store this way? I mean, is form_field_values's intended purpose just to support current input sessions or do you mean to persist all input in that table? – Andriy M Sep 26 '16 at 9:20
1

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.

1

Think about how the column will be used. Then be prepared to change the spelling if needed ("0" vs 0 vs "false" vs "FALSE" vs ...). MySQL's boolean is rather flexible and will probably do the 'right thing' with any of those. But, when selecting, will you get a user-friendly value?

Does MySQL need to "test" the value? If not, then the field is simply a repository for whatever the UI needs, and a VARCHAR may be the best datatype.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.