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,915
    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,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Suggestions??
    keep it simple, and use a single VARCHAR column
    rudy.ca | @rudydotca
    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,915
    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,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, it isn't, and no, it's not
    rudy.ca | @rudydotca
    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
    5,215
    Mentioned
    153 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.


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
  •