Question and answer tables for a quiz app...

[Wolf_22]

I'm a bit confused about how I need to store the answers to a multiple question type of exercise coming from an app I'm trying to create.

I have 2 tables pertaining to this:
"Question" and "Answer"

In Question, I have the following general structure:
• id BIGINT(20) NOT_NULL AUTO_INC
• q_question TEXT NOT_NULL

In Answer, I have the following general structure:
• id (QUESTION ID) BIGINT(20) NOT_NULL
• a_mc_answer TEXT NULL

I'm unsure about how to store the wrong answers of the multiple choices in the answers table.

Here's the thing: the user will have the ability to create any number of possible answers to each question they decide to submit. Each question can have any number of possible answers due to its multiple choice nature. Because of this, I'll need a JavaScript-powered form that can populate the page with text inputs the user designates. Each input will correspond to a given answer and with each text input, there will be a respective check box beside it to tick and this will indicate which answer is the correct answer.

How should I store the incorrect answers, though? I thought that using some sort of string separator, but I'm not sure this would be a good idea...

Advice?

[Kalon]

what about something like this?

tblQuestions

fldQuestId
fldQuestNo
fldQuestion
fldCorrectAnswer

tblOptions

fldOptionId
fldQuestId
fldOption

[r937]

first of all, you really only need to use INTEGER instead of BIGINT, because if you are ever in danger of having close to two billion questions, other things will have to be redesigned long before you run out of numbers

to store your answers, you need a simple one-to-many relationship

CREATE TABLE questions
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, question TEXT NOT NULL
);

CREATE TABLE answers
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, question_id INTEGER NOT NULL REFERENCES questions ( id )
, answer TEXT NOT NULL
, correct CHAR(1) -- 'Y' or 'N'
);

the correct column will be used to indicate which of the multiple choice answers is correct

[Wolf_22]

So when would a MySQL database begin to show signs of fatigue? The first 100k or so records or 1 million?

As far as the 2 billion capacity is concerned, I guess I was just trying to be accommodating. I honestly have no idea what data type to use half the time because it seems so up in the air. It's like with text or varchar... Int vs. tinyint or whatever... There's so many types to select from that half the time I find myself wasting time just trying to decide between them.

[r937]

So when would a MySQL database begin to show signs of fatigue? The first 100k or so records or 1 million?

mysql can easily handle hundreds of millions of rows, it'll be your hard drive that craps out first



regarding which datatype to choose, have you seen the page in the manual which describes the storage requirements for each different type?

[Wolf_22]

Yeah, I've seen them. I just wish they were a bit more organized and easier to understand.

...but nobody said it would be easy, right? I felt like this back before I knew anything about the PHP manual, so I suppose it's just the same thing with the MySQL manual, too.