How to design a database for relational dynamic forms?

Question

My requirements are:

• Need to be able to dynamically add forms with different fields/field types
• A form can include a dropdown that gets filled with the values of another

submitted form Eg : Staff form :

1. First name(text box)
2. Last name(text box)
3. Department(dropdown) -> should be bind to department form

Department Form :

1. Department name(dropdown)

Other information:

• My database is SQL server
• I’m looking for performance
• Each table can have more than a million records
• We have more than 1000 users in our system
• We need to get different reports on submitted record by user
• Reports need to have filtering functionality on all columns

Options:

1. Create one table per form
2. add 2 tables for form-structure and field-structure then 2 more tables for submitted-form-value and submitted-field-value

Any other option or idea?

Answer 1

Well, I would probably create a database structure like this:

-- This table will hold the forms
CREATE TABLE tblForm
(
      Form_Id int IDENTITY(1,1) PRIMARY KEY
    , Form_Name varchar(100)
    -- Any other form related data such as create date, owner etc`
)

-- This table will hold the input types (i.e text box, combo box, radio buttons etc`)
CREATE TABLE tblInputType
(
      InputType_Id int IDENTITY(1,1) PRIMARY KEY
    , InputType_Name varchar(100)
)

-- This table will hold the inputs used in the form
CREATE TABLE tblFormInput
(
      FormInput_Id int IDENTITY(1,1) PRIMARY KEY
    , FormInput_Form int FOREIGN KEY REFERENCES tblForm(Form_Id)
    , FormInput_InputType int FOREIGN KEY REFERENCES tblInputType(InputType_Id)
    , FormInput_Name varchar(100)
    , FormInput_DisplayOrder numeric(18,18)
    , FormInput_DefaultText varchar(100)
)

-- This table will hold the texts and values used for combo boxes, radio buttons, check boxes etc`
CREATE TABLE tblFormInputExtraData
(
      FormInputExtraData_FormInput int FOREIGN KEY REFERENCES tblFormInput(FormInput_Id)
    , FormInputExtraData_Text varchar(100)
    , FormInputExtraData_Value varchar(100)
)

Points of interest:

1. I've used varchar(100) for all textual data just as an arbitrary number for this example. You should choose the appropriate content length for your application.

2. I've used varchar also to store the value of inputs such as combo boxes and radio buttons, since it's easy. If you intend to use non-textual values (such as .Net objects, or images, you should change this data type to whatever fits your needs - xml and varbinary comes to mind.