Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2006
    Posts
    34

    should have posted here

    sorry for cross posting...didn't see "database concepts and design" when i posted originally, and could move it to here, so i will copy and paste it here.
    ________________ from MySQL forum

    Dear Dr. DB,

    I have a few question regarding the design of a database. I am creating a survey. Suppose my questions were:

    1. Do you like this? (option buttons = yes or no)
    2. Which software do you use? (checkboxes)
    A. Access
    B. Excel
    C. Word
    D. Outlook

    My question is, should i create a table for each response item like:
    tbl_Item1
    -----------
    id Answer <-- these are columns

    tbl_Item2
    -----------
    id A B C D <-- these are colums


    OR should I create one answers table like
    tbl_Answers
    --------------
    ansID question_id Choice
    1 1 Yes
    2 1 No
    3 2 A
    4 2 B
    5 2 C
    6 2 D


    OR am I complete igornant to the proper way of designing a database survey?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Your last design is best - i.e. all answers go in the "Answers" table. To create a table per item would be liking having a separate address book for each of your friends!

  3. #3
    Join Date
    Feb 2006
    Posts
    34

    thanks

    thanks tony, that's what i thought.

    as a follow-up question, my survey will be over the internet. i plan to have

    TABLE 1. tbl_questions (using the & to separate columns)

    question_ID & question_text
    ---------------------------------
    1 & Do you like dogs?
    2 & Which pets do you have?

    TABLE 2. tbl_answers

    answer_ID & question_ID & answer_text
    -------------------------------------------------
    1 & 1 & Yes
    2 & 1 & No
    3 & 2 & Dog
    4 & 2 & Cat
    5 & 2 & Pig
    *where question 2 allows multiple answers.

    Suppose there were 2 survey participants (1 and 2)

    TABLE 3. tbl_RESPONSEs

    response_ID & question_1 & question_2
    ------------------------------------------------
    1 & answer_ID (1) & answer_ID(1)
    2 & answer_ID (2) & answer_ID(2)
    * this table tracks an ananomous responder by response_ID and tracks the answer ID for each question.

    does this sound like a reasonable design, or is the response table off? should i store different information in the response table?

    Thanks again!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    The Responses table would be more like:

    responses (participant_id, question_id, answer_id);

    i.e. "participant X's answer to question Y was Z"

  5. #5
    Join Date
    Feb 2006
    Posts
    34
    thanks again tony

  6. #6
    Join Date
    Feb 2006
    Posts
    34

    but how do I

    handle multiple checkbox options?

    for example,

    Q: Which pets do you own?
    A: Dog, Cat, Pig

    in the table I would have
    answerID & questionID & Text
    -------------------------------------
    1, 1, Dog
    2, 1, Cat
    3, 1, Pig

    but there are six combinations.
    a response could be: Dog and Cat, or Dog and Pig, or all three.

    the response table with (participantID, answerID, questionID) is good for exclusive choices, but what about inclusive choices?

  7. #7
    Join Date
    Feb 2006
    Posts
    34
    8 combos i mean

    dog cat pig
    0,0,0
    0,0,1
    0,1,0
    0,1,1
    1,0,0
    1,0,1
    1,1,0
    1,1,1

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    The same table design allows that, but now the unique key is all 3 columns:
    Code:
    create table inclusive_responses
    ( participant_id integer
    , question_id integer
    , choice_id integer
    , primary key (participant_id, question_id, choice_id)
    );
    
    insert into responses (participant_id, question_id, choice_id) values (1,1,1);
    insert into responses (participant_id, question_id, choice_id) values (1,1,2);
    insert into responses (participant_id, question_id, choice_id) values (1,1,5);

  9. #9
    Join Date
    Feb 2006
    Posts
    34
    tony, first of all, thanks again for your help. you are probably saying, go buy a book on db design...which i did...but i need to fill in the gaps....i have this project to do (self-initiated = not for employment, but for school project) with that said, sorry to be a pest, but...

    let me see if i have this right.

    1. the "choice-id" corresponds to the "answer_id" before, right?
    2. assigning all three as primary key make the total combination a primary key, not any one of the fields, right?

    3. is this the 'standard' design of survey items(question), responseItems(answers), and responses?

    4. this will be a web-based survey. i plan to "pull" in the questoins and answers from these tables...so on the one hand i want the answers to be "text", but on the other hand, when i compile the "statistics" i want to have codes. For example, i want the web form to have "Yes" and "No", but for backend i am going to use "1" and "0". Or better yet, the participant will see "Dog", "Cat", "Pig", but i want to "code" those as "1", "2", "3". Is it best to create a separate table with the coding system and relate it to the answers, or create another field(s) in the same table?

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    1. Yes

    2. Yes

    3. I don't know - it's just what I woiuld find natural to do. You could look at this example on DatabaseAnswers.

    4. A separate table.

  11. #11
    Join Date
    Feb 2006
    Posts
    34
    sweet!

    :-)

Posting Permissions

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