Need opinions on a database design

[mikjall77]

I am in the early stages of writing a program in VB 2005 that deals with taking a test. I need to create an ERD for the project that deals with taking tests and storing the results. All users, admins, instructors, tests, questions, answers, and results will be stored in a database (either SQL Server or MS Access). The "business rules" for this venture are as follows:

• Tests will be multiple-choice, true/false format (a mixture of both).
• The user will enter their ID and password before taking the test (the student ID and password should be authenticated against values stored in a database).
• The test will display a series of at least ten (10) questions.
• There can be from two (True/False) to four (A, B, C & D) answers per question.
• When the test is complete, the answers should be stored in a table with the users ID and the time and date they took the test.
• The questions and answers are stored in a table(s).
• At least three tests will be available that the student may take.
• Students may take a test only once.
• Admins and instructors may also go into the database and change questions or maintain the database.
• Results include the answer to each question for each student for each test, student ID and timestamp.

My main confusion is how to set up the tables with all of the answers and incorrect answers (especially having either multiple-choice or true/false). I figure I will have 10 correct answers per test (at a minimum of 3 tests) and 20 - 30 incorrect answers per test. I will then have to figure out how to store the incorrect questions with the correct results (and the overall results) in the database also.

Any opinions on how I can set this up would be really helpful and appreciated!

[hooray]

Ok here is how i would design the database for this, it is in the format
TABLENAME(primary_key**, foreign_key *, other field, other field)//notes
(each table has start_date and end_date at the end but i didnt put them in)

USERS(user_ID**) // just so that all the people are connected
STUDENTS(student_ID**, user_ID *, name, user_name, password)
TUTORS(tutor_ID**, user_ID *, name, user_name, password)
EXAM(exam_ID**, tutor_ID *, exam_name) //tutor_ID to keep track of who wrote the exam
QUESTIONS/ANSWERS(qa_ID**, exam_ID *, question_number, question, answer) //this
//keeps track of the question and answer for all exams.
RESULTS(result_ID**, student_ID *, exam_ID *, question_number, answer)//this keeps track of what answers each student gives.

This structure will allow all of the things you requested so long as you can deal with nested SELECT statements, if you need help with those just ask. I figure you can put 1/0 for true/false and just a/b/c/d for the other values in the answers section.

I have also included an attatched jpg of a quick sketch ERD from paint just so you can understand a bit better the structure.

[mikjall77]

Hooray,

Thank you for the information, it was definitely helpful. I was leaning towards the way you suggested but started thinking too much on the question/answer table and got overwhelmed.

Thank you for the offer on the nested SELECT statement. I am familiar with those but just need to learn to translate the SQL statements into my VB code.

Thanks again!