SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Thoughts on Tables for Surveys

    I am working on a new feature for my website which allows Members to rate Articles (if they so choose).

    Below are two different designs. I would be curious to what everyone thinks.


    Business Rules:
    - For now, all Survey Questions will be Multiple Choice (i.e. Scale 1 to 5) and so there is only one value stored for each.
    - One Survey (i.e. certain set of Survey Questions) is related to zero or more Articles
    - One Survey will likely have maybe 5 Survey Questions but that can change
    - Survey Questions should be "banked" and may be used on many Surveys and re-used at later dates
    - One Article can have many Survey Questions
    - One Survey Question can be related to many Articles
    - One Member can rate many Articles
    - One Article can be rated by many Members
    - One Member can respond to many Survey Questions
    - One Survey Question can be answered by many Members


    Approach #1:
    article -||------- 0<- survey_results ->0-------|- article_survey_questions
    member -||--------0<- survey_results


    article
    --------
    - id


    member
    ----------
    - id


    article_survey_question
    ---------------------------
    - id


    survey_results
    -----------------
    - article_id
    - article_survey_question_id
    - member_id



    Approach #2:
    article -||--------0<- article_survey_map ->0---------||- article_survey_question
    member -||---------0<- survey_results ->0-----------||- article_survey_map


    article
    --------
    - id


    article_survey_question
    ---------------------------
    - id


    article_survey_map
    ----------------------
    - article_id
    - article_survey_question_id


    survey_results
    -----------------
    - article_id
    - article_survey_question_id
    - member_id


    member
    ----------
    - id


    I like Approach #2 as it seems more robust. Particularly, I think the concept of a "Survey Map" is important and great for managing things. (I got the idea from work.)

    Thanks,


    Debbie

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    I would allow yourself a little more wiggle room on the questions, rather than think everything is going to be an answer of 1-5.

    questions - possible questions for all articles
    --------
    question_id* AI
    question_text

    question_answers - possible answers for all questions
    --------------
    question_answer_id * AI
    question_id
    question_answer_text

    articles
    --------
    article_id* AI
    other info about article....

    article_questions - questions that are to be asked in each article
    ------------
    article_question_id* AI
    article_id
    question_id

    article_results - answers
    ------------
    article_result_id*
    article_question_id - INDEXED UNIQUE VALUE
    question_answer_id
    member_id

    members
    --------
    member_id


    EDIT: Though looking back it looks like you had almost exactly this. Thought i didnt see anywhere to allow fulle text answers as possiblities

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    K. Wolfe,

    I have no idea where you were going with your example, but that doesn't help answer my OP.


    Debbie

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Well I'm sure you'll get some help after the bunny with waffle post

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Well I'm sure you'll get some help after the bunny with waffle post
    Huh????

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Google it


Bookmarks

Posting Permissions

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