SitePoint Sponsor

User Tag List

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

    Storing Different Response Types

    Okay, let me see if I can explain this 10,000 words or less...

    I have created a way for Members to provide feedback by taking an optional Article Survey associated with each Article.

    Relationships:
    Articles and Questions form a many-to-many relationship, and are linked via a "Survey Map".

    article -||-----0< article_survey_map ->0------||- article_survey_question


    Members and "Survey Maps" form a many-to-many relationship which is where the Member's comments are stored in the "Survey Results".

    member -||-------0< survey_results ->0-------||- article_survey_map


    Problem:
    I am storing each "Response" as a separate record as explained above, because there can be any universe of "Survey Questions" and therefore any number of "Survey Maps".

    If I made each Question a column, then I would either have to limit the universe of new Questions, or I would have an extremely WIDE table!!!

    So, my Table layout above takes care of that issue, however it creates a new problem...

    How do I store different types of responses?!

    I do NOT want a generic VARCHAR(1024) or TEXT data-type as that would be silly for storing BOOLEANS or INTEGERS, and yet I need a way for someone to type Moby Dick in an open-ended question field?!

    Here is what that would look like...
    Code:
    articleID	questionID	memberID	response
    ----------	-----------	---------	---------
    1		1		19		True
    1		2		19		False
    1		3		19		Once a Week
    1		4		19		9
    1		5		19		This was a really
    						interesting article...

    Potential Solution:
    The solution I have come up with is having a few columns of appropriate Data-Types to store the different types of responses.

    It would look like this...
    Code:
    key		tf	likert		mc		open
    ----		---	-------		---		-----
    1,1,19		True
    1,2,19		False
    1,3,19					Once a Week
    1,4,19			9
    1,5,19							This was a really
    							interesting article...
    Since the "universe" of Data-Types is much more finite than the potential Questions, this doesn't seem too bad.

    Basically, BOOLEAN, INTEGER, VARCHAR(64) and VARCHAR(1024) should cover all of the different Question-Types I have now or might have in the future.

    And since I anticipate just wanting to tally up all data for a particular Article Question, this design would seem to be sufficient.

    However, it still feels kind of "dirty"...

    Suggestions??

    Thanks,



    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Suggestions??
    keep it simple, and use a single VARCHAR column
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,528
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    keep it simple, and use a single VARCHAR column
    Isn't it a bad idea to have disparate Date-Types in one "catch-all" field?!

    Also, is the design I posted wrong from a Database Design standpoint? (Even if you think it isn't simple enough.)

    Thanks,


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, it isn't, and no, it's not
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,807
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Isn't it a bad idea to have disparate Date-Types in one "catch-all" field?!
    When it is unnecessary, yes. But for your situation, this truly is the best option for you.

    Quote Originally Posted by DoubleDee View Post
    Also, is the design I posted wrong from a Database Design standpoint? (Even if you think it isn't simple enough.)
    No, as it is the best way to design your tables given the requirements.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


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
  •