Database organization & planning for Mutliple Choice Questions

[come_again]

Hello everyone,

I am putting together a project where people are able to upload multiple choice questions to a larger database and then create tests from them. I have several questions (and I apologize for the length of this post) about what is the best way to go about doing this as my experience with databases is extremely limited and I am trying to plan out how I can tackle this problem. If there is already some open source software that is similar please let me know. Thank you in advance.

For my question Database:
The columns I intend to have include
1) QuestionID
2) Question
3) AnswerOpt
4) CorrectAns
5) Statistics

My first question is this: What is the best way to store the answer options? For example, I have a series of answers such as A) Daniweb B) Google C) Yahoo etc. Is the best way to simply store as one string and parse using the A), B), C) strings? How would I precent someone typing in A) into the answer options and screwing everything up (even though this is unlikely)?

Next question: In the future, I would like to be able to "link" questions. In other words, there may be a series of questions related to 1 question stem. Is there anything I should be doing now that can make making that easier in the future?

Test storage:

I would like to store the tests that users do and their answer choices. This is what is puzzling me a little bit. How should that database look like? Would I store the questions used in another string and just parse it? I would like to be able to update students how many questions they have finished, done correctly etc. With the database I have now, this seems like it would be an extremely slow method because I would have first look at userID, then parse all questions, cross-reference with the main Qbank and then finally spit out the answer. Is there a better way of doing this? Should I store questions used in a separate column in my user database? This is what I have been thinking:

Previous Test DB:
1) UserID
2) TestDate
3) PercentageCor
4) QuestionsUsed
5) Answers (answers they chose)

Again, really sorry for all the questions. Any input/opinion is appreciated.

Cheers,
cg

[diafol]

Are you going to have a fixed number of options for all tests (e.g. 5 possible answers). If so, you could get away with (questions table):

question_id
question (blob or text)
answer_1 (blob or text)
answer_2
answer_3
answer_4
answer_5
correct_ans (depends)

The correct ans could be a single value (1-5), or there may be multiple correct answers (2,3)
Scoring can be simple or complex and may be determined by the number of correct answers. This complexity is often needless nonsense though.

However if you have a variable number of answers, you may wish to have a related answers table to the questions table:

answer_id (int PK)
question_id (int FK)
answer (text or blob)

The questions table then:

question_id (int PK)
question (blob or text)
correct_ans (depends)

The correct ans would point to the answer_id(s) of the answers table. But that starts getting messy with two lots of related keys.

The tests table:

test_id (tinyint/int PK)
test_name (varchar)
test_description (varchar or text)
test_uploaded_date (datetime)
test_poster (FK on users table)
randomized (tinyint 1/0)

The test_questions table:

test_id (int PK)
question_id (int PK)

There are many ways to skin a cat, so depending on your exact needs, the table structures will vary. These are just a few rambling thoughts. You should find many references to this use (DB and MCQ) on the web.

[come_again]

Thank you for the response. Interesting way of doing it... Are there any concerns for the database too big or too slow to check?

[diafol]

Shouldn't have thought so. MySQL dbs are designed to be pretty robust and to handle hundreds of thousands/millions of records. Remember that you will be extracting a small sample of the whole data with your SQL - made more efficient with WHERE, ORDER BY and LIMIT clauses - as well as only asking for certain named fields - always avoid using *.

For certain tasks you may want to store non-normalized data, e.g. score totals for tests - this again will speed up retrieval. You don't want to be calculating scores for say 100 users on a test summary page.

Difficult to give you any hard and fast rules as the scope of your needs is not known.