MySQL db structure help

Question

I'm working on a quiz project and I want create a mysql structure in such a way that:

questionID: A unique question identification number(primary key)
testID: A unique test identification number(question belongs to this test)(primary key)
questionOrder: The order of the question within the quiz questions, ie this question is n-th question in the quiz. I want this value to come from mysql, so that when I insert a new question to db, I don't have to calculate it
One question can be in multiple different tests.

I have couple of questions:
1) I have the following code but I get:
Incorrect table definition; there can be only one auto column and it must be defined as a key
How can I fix this?

2) This structure doesn't allow a question to belong to multiple quizzes. Any idea to avoid this?

3) Do you think this structure is good/optimum, can you suggest anything better?


CREATE TABLE `quiz_question` (
  `questionID` int(11) NOT NULL auto_increment,
  `quizID` int(11) NOT NULL default '0',
  `questionOrder` int(11) NOT NULL AUTO_INCREMENT,
  `question` varchar(256) NOT NULL default '',
  `answer` varchar(256) NOT NULL default '',
  PRIMARY KEY  (`questionID`),
  UNIQUE KEY (`quizID`, `questionOrder`),
  KEY `par_ind` (`quizID`, `questionOrder`)
) ENGINE=MyISAM;

ALTER TABLE `quiz_question`
ADD CONSTRAINT `0_133` FOREIGN KEY (`quizID`) REFERENCES `quiz_quiz` (`quizID`);

CREATE TABLE `quiz_quiz` (
  `quizID` int(11) NOT NULL auto_increment,
  `topic` varchar(100) NOT NULL default '',
  `information` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`quizID`)
) ENGINE=MyISAM;

Thanks for reading this.

pcrews · Accepted Answer · 2010-12-13 22:57:28Z

1

1) You can only have one AUTO_INCREMENT column per table. It should be a key. Generally, it's part of / is the PK.

2) A 'quiz' would be an entity composed of questions. You should have 3 tables: 1 - quiz_question: quest_id, question, answer 2 - quiz_quiz: quiz_id, topic, info 3 - quiz_fact: quiz_id, quest_id, quest_order

The quiz and question tables hold the per-item (quiz/question) information. The quiz_fact defines how a quiz is composed (this quiz has this question in this order).

3) My only suggestion would be to use Drizzle instead ; ) Seriously though, play with things - 'good enough' often is. If it suits your needs, why tinker? Otherwise you can ask more detailed questions once you have this up and runnning (ie my queries are too slow on such and such operations).

answered Dec 13, 2010 at 22:57

pcrews

1704 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gdj · Accepted Answer · 2010-12-13 22:50:09Z

1

1) Do the order increment yourself. The DB will only do it if it's part of a PK. You might be able to hack it by making a composite key containing the order column but it's not worth it.

2) Rename quiz_question to question (and quiz_quiz to quiz). Make a new quiz-question join table called quiz_question. It should have a quiz ID and a question ID, linking a quiz to a question. As the same question will have different orders on different quizes, put the question order on the new quiz_question. You no longer need a quiz ID on the question table.

answered Dec 13, 2010 at 22:50

gdj

1,29510 silver badges10 bronze badges

Comments

snarkback · Accepted Answer · 2010-12-13 22:56:47Z

1

Remove AUTO_INCREMENT from the questionOrder field.

As far as having MySQL set the value in the questionOrder field, then do that in a subsequent UPDATE query. Usually, you'd want the administrator of the test, using your admin utility, to be able to adjust the ordering of questions. In that case, you just enter an initial value +1 higher than the highest previous ordering value (on that test). Then, you can let them adjust it something like the manner of adjusting a Netflix queue :)

edited Dec 13, 2010 at 22:56

answered Dec 13, 2010 at 22:48

snarkback

1964 bronze badges

Collectives™ on Stack Overflow

MySQL db structure help

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related