Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Help with Data Model for Survey

    Hi. I am trying to design a database for a very simple web survey. It is one question with three mult choice answers. Users select check box, submit, and see the total per answer increase as theirs is added.

    I have no problem recording the number of "votes" per possible answer - IE What is your favorite item? 1. Apple 2. Chicken 3. Soap. Data reads as - Apple = 23, Chicken = 45 , Soap = 2

    The challenge is to also record the name of the survey, date of survey (hard coded), and the answer data.

    I assume that one table will house: SurveyName (Primary Key), SurveyDate.


    THIS IS WHERE I AM STUCK
    - Linking another table using SurveyName I need to define each mult choice answer.

    SurveyName, (SurveyAnswers) or Apple, Chicken, Soap

    - Per each mult choice answer I need to record the number of "votes"

    Apple = 23 , Chicken = 45 , Soap = 2

    Growth allowance:
    - The mult choices will be dynamic in the amount that could exist each day. One day could have three possible answers the next seven.

    - The individual mult choice answers need to be named so I can track which answer was chosen. These names will also change per survey.

    Apple = 23 , Chicken = 45 , Soap = 2

    Car = 43 , Truck = 24 , Bike = 7, Train = 56 , Boat = 87

    Any thoughts?
    Thanks
    Rich





    -
    Last edited by freaksarise; 01-07-04 at 14:51.

  2. #2
    Join Date
    Jan 2004
    Posts
    4
    Would it have to be like this or is there an easier way with less tables?

    Table 1

    SurveyID (KEY)
    SurveyName
    SurveyDate (hard coded)


    Table 2 linked by SurveyID

    SurveyID
    Mc1 (Multiple Choice Answer 1)
    Mc2
    Mc3
    .
    .
    .
    Mc12 (leave room up to twelve choices)


    Table 3 (linked by each Mc or multiple choice)

    Mc1
    Mc1Name (IE Apple)
    Mc1Value (IE 23)

    Table 4 through 12

    Mc2
    Mc2Name (IE Chicken)
    Mc2Value (IE 45)





    Would this be correct and efficient?
    I guess I will have to develop a way to give the Mc field it's own ID. Perhaps adding the SurveyID to the MC field?

    Any thoughts?
    Thanks
    Rich

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Sorry here is a possible fix to my last question:

    Table 1

    SurveyID (KEY)
    SurveyName
    SurveyDate (hard coded)


    Table 2 linked by SurveyID

    SurveyID
    Mc1_ID (Multiple Choice Answer 1)
    Mc1_Name (IE Apple)
    Mc2_ID
    Mc2_Name
    Mc3
    .
    .
    .
    Mc12 (leave room up to twelve choices)


    Table 3 (linked by each Mc or multiple choice)

    Mc1_ID
    Mc1_Value (IE 23)

    Table 4 through 12

    Mc2_ID
    Mc2_Value (IE 45)

    Would this be correct and efficient?

    Any thoughts?
    Thanks
    Rich

  4. #4
    Join Date
    Jan 2004
    Posts
    4
    I figured out less tables and what I believe is the most efficient way and is normalized:


    Table 1

    SurveyID (KEY)
    SurveyName
    SurveyDate (hard coded)


    Table 2 linked by SurveyID

    SurveyID
    Mc1_Name (IE Apple)
    Mc1_Value (IE 23)
    .
    .
    .
    Mc12 (leave room up to twelve choices)


    I LEFT THIS UP HERE INSTEAD OF OVERWRITING IN ORDER TO HELP SOMEONE ELSE OUT IN THE FUTURE. THIS WAY THEY CAN TRACE MY THOUGHTS. HOPE IT HELPS!!

    Still..............any thoughts?
    Thanks
    Rich

Posting Permissions

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