IDEAS

[Chris B.]

I am tracking hundreds of students in a school database.
I have a weird request however, I must track each students
answer to each test question. Therefore I have created
the supporting tables, students, courses, and tests. The
test has a sub table to handle the individual questions
since each test can have between 1 to 50 questions. My
original solution works but I can't believe it is the best
way! I created an Events table that has F-Keys to the
student and test tables, and originally 25 fields labeled
Q1,Q2,Q3,....Q25. I know I could just increase this to 50
fields but it seems alot of empty space for the smaller
test. A single course may have upto 10 test.

Thanks

 

[PC Datasheet]

TblFaculty
FacultyID
FName
LName

TblFacultyCourse
FacuktyCourseID
FacultyID
CourseID

TblStudent
StudentID
FName
LName
etc

TblCourse
CourseID
CourseName
etc

TblTest
TestID
CourseID
<Way to Designate for what grade>
etc

TblTestQuestion
TestQuestionID
TestID
Question

TblTestAnswer
TestAnswerID
TestID
TestAnswer

TblTestDate
TestDateID
TestID
TestDate
FacultyID

TblTestTaker
TestTakerID
TestDateID
StudentID
Grade

TblStudentAnswer
StudentAnswerID
TestQuestionID
StudentID
StudentAnswer

(The design of TblStudentAnswer depends on the type of questions on the tests.
The above is for openended questions. The table below is for multiple choice)

TblStudentAnswer
StudentAnswerID
StudentID
TestAnswerID

 

[TC]

You need to read:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC