My website allows members to take surveys about articles.
Here is some sample data…
QUESTION
id stem
--- --------------------------------------
1 Where have you made friends?
2 What colors do you like?
ANSWER
id question_id choice
--- ------------ ---------
1 2 Work
2 2 Church
3 2 Other
4 6 Red
5 6 Green
6 6 Blue
7 6 Yellow
I have a junction table called “t_survey_multiple_response” which ties together ARTICLES, MEMBERS, QUESTIONS and ANSWERS.
I am trying to ensure that any record in this junction table has valid Question-Answer pairs, e.g. “Yellow” (id=6) would not be a valid response to Question #1.
Here are my table designs…
t_survey_answer
CREATE TABLE `t_survey_answer` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '(PK)',
`question_id` mediumint(8) unsigned NOT NULL COMMENT '(UK1)(FK)',
`choice` varchar(128) NOT NULL COMMENT '(UK2)',
`sort` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1
t_survey_multiple_response
CREATE TABLE `t_survey_multiple_response` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '(PK)',
`article_id` mediumint(8) unsigned NOT NULL COMMENT '(UK1)(FK)',
`survey_question_id` mediumint(8) unsigned NOT NULL COMMENT '(UK2)(FK)',
`survey_answer_id` mediumint(8) unsigned NOT NULL COMMENT '(UK3)(FK)',
`member_id` mediumint(8) unsigned NOT NULL COMMENT '(UK4)(FK)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
So, I am trying to create a multi-field Foreign-Key Constraint, and it appears that MySQL requires an index on the related fields.
QUESTION:
In order to achieve my goal of making sure only valid Question-Answer keys can be inserted into the “t_survey_multiple_response” table, must my Composite-Keys in the “t_survey_multiple_response” and the “t_survey_answer” tables be UNIQUE??
Hope that makes sense?!
Sincerely,
Debbie