Chazy Chaz Chazy Chaz - 4 months ago 6x
MySQL Question

DB structure - form with dynamic number of options

I've been reading similar questions, but I think my case is a bit more complicated.

I have a form that register items. These items may have options with sub-options (checkboxes and radio buttons):

  • The number of checkboxes and radio buttons may decrease/increase but the real pain to design a good structure is for the checkboxes, as these must have (at least I think so) a fixed name column for each one.

  • The case for radio buttons is easier as I just assign an id to each one (and save the names in a different table).

My current DB structure is simple (between parenthesis is the table/column name):

  • The items table (item) have columns of type integer (to save the id of the radio buttons).

  • Another table for the checkboxes (item_option), with columns of type integer (1 if checked, 0 if unchecked). And 1 PK column (item_id) that points to the PK column (id) of the items table.

  • And tables (again item_option) for the names of the radio buttons with a PK column (id) that points to the option column (is this understandable? Sorry for my bad english).

I think a different table containing the sub-options is better than put all the columns in the main table, right?

So, the radio buttons are stored in the main table (1 column per option) and the checkboxes in a separeted table (1 table per option):

Items table:

| id | Option_1 | Option_2 |
| 123 | 3 | 1 |
| 456 | 2 | 3 |
| 789 | 1 | 2 |

item_option_3 table (this would be needed to know which ones are checked):

| Sub_Option_1 | Sub_Option_2 | Sub_Option_3 | item_id |
| 1 | 0 | 1 | 123 |
| 1 | 1 | 0 | 456 |
| 0 | 1 | 1 | 789 |

item_option_1-2 table (this would be used to print the names):

| option_id | name | name_es |
| 1 | Sub_Option_1 | Sub_Opción_1 |
| 2 | Sub_Option_2 | Sub_Opción_2 |
| 3 | Sub_Option_3 | Sub_Opción_3 |

What kind of structure do I need to spawn these sub options (checkboxes) dynamically?


What about something like this?

Your model has option keys as columns and values as rows. Why have both keys and values be rows? If you don't need complex type-based validation, it should suffice to have a single options table with a one to optionally many relationship to itself to account for suboptions. To enumerate all options and values, just retrieve all rows from the table. If ParentOptionId is null, then it is a base-level option; otherwise it is a suboption.

UML & ER version below.

enter image description here

EDIT: After reading through your question and comments again, I've come up with a more complicated but more robust design for you to consider:

It works like this:

  • Every user input is an Option. Every option consists of a display text (OptionText), tooltip/subtext/etc (Description), a default and then user supplied value (Value), a value type (ValueType boolean,text, date, etc). It also has a DisplayOrder so you know where to situate it in relation to other Options in its group. Options can also have a parent/child relationship with other Options. You can do the same for the other entities if you want but I did not model that.
  • Every Option is contained within an OptionGroup with 0 or more sibling Options. OptionGroups are just a collection of one or more related Options. The GroupType field dictates how your form builder needs to treat that group. The most obvious example would be for your radio button groups; each of those would be an OptionGroup and each radio button would be a boolean Option within the OptionGroup. An OptionGroup could just as easily handle a multiple selection checkbox group or just some related text inputs that need a common header text (like a street address).
  • For further dynamic design OptionGroups are contained within GroupSections, even if there is just one default GroupSection in a form.
  • Finally, a Form models your final actual UI form and consists of one or more GroupSections.

This should be flexible enough for you to tweak to your liking. What do you think?

Final note: if you are looking into dynamically building your forms in Javascript, check out a few frameworks like X-editable or formly. They take JSON or configuration objects and build out the entire form with validation/etc from there while giving you some hooks for event handling. Chances are you don't need to completely reinvent the wheel unless you want to keep your implementation as simple and specific as possible.