This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes OO, Patterns, UML and Refactoring and the fly likes Need help for small db to support swing app Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Engineering » OO, Patterns, UML and Refactoring
Bookmark "Need help for small db to support swing app" Watch "Need help for small db to support swing app" New topic
Author

Need help for small db to support swing app

Ron Ingram
Ranch Hand

Joined: Mar 11, 2011
Posts: 63
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?

t1: facilty
fac_id, name, phone, address

t2: test_cases
test_case_id, test_case_type, test_case_family, test_case_cntrl, test_case_metric, test_case_question, test_case_expectedResults

t3: tests
test_id, test_date, test_poc, test_control, test_actual_results, test_comments, test_artifact, fk_fac_id, fk_test_case_id

I was going to try and have foreign keys map back to the questions for retrieval purposes.. just not sure how to do all of this as I have limited database experience.

Thanks for your time
Murali Ranga
Ranch Hand

Joined: Dec 16, 2011
Posts: 38
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)
userid,name,password etc
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
Ron Ingram
Ranch Hand

Joined: Mar 11, 2011
Posts: 63
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.

 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Need help for small db to support swing app
 
Similar Threads
best solution to questionnaire web app
DAO Design Question
Help with saving buttons?
Question Randomization in Access DB
CMR Relations one-to-many