db schema qestion

slava_php

I'm developing database schema and here's the task:

Database should accomodate user info (name and address), test they took and answers they selected.
So far, there are 3 tests, each has different length and format. There will be more tests, never the same in length or format!

Here's my schema, but i'm stuck at the end:

table user: contains user info
UserID (pri key)
Name
Address

table exams_taken: contains UserID linked to examName taken
etID
refUserID (foreign key UserID)
ExamName

//here's the question: should i create a table for each exam with name corresponding to ExamName? This doesn't seem to be normalized form.

table exam1:
e1ID
refUserID (foreign key UserID)
answer1
answer2
...
TimeSubmitted

table exam2:
//similar to exam1 to account for different structure.

any help is highly appreciated,

steve

Sxooter

Do NOT make a table for each quiz / exam. You are right that that is not the proper way to do things. Basically, the schema or layout of a database should not need to change over time for most applications, and designing an application around a mutable schema is generally a bad idea.

So far what you have looks pretty good.
What you should probably have is a table of exams that just defines the title / author / other info about the exam, a table of questions, that is joined against that, as well as a table of possible answers. Then you can just join it all up with a query like:

select * from exams e, questions q, answers a where e.id=q.eid and e.id=a.eid and e.id=1234

slava_php

thanks,
what info should be store in the answers table? each test has variable number of answers?
thanks
steve

ultraslacker

I've taken this from my online questionaire - it has extra tables that you probably do not need, as you will not be presenting the full form, i believe. This should give you an idea, but it may be too normalized for your purposes.

table user: contains user info
UserID (pri key)
Name
Address

exam:
exam_id
exam_name

question:
question_id
question_text

answer: (if we are talking about multiple choice)
answer_id
question_id
answer_text
answer_true

(you could also take out question_id from answer and have a many to many relation of question_answer)

exam_question
(the table that ties a question to exam, used to generate the exam form)
exam_question_id
exam_id
question_id
order (order of question)

user_exam
user_exam_id
user_id
exam_id
date_submitted

user_answer
user_exam_id
exam_question_id
answer_id (if multiple choice answer)
answer_text (if free text answer)