CODEWITHSUNDEEP
mysql
Caner
Caner

Reputation: 59318

MySQL db structure help

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.

Upvotes: 2

Views: 116

Answers (3)

pcrews
pcrews

Reputation: 170

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).

Upvotes: 1

snarkback
snarkback

Reputation: 196

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 :)

Upvotes: 1

gdj
gdj

Reputation: 1295

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.

Upvotes: 1

Related Questions