Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2001
    Location
    CT
    Posts
    8

    Unanswered: Table Structure Problem

    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:

    Table 1 - Questions
    QuestionID (Int), Question (NvarChar)

    Table 2 - Completed Survey
    SurveyID (Int), Question 1(Int), Question 2 (Bit), Question 3(Text)

    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???

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  3. #3
    Join Date
    Aug 2001
    Location
    CT
    Posts
    8
    I might be a little over my head. I have review spt_values and I can't even figure out what the h--- that table is for.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmm, why did I say you needed that table, when you actually needed spt_datatype_info?

  5. #5
    Join Date
    Aug 2001
    Location
    CT
    Posts
    8
    Whew! That makes me feel better. The real question is why didn't I notice that tabel when I went to look at the other one

  6. #6
    Join Date
    Aug 2001
    Location
    CT
    Posts
    8
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •