-2

I want to build an application that allows a user to build its own form(s).
A user can input data into its form and query the data too. The form can be edited after being created and used ( add/remove fields from it ). The form can have small text box, big text box, single list, check box, or a drop down menu.

I've been reading and asking around about which approach is better for building this kind of databases and I got no straight/good answer. I read that in this kind of application, the column based design (and representing each field as a column) is not a good idea since a user can be adding many fields and this is not a good idea, since there should be a limit on the number of columns in a table. However, I can't know in advance how many fields the user will add. (I'm not sure what they mean. It will help a lot if you explain further by giving an example.)

I thought a good idea might be to group the fields according to their types. (type 1: text box , type 2: drop down list, type 3 : single list... and so on). I want to design my tables so that I can upgrade my application and add a new type any time I want without ALTERING my schema. So I was thinking to have 2 tables, one table to hold the metadata about the field: its type, its name, its position maybe in the form and another table to hold the actual value of the field. I can't seem to know what exactly to have in these tables and how to design them correctly. Your help is highly appreciated.

Bill the Lizard
  • 369,957
  • 201
  • 546
  • 842
PeacefulSoul
  • 47
  • 1
  • 3
  • Peaceful, what is the intention for the app? If I understand correctly, you want any user to be able to create their own form, that presumably others can then use to fill in information? Would it be accurate to think of this as a kind of 'SurveyMonkey' where people can create surveys for others? – sscirrus Dec 11 '10 at 23:43
  • 1
    The last part of your question is the answer to your question. One table describes the fields of the form, and another holds the response values. Those different pieces of metadata (type, name, position) are columns of the metadata table. – Dan Grossman Dec 11 '10 at 23:46
  • yes i know ! but i dont know what exactly to put in these two tables so that if i want to add another type later on..i wont have to alter the schema.. say at first i had only text boxes and single list..then after ..i decided to let users use a dropdown menu...how should these two tables be in order to give me flexibility in adding new types without changing the schema..better said, are these 2 tables enough ?? – PeacefulSoul Dec 12 '10 at 00:08
  • possible duplicate of [so lost in designing those tables !](http://stackoverflow.com/questions/4422696/so-lost-in-designing-those-tables) – agf Aug 25 '11 at 00:04

1 Answers1

0
**Form**     A Form Template
----
formID (PK)
authorID
Name



**FormField**    =  1 row for each new question on the form
----------   
FormFieldID (PK)
FormID   (FK)
FieldID  (FK)
SortOrder  INT


**FormFieldOptions**     - if the form field is a selectbox this will store the options availble to pick from
---------------
FormFieldID   (fk)
Name
SortOrder  INT


**Field**            -- The will be Text Short, Text Long, Checkbox, Radiobutton, Selectbox etc
------
FieldID   (PK)
Name      
TYPE

That is for creating forms. For saving users answers/responses to the form I will leave for an exersise for the reader ;)

Daveo
  • 15,776
  • 10
  • 45
  • 69