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…
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…
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
