DB Design for storing dynamic form data with multi select fields

user_1057094 0
Viewed 5764

How to store data from dynamic form with multi select fields.

I have read various post and all example has only list key,value input. But if the form has multi select input. what is the best way to store the data. I am not looking for NoSQL solution.

current design

 forms
-----
  id (PK)
  name     
  (other fields)

form_elements
-------------
  id (PK)
  form_id (FK to forms.id)
  element_type_id (FK to element_types.id)
  name
  list_group (nullable, it will be related only for multiselect inputs)      
  (other fields)

element_types
-------------
  id (PK)
  name

list_values
-------------------
  id (PK)      
  value
  group
  (other fields??)

Form Table sample data

| form_id |    form_name    |
|:-------:|:---------------:|
|    1    |   Enquiry Form  |
|    2    | Test Drive Form |
|    3    |  Feedback Form  |

Form_elements sample

| id | form_id | element_type_id |    name   | list_group |
|:--:|:-------:|:---------------:|:---------:|:-------:|
|  1 |    1    |        1        | firstName |         |
|  2 |    1    |        1        |  LastName |         |
|  3 |    1    |        2        |   color   |    color|

element_types 

| id    |   name    |
|:--:   |:--------: |
|  1    |   text    |
|  2    | checkbox  |
|  3    |   radio   |

list_values sample data

| id    | value     | group     |
|:--:   |:-----:    |-------    |
|  1    |  red      | color     |
|  2    |  blue     | color     |
|  3    | green     | color     |
| 4     | Dell      | brand     |
| 5     | HP        | brand     |

sample json posted

{
  "firstName": "john",
  "lastName": "Doe",
  "color": "red"
}

form_submit table will have the following rows

| form_id   | Column_id     | value     |
|:-------:  |:---------:    |:-----:    |
|    1      |     1         |  John     |
|    1      |     2         |  Doe      |
|    1      |     3         |  Red      |
|    1      |     1         | James     |
|    1      |     2         | Smith     |
|    1      |     3         |  Blue     |

if the dynamic form has multi select option sample json posted will be 

{
  "firstName": "John",
  "lastName": "Doe",
  "color": [
    "red",
    "green",
    "blue"
  ]
}

how do you store this data.
Do we need to store it in same form_submit table. or store in different table

dynamicform forms database-design json database
2 Answers

Using JsonString to save your submitted forms can be a solution:

form_submit
-------------
  id (PK)
  form_id
  user_id
  value_json
  (other_fields)

wherein value_json --> {"form_element_id as the key" : "submitted value"}

lets says in a certain form, we have a name field(field Id 23) and a multiselect(field id 24) with ["a", "b", "c"] as options.

So sample data for form_submit Table can be 

| form_id | u_id |                      value_json                       |    
|:-------:|:----:|:-----------------------------------------------------:|
|    1    | 049  | '{"23": "Some random question", "24": ["a", "b"]}'    |
|    1    | 033  | '{"23": "Another random question", "24": ["a"]}'      |
user_105339920
Related