troubleshooting Question

Table Structure for Form Data

Avatar of hdiadmin
hdiadmin asked on
ASP.NETMicrosoft SQL Server 2008
4 Comments2 Solutions293 ViewsLast Modified:
We have a web based application which has users fill in online assessments.
An assessment is a form which looks like this.

AssessmentName

1. RowName       CheckBoxControl      RadioButtonList
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros