Guru Muhendis Guru Muhendis - 1 year ago 48
MySQL Question

How to store different kind and number of html form data in database?

First of all thank you for viewing my question.

I got myself into trouble about 1 problem that i have encountered recently. I am trying to write a survey system with the ability to generate different type of questions with the help of a drag & drop html form builder. A client will generate its survey form and the html body of that form will be stored in a database table. If that client wants to show that form somewhere he will just call a service from my system and i will provide him that form. On the other hand i also want to store the results of that form in a table. But as i mentioned before that generated forms fields and database tables fields may not be match. I have to overcome this problem with the help of you guys.

The dummiest (well, not so practical and it makes me feel dumb) solution i come up with is generating matching table for each form.I think it will also be costly because in this case i have to have 1 more table for matching form table (which stores html form of form data) and auto generated table (which stores data that comes from submitting that html form). So, what kind of design should i implement, what do you suggest?

Answer Source

If the types of questions are known and limited (single-choice, multiple-choice, free-text) then I would suggest you to create additional table and update it when creates a new form.

- id
- question_type
- question_text
- form_id -- reference to forms table

Then you can store the results in the second table

- id
- question_id -- reference to Questions
- user_id -- some identification of who submitted that result
- result -- text or 1/0 or something meaningful for that question type