I am in the process of designing an online examination application.
There will be several hundred different courses (each with their own exam).
Each course could have up to 100 questions.
Every time a user takes the test, the answers will be re-pulled from the database and shuffled into a random order.
The test will be all multiple choice/true false.
Answers will also be shuffled randomly, but only if it is not a true/false or if answers rely on others (for example D. A and B)
X amount of questions will be pulled from the database (semi) randomly. X can be different for each course and therefore this number will need to be stored too. The reason that it is semi randomly is because some exams will have questions based upon objectives. If an exam does have this, then every time the X amount of questions are pulled, there must be at least Y questions from each objective. (For example: A particular test may have a bank of 100 questions. 50 questions will be randomly displayed and shuffled. Of those 50, there are 6 objectives. For each objective 2 questions relating to that objective must be part of the 50 that are semi-randomly selected.)
I need help determining the best database structure to store all of these questions and answers. I want something that will allow the answers to be randomly selected fast.
Currently I was thinking about a table named Questions with the following rows: