DB Design for storing dynamic form data with multi select fields

Question

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

What purpose are you storing it for? Is it to upload to another system or report on it? If it's to report on, what kind of reports are they? Do you need a report counting all the people who picked green? Or is it just a hard copy dump of what people picked? Or do you have logic that goes over answers and assigns scores? — Nick.Mc, Jul 06 '17 at 05:44
You haven't described any constraints that would help us decide whether you can just stuff the JSON in as-is, or whether you should build a bridge table defining a many to many relationship. The "proper" normalised way is to build a bridge table, but we don't know your constraints. — Nick.Mc, Jul 06 '17 at 05:46
@Nick.McDermaid we need to download/ interface the raw data to another system. Also we would like to have reports, like count of colors picked etc. we are using ms sql server 2014 . / Spring MVC . I dont want to store in JSON. I prefer to store in tables for complex queries. So I am looking for a best DB design to support this — Mukun, Jul 06 '17 at 06:01
So what does `form_submit` represent? It doesn't look like it supports anyone entering multiple values in a form. i.e. if one person enters one set of values and another person enters another set, it doesn't look like this is supported by this table. You need anothe rcolumn, something like `submit_id` that represents each persons submissions. have a think about that, then me or someone else can pose an answer — Nick.Mc, Jul 06 '17 at 06:59
Yes form_submit is to store the data submitted and it currently does not support multi selected inputs. It will work, if we have only a list of key,value. so I need help in designing a DB tables to support multiple selected values(array of selected colors) — Mukun, Jul 06 '17 at 07:17
I know it doesn't support multi select. It also does not support any more than 1 form entry. When the next person fills in the form does it just overwrite what's there? Or do you only ever expect the form to be filled in once? — Nick.Mc, Jul 06 '17 at 07:24
It does support more than 1 form entry, I have updated the table. For sake of simplicity, I have ignored fields,like submitted date, user etc — Mukun, Jul 06 '17 at 07:36
Ok well I'm going to assume this table has a column called submit_id.which indicates a group of questions and answers in a single submission. — Nick.Mc, Jul 06 '17 at 07:42

score 1 · Accepted Answer · answered Jul 06 '17 at 10:34

Firstly... I must warn that dynamic schemas like this are usually a bad idea.

Secondly it appears that your form_submit can store only one set of answers

I'm making assumptions here - its not clear if you need to support multiple sets or not, but it would make sense if it did.

So firstly let us extend form_submit to support multiple sets of questions from different people. We'll add a column submit_id, which is the instance of a questionaire (set of questions) that someone is answering.

submit_id   form_id   Column_id   value
    1           1        1        Block
    1           1        2        Rough
    1           1        3        Red
    2           1        1        Cylinder
    2           1        2        Smooth
    2           1        3        Blue

Now we know that submit_id=1 is one set of questions answered by one person and submit_id=2 is another different set of questions answered by another

You might want to create a submit header that describes this:

submit_id      form_id    submit_datetime          submit_by
    1             1        2017-07-06 09:37:00       Fred
    2             1        2017-07-02 07:31:00       Fred

Now we can create a table, say multiselect that lets us define the relationship between many possible questions and many possible multiselects

submit_id   column_id   list_value_id
     1          3            1   (red)
     1          3            2   (blue)
     1          3            3   (green)
     2          3            4   (dell)

This set of rows tells us that questionaire 1 had a multiselect on column 3 and they picked red, blue green.

Questionaire 2 had a multiselect on column 3 and they just picked Dell

You don't even need a row in your form_submit table for this. It depends what else your form_submit stores.

That's just one way to do it. But it's really dictated by your business processes, relationships between entities, how you want to get data in and out etc. You might want to research questionairre data models online because this is nothing new.

I suspect this may just prompt more questions but lets try this first

I also thought similar for DB Design One Table (form_submit) to store key value fields and another (multiselect) to store multi select fields. But I am trying to visualize the Java Entities . and how I would persist them and retrieve — Mukun, Jul 06 '17 at 18:32

score 1 · Answer 2 · answered Aug 06 '19 at 15:55

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"]}'      |

I think this non-atom solution cannot be used for sorting in the admin panel. — Cody Ng, Sep 27 '22 at 21:13

DB Design for storing dynamic form data with multi select fields

2 Answers2