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
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