0

Lets say I have a table with "Groups of Questions"

GroupID | value
--------+------
42      | How often do you
9071    | Other question
...

And, for each group of questions I have "questions" and "possible answers"

Group | QuestionID | value
------+------------+------
42    | 5          | ... brush your teeth?
42    | 89         | ... go to the movies?
9071  | 709        | ... another question ...
...

Group | Answer | value
------+--------+------
42    | 134    | several times per day
42    | 135    | twice a day
42    | 71     | once a day
42    | 803    | every other day
42    | 8      | once a week
42    | 666    | once a month
...

Now, in PHP/HTML, I do a (virtual) full cross product of questions and possible answers of group 42 to build a 2-entry table where the user will select his/her answers (HTML version)

How often do you      | N/d | 2/d | 1/d | d*2 | 1/w | 1/m |
----------------------+-----+-----+-----+-----+-----+-----+
... brush your teeth? | ( ) | (X) | ( ) | ( ) | ( ) | ( ) |
... go to the movies? | ( ) | ( ) | ( ) | ( ) | (X) | ( ) |

I know I'll need to
insert into answers (question, answer, ...) values (5, 135, ...) for teeth
and
insert into answers (question, answer, ...) values (89, 8, ...) for movies

The question is: is there a way to enforce that both question and answer, in the answers table, "belong" to the same group of questions?

I'd like that
insert into answers (question, answer, ...) values (709, 71, ...)
be disallowed, because question 709 "belongs" to group 9071 and answer 71 belongs to group 42.

pmg
  • 106,608
  • 13
  • 126
  • 198
  • If I understood correctly, you need a **constraint on Answers table**, that states that the group of the answer is equal to the group of the question. Check your database help to see how to precisely write this... – KLE Oct 16 '09 at 12:49

2 Answers2

1

Well, the answer relates to the question, not to the group (at least not directly), so I would have thought the "possible answers" table should be:

Question | Answer | value
---------+--------+------
5        | 134    | several times per day

Then when you insert an answer, you give the QuestionId and AnswerId, and together they reference the "possible answer" and thereby, indirectly, the group.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • What's to stop QuestionID = 709 (from GroupID = 9071) and Answer = 134 (from GroupID = 42) appearing on the same row? – onedaywhen Oct 16 '09 at 13:00
  • Answer 134 would not be a possible answer for Question 709, so therefore it would be invalid for the user to choose it at all. – Tony Andrews Oct 16 '09 at 13:18
  • The same set of answers can be used for many many questions. With your approach, the possible answers would be repeated in the `Question` table for every question in the same group. – pmg Oct 16 '09 at 13:25
  • @Tony Andrews: what I meant was, what is there _in the database_ to make that combination invalid? You shouldn't rely on the UI to offer only valid choices. – onedaywhen Oct 16 '09 at 13:36
  • @onedaywhen: in the database I have answer 134 belongs to question 654 (or whatever), so when the question is 709, 134 isn't a valid answer. So not sure what your point is? – Tony Andrews Oct 16 '09 at 14:12
  • 1
    @pmg: OK, I see your point (at last!): a group like 42 consists of a set of questions that all have the same set of possible answers. Now my answer would be same as onedaywhen's. – Tony Andrews Oct 16 '09 at 14:15
  • Light bulb goes on :) Thanks for hanging on in there. – onedaywhen Oct 16 '09 at 15:34
0

You include the columns Group, QuestionID and Answer then create two foreign keys, one on (Group, QuestionID) and the other on (Group, Answer).

onedaywhen
  • 55,269
  • 12
  • 100
  • 138