How to design database for tests in online test application
- by Kien Thanh
I'm building an online test application, the purpose of app is, it can allow teacher create courses, topics of course, and questions (every question has mark), and they can create tests for students and students can do tests online.
To create tests of any courses for students, first teacher need to create a test pattern for that course, test pattern actually is a general test includes the number of questions teacher want it has, then from that test pattern, teacher will generate number of tests corresponding with number of students will take tests of that course, and every test for student will has different number of questions, although the max mark of test in every test are the same. Example if teacher generate tests for two students, the max mark of test will be 20, like this:
Student A take test with 20 questions, student B take test only has 10 questions, it means maybe every question in test of student A only has mark is 1, but questions in student B has mark is 2. So 20 = 10 x 2, sorry for my bad English but I don't know how to explain it better.
I have designed tables for:
- User (include students and teachers account)
- Course
- Topic
- Question
- Answer
But I don't know how to define associations between user and test pattern, test, question. Currently I only can think these:
Test pattern table: name, description, dateStart, dateFinish, numberOfMinutes, maxMarkOfTest
Test table: test_pattern_id
And when user (is Student) take tests, I think i will have one more table:
Result: user_id, test_id, mark
but I can't set up associations among test pattern and test and question. How to define associations?
