Home › Forums › Back End › How do I represent this data?! › Reply To: How do I represent this data?!
September 27, 2013 at 7:40 pm
#151557
__
Participant
Can questions ever belong in more than one set?
If not, you can add a set
column in the question
table – no need for another intersection table.
if SetID = 1 and the order of answers are, “ringo, blue, yes, no” go to set 2 if SetID = 1 and the order of answers are, “John, green, no, no” go to set 3 if SetID = 1 defaults go to set 5
hmm… it’s not exactly normalized, but you could use the answers as the pattern:
question_map
+---------+--------------+
| answers | question_set |
+=========+==============+
| 6,2,T,F | 2 |
+---------+--------------+
| 4,1,F,F | 3 |
+---------+--------------+
The query would be a little complex, though, since you would have to either allow for missing characters (while still selecting the “best” match) or explicitly provide a default option in your query:
SELECT id,q
FROM question
WHERE set IN(
COALESCE(
(SELECT question_set
FROM question_map
WHERE answers = '6,2,T,F')
,5 -- this is the "default" question set
)
)
;