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.