I'm trying to design a database that will support a dynamic questionnaire application. What I mean is the questions most likely will change from time to time and have the edited question pre-populate when a new questionnaire is triggered.
I want a database that will have a table to store the questions with expected results and the ability to pull the current question in the table and use them for a questionnaire. The questions and sub-questions are categorized using the following structure: Family, Control, Metric
For each questionnaire, I need to store the name, address, date etc and the answers to each question so that it can be retrieved.
Unfortunately, I need help..
Here is what I have so far. Can someone help me construct this into a meaningful solution?
You can design your database different ways.Most of your requirements are not clear to me,
I am giving some directions to design Unless I am incorrect as to the requirement
My understanding facility means test center location details
T1 (Facility Details)
T2 ( Exam/Test case details,not for a exam specific questions)
test_case_id, test_case_type, test_case_family etc( which are common to Test/Exam ,not pertaining each question)
T3( Specific to each question and mapping to Exam id)
Is it multiple choice questions? If yes then added choice as a column
test_case_id, Question_id ,question, choice1,choice2,choice3,choice4,test_case_expectedResults
I think you need user details also
t4 (user details)
Storing the test results
From the application how the test results are evaluated,User provided answers are those stored in the user session and at end of the exam are you validating with actual result
Or depending upon your requirements if user can take/continue the same test multiple times login/log out
if you are using session and you are storing results for audit purpose
Then store all the answers as a single string and even you can use this for other scenario but application has to do some logic to get the results.
Disadvantage with this if question sequence changes then there will be mismatch
T5 :Unique_id,User id,test id,date,facility id ,answerstring,actualscore ,comments etc
If you don’t want to store as a string
T5 :Unique_id,User id,test case id,date,facility id , actualscore
T6 :Unique id,questionid,answer
Joined: Mar 11, 2011
Thank you.. Sorry I wasn't as clear as I should have been. Yes, you're correct with the location (facility).
I'm going to review and apply today and will update soon.
Thanks for your help.