Im doing a multiple choice online test project. It needs to be well structured. At the moment, Im in the very early stages of the project life time. I have designed a database structure but unsure if it is a solid desgin.
Requirements:
Single exam can consist of multiple sections.
Allow for different language versions of single exam.
Allow for unlimited number of answers.
Allow for specifc ordering of answers - eg, to allow for "1,2,3, all of the above"
Ensure that correct answer cannot be identified by viewing source (eg lowest ID is correct)
Maintain details of all users' attempts.
tbl_Tests: remove "NumberOfQuestions". This will be calculated at query time by counting the number of questions assigned to the test through the other tables. Otherwise you'll need to employ triggers on those other tables to keep this number up to date
as questions are added/deleted (and don't assume they won't be).
I would remove the "correct" column from tbl_Answers, and instead move the "Score" column out of tbl_UserAnswers and into tbl_Answers. The score will be zero for incorrect answers and whatever value you place on the question for the correct answer.
You might want to include LocalLangID in tbl_Answers, even though you can get it from tbl_Questions, there might be some instances where you can eliminate a JOIN just to get it.
I would put an identity column in tbl_TestUserAttempts and use that as a FK in tbl_UserAnswers instead of the Attempt column. Otherwise how do you know which test an answer applies to? It could be the same question in the same attempt on 2 different
tests.
tbl_Tests: remove "NumberOfQuestions". This will be calculated at query time by counting the number of questions assigned to the test through the other tables. Otherwise you'll need to employ triggers on those other tables to keep this number up to date
as questions are added/deleted (and don't assume they won't be).
I would remove the "correct" column from tbl_Answers, and instead move the "Score" column out of tbl_UserAnswers and into tbl_Answers. The score will be zero for incorrect answers and whatever value you place on the question for the correct answer.
You might want to include LocalLangID in tbl_Answers, even though you can get it from tbl_Questions, there might be some instances where you can eliminate a JOIN just to get it.
I would put an identity column in tbl_TestUserAttempts and use that as a FK in tbl_UserAnswers instead of the Attempt column. Otherwise how do you know which test an answer applies to? It could be the same question in the same attempt on 2 different
tests.
Thanks for your replay.
I agree with you in most of your points. However, NumberOfQuestions will store how many questions will be displayed for a test. For example, we can have test "a" and NumberOfQuestions of lets say "40". This is not the number of questions we are going to
store for each test, we can have as many as we want but will randomly pull only 40 to display.
To make ecen clearer, we will have x number of sections for each test - typically 4. each section consists of 10 questions that will be pulled out randomly, that's why I have TestSectionID in tbl_Questions as a question has to belong to a section for a test.
Ok, if that is the purpose of NumberOfQuestions, then you should keep it.
A couple of other things I've just noticed:
I don't see any need for the Accesses column in tbl_TestUserAccess. You will be able to calculate how many times a user accesses a test by counting the attempts.
You will probably want some kind of datetime stamp in tbl_TestUserAttempts, and you'll need to make the IDENTITY column I suggested the PK, since if a user can take a test multiple times, then TestID/UserID won't be unique.
-Tab Alleman
Marked as answer by Chen Yu - MSFT on Dec 28, 2011 09:28 AM
ModifiedDate is just a DateTime field to store when a test or question or answer was created, or possibly updated.
Table Tests holds informations about all tests within the system (there are many tests and each on consists of multiple sections). If you have only one test you probably dont need table Tests.
I've got the full design as visio document, I dont know how to upload it in here
Your Question and Posts on it were so much useful for me.
ziad18
I've got the full design as visio document, I dont know how to upload it in here
Regarding Visio Design, I may suggest you to take a Screenshot of it and post the image here, else you can mail it to me at
er.himalayagarg@gmail.com as one more favour.
Thanks for your helpful post. It is very nice. I have similar request like Himalaya.garg for your last design as I have to deliver a project like yours in a very tight time.
I'm wondering if it's possible, please send your last design to me here or by email. I greatly appriciate your help.
ziad08
Member
2 Points
2 Posts
Database design for online test
Dec 21, 2011 11:07 AM|LINK
Hi all,
Im doing a multiple choice online test project. It needs to be well structured. At the moment, Im in the very early stages of the project life time. I have designed a database structure but unsure if it is a solid desgin.
Requirements:
Single exam can consist of multiple sections.
Allow for different language versions of single exam.
Allow for unlimited number of answers.
Allow for specifc ordering of answers - eg, to allow for "1,2,3, all of the above"
Ensure that correct answer cannot be identified by viewing source (eg lowest ID is correct)
Maintain details of all users' attempts.
My initial design:
Tables:
tbl_Tests
TestID PK
PassMark
NumberOfQuestions
ModifiedDate
tbl_TestDescriptions
TestID PK
LocalLangID PK
Description
tbl_Sections
SectionID PK
LocalLangID FK
SectionDescription
tbl_TestSections
TestSectionID PK
TestID FK from tbl_Tests
SectionID
tbl_TestSectionDescriptions
TestSectionID PK
LocalLangID PK
SectionDescription
tbl_Questions
QuestionID PK
TestSectionID FK from tbl_TestSections
LocalLangID FK
Question
ModifiedDate
tbl_Answers
AnswerID PK
QuestionID FK from tbl_Questions
Answer
Correct
ModifiedDate
tbl_TestUserAccess
TestID PK
UserID PK
Accesses
tbl_TestUserAttempts
TestID PK
UserID PK
Attempt
LocalLangID
tbl_UserAnswers
UserID PK
QuestionID PK
AnswerID PK
Attempt
Score
Note: tables tbl_Users (stores users with their UserID) and tbl_Locales (stores locale languages with their LocalLangID) already exist.
TabAlleman
All-Star
15557 Points
2698 Posts
Re: Database design for online test
Dec 21, 2011 01:49 PM|LINK
I would change the following:
tbl_Tests: remove "NumberOfQuestions". This will be calculated at query time by counting the number of questions assigned to the test through the other tables. Otherwise you'll need to employ triggers on those other tables to keep this number up to date as questions are added/deleted (and don't assume they won't be).
I would remove the "correct" column from tbl_Answers, and instead move the "Score" column out of tbl_UserAnswers and into tbl_Answers. The score will be zero for incorrect answers and whatever value you place on the question for the correct answer.
You might want to include LocalLangID in tbl_Answers, even though you can get it from tbl_Questions, there might be some instances where you can eliminate a JOIN just to get it.
I would put an identity column in tbl_TestUserAttempts and use that as a FK in tbl_UserAnswers instead of the Attempt column. Otherwise how do you know which test an answer applies to? It could be the same question in the same attempt on 2 different tests.
ziad08
Member
2 Points
2 Posts
Re: Database design for online test
Dec 21, 2011 02:16 PM|LINK
Thanks for your replay.
I agree with you in most of your points. However, NumberOfQuestions will store how many questions will be displayed for a test. For example, we can have test "a" and NumberOfQuestions of lets say "40". This is not the number of questions we are going to store for each test, we can have as many as we want but will randomly pull only 40 to display.
To make ecen clearer, we will have x number of sections for each test - typically 4. each section consists of 10 questions that will be pulled out randomly, that's why I have TestSectionID in tbl_Questions as a question has to belong to a section for a test.
Thanks
TabAlleman
All-Star
15557 Points
2698 Posts
Re: Database design for online test
Dec 21, 2011 02:58 PM|LINK
Ok, if that is the purpose of NumberOfQuestions, then you should keep it.
A couple of other things I've just noticed:
I don't see any need for the Accesses column in tbl_TestUserAccess. You will be able to calculate how many times a user accesses a test by counting the attempts.
You will probably want some kind of datetime stamp in tbl_TestUserAttempts, and you'll need to make the IDENTITY column I suggested the PK, since if a user can take a test multiple times, then TestID/UserID won't be unique.
himalaya.gar...
Member
138 Points
96 Posts
Re: Database design for online test
Apr 30, 2012 07:56 AM|LINK
Hi,
Sorry for disturbing, your project might have been already completed. But I need your help in designing the similar DataBase for my new Project.
-I could not understand what is the purpose of using 'ModifiedDate'?
- Also what is the purpose of using Test table. Can not we use only Section table? Is it for the user can give number of tests?
Can you give me your last design, if possible.
Please help-
ziad18
Member
6 Points
4 Posts
Re: Database design for online test
Apr 30, 2012 02:59 PM|LINK
Hi there,
ModifiedDate is just a DateTime field to store when a test or question or answer was created, or possibly updated.
Table Tests holds informations about all tests within the system (there are many tests and each on consists of multiple sections). If you have only one test you probably dont need table Tests.
I've got the full design as visio document, I dont know how to upload it in here
himalaya.gar...
Member
138 Points
96 Posts
Re: Database design for online test
May 01, 2012 10:46 AM|LINK
Hello Ziad,
Very thanks. Now I am clear about the design.
Your Question and Posts on it were so much useful for me.
Regarding Visio Design, I may suggest you to take a Screenshot of it and post the image here, else you can mail it to me at er.himalayagarg@gmail.com as one more favour.
Thanks
mh_hatami
Member
9 Points
12 Posts
Re: Database design for online test
May 15, 2012 02:22 AM|LINK
Hello Ziad,
Thanks for your helpful post. It is very nice. I have similar request like Himalaya.garg for your last design as I have to deliver a project like yours in a very tight time.
I'm wondering if it's possible, please send your last design to me here or by email. I greatly appriciate your help.
Thanks Hatami
mh_hatami@hotmail.com
himalaya.gar...
Member
138 Points
96 Posts
Re: Database design for online test
May 15, 2012 06:26 AM|LINK
Hi...!!!
Below is link to my Design for the database. Slightly different from Ziad. But his design was very useful for me.
http://imageshack.us/photo/my-images/40/examonline.png/
Hope it helps
mh_hatami
Member
9 Points
12 Posts
Re: Database design for online test
May 15, 2012 07:19 AM|LINK
Hi himalaya,
Thanks. looks very helpful.