hdiadmin
asked on
Table Structure for Form Data
We have a web based application which has users fill in online assessments.
An assessment is a form which looks like this.
AssessmentName
The assessments are designed by a government agency and change quite frequently. We are trying to build the tables to store the information in a way that will work for different assessments without modification.
Our current data base design is as follows:
AssessmentMain
ID (Primary Key, Uniqueidentifier)
Name (nvarchar(50))
AssessmentRows
ID (Primary Key, Uniqueidentifier)
AssessmentID (FKey AssessmentMain, ID)
Text (nvarchar(100))
ControlTemplate
ID (Primary Key, Uniqueidentifier)
AssessmentRowID (FKey AssessmentRows, ID)
ControlType (int)
TextResponse
ID (Primary Key, Uniqueidentifier)
Value (nvarchar(100))
ControlID (FKey ControlTemplate, ID)
CheckBoxResponse
ID (Primary Key, Uniqueidentifier)
Value (bit)
ControlID (FKey ControlTemplate, ID)
We have created one table for storing all the free text responses, another for storing boolean responses and so on. All of these responses are associated with a control record which in turn is associated with a row record.
So each assessment can have multiple rows and each row can have multiple control records.
The problem I have is in querying this setup. There doesn't seem to be a straightforward way to query the value for a particular control because that value maybe either stored in the CheckBoxResponse or the TextResponse table. Currently I have to write a stored procedure and use the ControlType field in the ControlTemplate table to determine which table to fetch the control's value from.
Question:
1 - Can anyone suggest a better table design to accomodate the forms?
2 - Is there a straight forward way (without stored procedures) to query the control values in the existing design?
I realize this is a rather long winded problem but I could not think of a better way to describe it. We will be most appreciative of any input. Please feel free to ask me for clarification.
An assessment is a form which looks like this.
AssessmentName
1. RowName CheckBoxControl RadioButtonList
2. RowName CheckBoxControl RadioButtonList
3. RowName TextBox
2. RowName CheckBoxControl RadioButtonList
3. RowName TextBox
The assessments are designed by a government agency and change quite frequently. We are trying to build the tables to store the information in a way that will work for different assessments without modification.
Our current data base design is as follows:
AssessmentMain
ID (Primary Key, Uniqueidentifier)
Name (nvarchar(50))
AssessmentRows
ID (Primary Key, Uniqueidentifier)
AssessmentID (FKey AssessmentMain, ID)
Text (nvarchar(100))
ControlTemplate
ID (Primary Key, Uniqueidentifier)
AssessmentRowID (FKey AssessmentRows, ID)
ControlType (int)
TextResponse
ID (Primary Key, Uniqueidentifier)
Value (nvarchar(100))
ControlID (FKey ControlTemplate, ID)
CheckBoxResponse
ID (Primary Key, Uniqueidentifier)
Value (bit)
ControlID (FKey ControlTemplate, ID)
We have created one table for storing all the free text responses, another for storing boolean responses and so on. All of these responses are associated with a control record which in turn is associated with a row record.
So each assessment can have multiple rows and each row can have multiple control records.
The problem I have is in querying this setup. There doesn't seem to be a straightforward way to query the value for a particular control because that value maybe either stored in the CheckBoxResponse or the TextResponse table. Currently I have to write a stored procedure and use the ControlType field in the ControlTemplate table to determine which table to fetch the control's value from.
Question:
1 - Can anyone suggest a better table design to accomodate the forms?
2 - Is there a straight forward way (without stored procedures) to query the control values in the existing design?
I realize this is a rather long winded problem but I could not think of a better way to describe it. We will be most appreciative of any input. Please feel free to ask me for clarification.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There are pros and cons on what to store in the response. Generally, singleton controls, such as the text response, a check box, radio button list, or a drop down list, the response can be written or set directly from a single column using the key property (text, selectedvalue). For multiple selections controls, such as a check box list or list box, the response key values are concatenated with a delimiter, such as a semicolon, before being written to the single column. These responses would have to be split apart before setting the values in the control. These actions would be done in the presentation layer.
Another option would add a child table under the Responses to handle multiple responses.
Another option would add a child table under the Responses to handle multiple responses.
ASKER
I have some questions regarding the Responses Table.
We expect the user response to consist of the following types:
A - Free text response.
B - A Check box or a group of check boxes
C - Radio buttons or drop down slection type
Questions
1 - How will I structure the responses table keeping in mind the above response types.
Originally I was thinking of storing the options for the radio buttons, and checkboxes as name value pairs in a table. Then I was thinking of grouping the responses to a check box list using some key. That way I would be able to handle checkbox groups of varying lengths. I am unsure how I can do this with just one response table.