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.
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
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
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,3,19 Once a Week
1,5,19 This was a really
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"... :-/
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?! :eek:
Also, is the design I posted wrong from a Database Design standpoint? (Even if you think it isn't simple enough.)
no, it isn't, and no, it's not
When it is unnecessary, yes. But for your situation, this truly is the best option for you.
No, as it is the best way to design your tables given the requirements.
This topic is now archived. It is frozen and cannot be changed in any way.