Number of Tables for Test
I'm working on my second database and I have the basic framework set up. Before I get into the nitty-gritty, I'd like to make sure I have a good foundation.
The intention of this database is to allow easier analysis of tests taken by students. Specfically, I would like to assign values to their responses. I've only done a questionairre database, so I don't know yet how to attach a value (0/2 points, 1/2 points, 2/2 points, for example) to their answers. Their end grade is less important than their specific answers, as the answers are used to detect common traps that students fall into when using logic to solve a problem.
The database has these tables so far:
- Student Information (First Name, Last Name, ID#)
- Question Bank (QID#)
- Student Responses (Responses)
- Answer / Value Bank (has the answer prompts and the values/scores for each answer)
- Student Info <--ID-->Student Responses
- Student Responses <--QID--> Question Bank
- Question Bank <--QID--> Answer / Value Bank
I guess where I'm a bit confused is whether or not I need separate tables for the Responses (i.e. the student picked "A"), the identifier for that specific choice (i.e. "A" for Q1 is 45), and their value (i.e. choice "A" on Q1 gets 3/5 points). I haven't yet gotten the feel for knowing when you need to add separate tables or not.
While the identifier is not totally critical, I'd like to be able to see why their answer is wrong, not just whether or not it was wrong.
Thanks in advance for any guidance anyone can provide,