SQL suggestion???

[Abhilash Etikala]

Hi everybody,
Iam doing my project which is just like an online examination where many users write their exam and if they qualify they have to write another exam.so,my doubt is how should i maintain the tables for every round??every thing the answers by different users should be stored.I need to store the answers made for different questions of each user for each round...any suggestions..

[rski]

Each round means each exam? or one exam is splitted into rounds?

For the start you would define tables
USER (informations about users)
EXAM (informations about exams, also exams path i mean which exam user must past to take another)
QUESTIONS (questions that was asked, connected with exams and users)
ANSWERS (answers for questions connected with exams, users and questions)

How many data you think there would be in the tables?

What do you think about that solution? Did I miss something.
Treat it as a sketch.

Sorry for not so good english, i think everything i wrote is clear.

[Abhilash Etikala]

Thanks rski...
wat ever the answers given by users should be stored.So my problem is i took a table USER_ANSW which stores all the answers of each questions of each round.of each user....but the users record gets repeated in USER_ANSW table...for every round.so shall i use the same table to store with repeated users or any other suggestion...

[rski]

I think that USER_ANSW should be a relation, that keeps only ids (which are primary keys)
USER_ANSW(user_id,answer_id,question_id,exam_id)

You don't write if exam is a test (open questions exam or choice/multichoice exam)? If it is an open question test(i mean users writes answers not choice them from a list) then you probably do not need ANSWERS table and USER_ANSW will look like that (no answer_id but real answer)
USER_ANSW(user_id,answer,question_id,exam_id)

[Abhilash Etikala]

oh thank you sir,
the idea was gud if i make USER_ANSW as a relation then i guess i should use PRIMARY KEYS in that table but not UNIQUE constraint rite???
and how to take a table for answers which relates to USER_ANSW table which stores only an option value for different questions for each user(previously i tried with arrays)..is it better to use arrays to store answers options for each user...???

[rski]

Primary key should be
user_id in table users
exam_id in table exams
question_id in table questions
answer_id in table answers

Table user_answ do not need primary key, but you can add one let say
uans_id.

table which stores only an option value for different questions for each user(previously i tried with arrays)

What do you mean for each user, i thought the answer table be like that

ANSWER(answer_id,question_id,answer_text)

so for one question there would be few rows with posible answers (related with questions via question_id foreign key)
Do you want to store here which user was asked that question? What for?
You can read that information from user_answ table.
Or I simply do not understand what do you want do achieve?

[Abhilash Etikala]

Thank You rski....i got it...

[quote]table which stores only an option value for different questions for each user(previously i tried with arrays/QUOTE]

what does i mean from above is that previosly my table for storing answers was:
answers(user_id,exam_id,answ array[])

where i used for every exam i stored answers(only options,one option per question) in array for each user and the userid's would repeat if he attempts more than one exam. which made me problem in retrieving.the data...

but now the problem is solved sir,

i think you got it wat does i mean....

Thank you much for valuable suggestion...