Effective way of creating tables

[Riteman]

Am creating an online examination website. I need to store the answers answered by the participants who are given a unique ID. So I will be storing ID, Answers for the question, score. So I think I have two ways to do it.

1. Create a table for every student with columns QuestionID, Answer, Score (When 1000 participants participate what can I do here?)
2. Create a table with ParticipantID, Question1, Score1, Question2, Score2,...... (Too many columns when questions count is around 200)

Which is the optimal way of creating the table for above scenario. Since I can understand well that MySQL can have 1000s of tables. But only limited columns.

[rjent]

I would use two tables related to each other by participant. Your relation in a one to many between the participant and the "results" is cleaner IMHO. Having all of the answers in one file has advantages, and you have a DB with participant info that allows other activity with a given participant.

Good luck!

[Taywin]

Both way are inefficent and bad. Even though you are allowed to create many tables, it is a very bad way to use that many to solve a simple problem especially in this case.

As rjent said, 2 tables should be enough. However, you do not give enough information for the requirement. Therefore, I can't give a precise answer for now. You need to answer the following questions...

Does each question have the same number of answers? For example, every question is multiple choices and will always has 4 answers. Or you have to hold separated table for them?
Do you need to save the participant information (besides ID) in your requirement?

[Riteman]

I have separate table to maintain the details of all participants. Apart from this I needed to store the answers. Not all questions MCQ. Some are like filling the blanks. I don't bother about question, the thing is that I want to store the answers and score for every question answered. again questions are in a separate table.

In short, till now, I have these tables:
1. participant details - participant info
2. question list MCQ - questionid, question, choice1, marks1, choice2, Marks2,..., marks4
3. question list 2 (fillup type) - questionid, question, answer, marks

As for now, I have automated to create a table with questionid, answer, score for every student who registers their name and other details.

[diafol]

This can be as simple or as complicated as you wish to make it. Trouble is that when we start to create a simple system, we then think, oh, that would be cool. And if we set up all the data into a couple of tables, we run the risk of having to redesign the whole thing later on. So setting up the DB in order to make it as flexible and extensible as p[ossible is a good thing to do IMO. A quick idea (off top of my head)...

TABLES
users
user_id(PK), other fields
mcqtest
mcq_id(PK), rand_order (tinyint 0/1), rand_answers (tinyint 0/1), retake (tinyint 0/1), opendate, closedate, other fields [this for a specific test]
questions
q_id, q_content, ans1_content... ans4_content, correct_ans [this can be quite complicated depending on your needs - e,g, multiple correct answers, specific combos, either/or]
test_questions (link table)
tq_id (PK)
mcq_id (FK)
q_id (FK)
test_results
tr_id (PK)
tq_id (FK)
user_id (FK)
ans [data dependent on how scoring set up]
q_score [non normalized field for convenience]
summary_results (not normalized)
res_id (PK)
user_id (FK)
mcq_id (FK)
totalscore

You'll notice non-normalized datafields. As a rule you'd want to avoid this where possible, but in the real world, you end up requerying the same old data and calculating totals from many rows in huge tables. This can be a drain on resources, so the odd bit of "cheating" may be permissible. I stand to be corrected on this though.

[Riteman]

yeah you are right but, in MCQ for my requirement, every choice has certain marks. say for choice 1 - 0.6, choice 2 - 1, choice 3 - 0.3, choice 4 - 0.4.

Also test_results displays answer for only one user for a question. my question set contains a combination of all questions in random from mcq table and filling the blanks table. so I need only answer to be recorded for each candidate.

[diafol]

OK, so instead of ans, use your ans1, ans2... etc.

Not sure if I understand your 2nd point.

test_results holds all answers by all users in all tests (tq_id by relationship links to mcq test and specific question). Again ans may need to be ans1, ans2... etc

[Taywin]

Just to throw in a quick idea...

/*
.--------------.            .-------------------------.
| Participants |  1 : many  | ParticipantAnswers      |      
|  -id (int)   |<---------->|  -participant_id (FK)   |
|  -other info |            |  -question_id (FK)      | many : 1
'--------------'  1 : many  |  -answer_id (FK)        |<------.
                .---------->|  -answer (varchar/text) |       |
               /            '-------------------------'       |
              /                                               |
             /                                                |
.---------------------------.            .-------------------------.
| TestQuestions             |  1 : many  | TestAnswers             |
|  -id (int)                |  1 : 0     |  -id (int)              |
|  -question_type_id (FK)   |<---------->|  -question_id (FK)      |
|  -question (varchar/text) |            |  -answer (varchar/text) |
|  -start_date(date)        |            |  -weight (float)        |
|  -end_date(date)          |            '-------------------------'
'---------------------------'
        ^
        | many
        |  :
        |  1
.---------------.
| QuestionTypes |
|  -id (int)    |
|  -type (int)  |
'---------------'
*/

The question type is an int or varchar does not matter. The program that deal with the database will need to identify that.

Any test question that has no test answer is the fill in the blank. It is also dealt in your program.

The weight is the score for each answer (pre-defined). However, you have to deal with fill in the blank answer type in the program.

Just my 2 cents...

[Riteman]

@diafol

OK, so instead of ans, use your ans1, ans2... etc.

Then this is again the way I suggested secondly in my view:

Create a table with ParticipantID, Question1, Score1, Question2, Score2,...... (Too many columns when questions count is around 200)

But @Taywin Suggested me both as inefficient method. ans1, ans2,... again creates 200 columns for 200 answers right?

@Taywin

Thanks for your suggestions. I need only the way to store test_answers. I couldn't understand "-end_date(date) -start_date(date)" what is the reason to include them? I already have the database created(as I mentioned earlier):

1. question list MCQ - questionid, question, choice1, marks1, choice2, Marks2,..., marks4
2. question list 2 (fillup type) - questionid, question, answer, marks

The problem runs around only how I need to structure the table to store answers only.

[Taywin]

start_date & end_date are thrown in simply to enable/disable questions. Don't need to lookat them :P To store answers, you need a type of table which stores participant_id (FK), question_id (FK), answer_id (FK), and answer. The reason you need both answer_id and answer is because you are talking about weighing your answer. The weight (score) is saved in the Answers table for flexibility if you want to change the weight at any time. If you think you have a fixed weight, then the answer_id (FK) could become a float instead for score. The answer field is served for both multiple-choices and fill-in-the-blank. Just my 2 cents...

[diafol]

(Too many columns when questions count is around 200)

No you misunderstood. THe columns are for a single question. I am now totally confused as to what you're trying to do.

[Riteman]

@diafol

Yeah mixed your idea with my situation! Thanks!

Now my table to store the results of the participant is:

ResultID (PK), QuestionID (FK), ParticipantID (FK), Answer, Score

And then retrieved based on search term.

Thanks again!