I do not want to create the database tables when the form is created. I only want to keep the form's, form's fields and options of form's fields in the tables so that on the frontend, I can pick the form id, and get all the fields/lebels associated with that forms and in case the field is check box/ radio button and dropdown, then also collect the options for those fields.
So I plan to have my schema like this :
1) Form Main (here I will keep the main data about the form, like name, id and description of the form) for example, A BUSINESS PROPOSAL FORM
2) Form Fields (here I will keep data about all the fields in the form) for example,
First Name, text field,
Last Name, text field,
Grad Year, dropdown
Practicing Area, check box
3) Options of Form Fields (here, I will keep the options for the form's fields if they are Drop down, Check box and radio button.) For example,
the ID of the form field from the 2nd table above, and then value/text. suppose that the ID of Features field above is 52 so it will have :
52, Medical Science
52, Medicine Science
52, Bio Tech
52, etc etc etc
Now, when my script get a form ID, it will grab the form details from the Table no. 1, and collect the fields and their options from Table No. 2 and 3. and now the form is ready to fill and submit.
Now, the issue:
To collect the data user enters on this form, what is the best approach ? I do not want to have 1 table for each form type. It will be 1 table for all the forms I have.
Can I have a table like this:
ID, Date submitted, Form Id, Field Id, User Entered data
1, 2011-02-01, 13, 1, Zeeshan
2, 2011-02-01, 13, 2, Hashmi
3, 2011-02-01, 13, 3, 2009
4, 2011-02-01, 13, 4, Medical Science
5, 2011-02-01, 13, 4, Microbiology
Suppose that user enters names and grad year and then select the 2 check boxes for Medical Science and Microbiology ?
Can I achieve the same by saving my data as a XML ?