Use database or individual files and then best way to setup tables?

[galvin]

Sorry, very much a newbie and trying to grasp the "normalization of tables" concept but not there yet

 

Let's say I'm going to have a website where users can take quizzes/tests.  And hopefully one day there will be a TON of available tests to take.

 

I'm going to use AJAX to check user-entered answers against the actual answer.  Couple questions...

 

1.  GENERAL SETUP QUESTION:  Is it better to store the actual answers to all the test questions (which one day could be thousands and thousands) in a MySQL database, or is it better to create individual files for each an every test (i.e. test1.php, test2.php, etc).

 

I'm assuming it's better to keep them in a MySQL database, but looking for confirmation before I begin

 

2.  NORMALIZATION QUESTION:  Assuming it is correct that it's better use MySQL for this scenario... All tests will vary in how many answers there are.  One test might have 10 answers, another test might have 100 answers, and everything in between. 

 

What is the best/proper way to setup the table(s) containing all of these answers?  The way I  see it, I have two main options...

 

A.  Have one table called "testsandanswers" with a column for all possible answers up to the max that any test will have (we'll say 100 answers is the max).

 

So there would be columns of "testid" and maybe "testname" and 100 more columns for "answer1", answer2", "answer3", etc all the way up to "answer100".

 

Then if a test has only 10 answers, columns "answer11" thru "answer100" will be left blank.

 

If a test has 100 answers, then there would be data in all columns.

 

B.  Have one table called "Tests" (with a field of "testid" and "testname") and another table for "Answers'" (with columns for "testid" and "answer" and "answerid"). 

 

I'm guessing that option B is probably better since there would never be empty fields in the database.  However, on the flip side, I feel like the server would be put under more stress looking through this huge table (option B) for all the lines that have a certain "testid" so that it can find all the answers for that test. 

 

With option A, the server would only have to look for the ONE line with the certain "testid" (I could even use "LIMIT 1" since there will only be one) and all the answers it may need to check will be right there.

 

I know I'm probably just not knowledgable enough about normalization to understand the best way to do this, so if anyone can point me in the right direction, I'd greatly appreciate it.

[ignace]

Quiz (id, ..)

Question (id, quiz_id, ..)

Answer (id, question_id, is_correct_answer, ..)

 

You should also think about edge-cases, like: what if a user removes an answer, or rephrases it?

[galvin]

Thanks Ignace. Quick follow-up (which I guess falls into the category of "edge-case" that you mentioned)....

 

If I accept a few different answers for the same question (say 4 acceptable answers), should I store the answers as...

 

1.  Four separate lines in the ANSWERS table (all with their own unique answer id, but the same question id)

 

OR

 

2. Store them on the same single line in the database (with one answer id) and just separate them by comma in the database field and implode them when comparing the user entered answer to the actual answer(s)?

 

OR

 

3.  Does it not really matter?

 

OR

 

4.  Does my question not make sense?

 

[ignace]

You should create a new entry for each answer given by the user.

 

UserResult (question_id, answer_id)

 

But like I already said, you should mind the edge-cases.