Hey, this is my first post here so pardon any faux pas.

I am working on designing a db for a program that tests a user and then generates a report with the results. Here's my issue: there are bound to be multiple incorrect answers and I can't figure out the best way to go about storing them. I have two tentative solutions and I was wondering if I could have you kind folks help me out. I am not a db guy, although I can stumble pretty convincingly, so I'm turning to more knowledgable sources.

Here's ex. 1: This one stores it in the report table itself
Questions (Ques_ID, Ques_Text)
Answers (Ans_ID, Ans_Text, Ques_ID)
Report (Report_ID, User_ID, Date, Result, Ques_ID, Ans_ID)

Here's ex. 2: This one has its own table for incorrect answers
Questions (Ques_ID, Ques_Text)
Answers (Ans_ID, Ans_Text, Ques_ID)
Incorrect (Inc_ID, Ques_ID, Ans_ID)
Report (Report_ID, User_ID, Date, Result, Inc_ID)

Which is the better way? I've seen ways to store multiple values in a single field as in ex.1, but everything I've learned says "bad". Would you suggest a different approach?
I hope I've asked the right questions and gave the necessary info.

Thanks for reading

Is it ever possible for a question to have multiple correct answers? (Think of checkboxes instead of radio buttons)

You want to store the question and all potential answers for the question. Where do you specify which answer is the correct answer?

Do you care what order the answers are presented? Should they be presented in random order, or in a specific order?

Will more than one person take the test? How do you store user info if so?

Do you need to provide the capability for more than one report type?

Do you need to provide the capability for more than one test?

First, take a step back, and make sure you've define all the entities involved. Questions/Answers/Results/Report(s)/User(s)/...

In this case, yes.

Ok, I read over you're post again, and I think you misunderstood, or maybe I did.

What I need to do is store every answer that a user gets wrong so I can display a summary at the end. So the multiple wrong answers come from multiple questions, and I want to store them in one place to access them later.

Is that a little clearer??

I was thinking in terms of a multiple choice test scenario. Question 1 has 5 possible answers, of which, 1 or more may be correct. Question 2 ditto. Question N the same.

And that you wish to store the test questions and answers (along with which answer(s) are correct, (so you could re-use the test next year) in addition to the user responses.

This would make the test presentation data base driven, as well as the responses...

If all you store is the incorrect responses, how can you score the results when it's possible to have multiple correct responses? The system would need to have knowledge of the test questions and choices beyond what is stored in the database...

I looked the questions over, and most are multiple choice, with one correct answer and in rare cases two, but I'm lobbying against two. Some are also true/false.

Not quite sure what you meant, but the test is only able to be taken once per employee as of yet, and I suppose a yearly retry is probable.

Elaborate??

Couldn't I just compare the number wrong to the number of questions in the db to get a numeric result?
The answer table has a boolean value field to record if answer 1a is correct, 1b is false, etc. Is that what you mean?


Thanks for your responses, I appreciate the input. It's making me think about things I probably wouldn't otherwise.

I'm still trying to get a handle on your requirements!
Will the database EVER be used for different tests? In many places, if a test needs to be repeated, a series of tests are used; the test from year 1 is different from year 2 and so on. After a few years, the test cycles back around to the first one again. The test rotation helps to avoid cheating. Does the data need to be retained? If so, for how long? If a yearly re-test is probable, and you need to retain the records for more than one year, then you will want to add a Test ID field, so you can keep the tests from 2010 separate from those from 2011, separate from those from 2012, and so on. The test id field would be included in the primary key. However, if the test data will be flushed after a year, then you could delete the records before you fill it again the next year.

What I was talking about was, do you want to support a testing program, or is this table going to be used to simply record the results of a paper test? If, as you have stated above, you want to have an computer-based application which presents the test to the user, and records their responses, your database would need to model the source data that exists on a paper test. Test identification and date, the test-taker's name, the test questions text, the test answers text, the ordinal position of each, as well as the answer(s) chosen by the test-taker. If you only want to store the results, but if you need to store what the questions and answers were, you would still need a data structure to support this data. Potentially, you could keep keep a paper copy of a blank test forever, as a reference, but, as this makes the data in the database dependent on an external metadata source, I wouldn't recommend it. You should be able to recreate the test and the results from the contents of the database alone. Note that in a testing situation, there is a class of data that is associated with the test as a whole, there is a class of data associated with each individual question, there is a class of data associated with each test-taker, and there is a class of data associated with the test-taker's answers.
It depends on how you are grading - i.e. - do you allow partial credit if, in the case of multiple 'correct' answers, the user only selects one of them. By not selecting both, does he get full credit, partial credit, or no credit? If any question will ever have multiple 'correct' answers, or could have multiple 'partial' answers, you need to be able to model this in your design.

There also seems to be a problem in communication/terms. I would suggest that we both try to be more specific in this area. I propose that, in this thread:

• The Test consists of (at a minimum)
  • test identification data - test id, test title, test revision
  • the set of questions for the specific test
  • the set of answers for each question
  • a boolean flag for each answer, to specify if the answer is a correct answer
  .
• The User would be the test taker.
  .
• The Testing Data would include only data specific to the test (as a whole) taken by the user. Including
  • The test ID
  • The testing date
  • The User who took the test
  .
• The Responses would be the user's selection of one or more answer(s) for each test question.

Note that the testing data and responses don't need to include data from the Test, (other than the test ID.) You can get the test data with a join.

I know and I appreciate the effort. I"m trying to give the best description I can without giving away too much and breaking my NDA.

No. Same test for everyone, cheating is not an issue.

Most likely, although it might be a once in a lifetime test, undecided.

Yes, more application driven. Basically just any other test. It will store and display a summary at the end, and they can login and see it at any time. I have User, Questions, Answers, Report and a few supporting tables, such as a Quadrant table that groups the questions, etc. Are there others you would suggest?



No, if it has multiple possible correct answers, any is considered correct. There aren't alot of those, and I'm trying to convince the higher-ups to restructure those.

I'll do my best

I hadn't considered a Test entity, but it makes alot of sense. I was just pulling all that info together in the Report table (although with deeper consideration, it might be the same table just a different name, it might not). This is what I needed help with. DB's are admittedly not my specialty.

I had it set up to hold questions, hold answers, hold user info, and display a report at the end. A test entity would allow me to store all the incorrect answers from the test, which is my dilema (sp?) at the present time. So your saying add a Test entity to store them and then link that to the Report table to pull in the users test results to be displayed on the summary? Or do away with the Report entity altogether and just do it in Test?

Again, I appreciate the help and I'll do my best to give as much info as I can

I was thinking about it, and I'm unclear as to why I would need a Test table. I don't really need to store anything about an individual test, I don't think. They'll all have the same questions and will just be generated from the db over and over. I need to store each users responses so I can summarize them to the user at the end in a report, so any comparisons could just be done there couldn't it?

Maybe I'm missing something.

Thanks for your patience

Ok, I'm wrong. I checked with the big-wig and he said it will be repeatable so I do need the Test Entity. Disregard the quoted post

Ok, here's what I took from this thread. Am I on the right track? I'd appreciate your comments, suggestions.