I need some help with the following scenario. I am creating an app that allows users to take a survey with questions that change dynamically based on the answers that are given to previous questions. I currently have the tables structured as follows:
I don't know of a way to relate Table 2 to Table 1 as shown in the example. This leads to a lot of unneccesary coding on the other side when trying to read data back out of the tables for reporting purposes.
I know I could always switch Table 2 to the following structure:
SurveyID (Int), QuestionID (Int), Answer(???)
But given the fact that the answers can be many datatype's I'm not sure it's the best way to go. Theoretically I'd have to either do a lot of type conversion to text and back, or union multiple tables, 1 for each different datatype.
Does anybody have any suggestions that might make my life easier. Am I missing something simple here???
Everything bottle down to how much coding you want to do. The more coding you desire to have, the less investment of time you make when designing your database.
In your case, if a specific answer yields a sequence of questions presented, you need to try to determine if it's a finite relationship between questions and answers.
Also, add a table that has all datatypes coded out (you can have a look at spt_values in master,) and add a table that will have all possible answers coded for each datatype. It may sound overwhelming, but may not necessarily be so.
Of course you'd need a maintenance module for those possible answers. This way you questionnaire will be truely flexible and easily maintainable.
I must confess, now that I am looking at the right table. I still can't understand the practical application of a table like this in my situation. I may be a bit nieve, but I just can't grasp why I'd use this.
The example I used is certainly simplified from the actual data. In all I have about 20 questions that are fed to a vb.net app and most have dropdownlists that are related to the questions table through an additional field. This arrangement allow most of the answers to be int. There are a few that are yes/no(bit). But there are at least 3 or 4 that are narrative (text). It's really those that are throwing me off and preventing me from using the second table structure above. Any specific ideas?