Database Design/ERD - Quiz Engine

Question

I've been using this site as a great resource since I started at school - but I've never had a problem this specific before and I was hoping for a bit of help. I was never the best when it came to figuring out database structures, and I've been tasked with creating a PHP/MySQL test engine with some rather specific specifications.

So you can better understand what I'm going for here - I am trying to take into account the following:

• Administrator and student login are required, and provide different levels of access.
• An administrator should be able to build one or more tests and assign it to one or more students.
• For each question an administrator builds within a test, the administrator should be able to assign a point value.
• A test should be able to present one or more questions.
• Your application should support three basic questions types: true/false, multiple choice and fill in the blank.
• Final results will display an overall score, as well as a student’s response to each question.
• A student should be able to see final results for only test they have access to.
• An administrator should see results for tests from multiple students.
• Students are not allowed not retake the same test.

Mostly, I am trying to deal with the basic structure. I had five tables at the start, I condensed the Question/Answer section into one table - and excuse my poor attempt at switching around the relationships here, because I've had them 20 different ways it feels:

Am I on the right track? Any suggestions?

Answer 1

A good rule of thumb with database design in N-1. For all tables that have relationships, you should have N-1 relationships (where N is the number of tables). Tables with circular references are a no-no. Putting as the security components and just looking at test/questsion/answers, you want a design that has Some basic objects:

1. Admin (or teacher) table
2. Test table
3. Student Table
4. Question Table

You didn't mention it in your question, but if you want each test to be associated with a class that the teacher teaches, you will need that as well. With those four, or five, tables you should be able to create your relationships. Hint: Most of these relationships are many-to-many and, as such, will need an XRef table to resolve this. Post back what you do with this and we can look at what's next.