Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2010
    Posts
    24

    Need help with design

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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)/...
    Last edited by loquin; 04-15-10 at 16:17.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by loquin View Post
    Is it ever possible for a question to have multiple correct answers? (Think of checkboxes instead of radio buttons)
    In this case, yes.
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  4. #4
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by loquin View Post
    Is it ever possible for a question to have multiple correct answers? (Think of checkboxes instead of radio buttons)
    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??
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by loquin View Post
    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
    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.

    Quote Originally Posted by loquin View Post
    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.
    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.

    Quote Originally Posted by loquin View Post
    This would make the test presentation data base driven, as well as the responses...
    Elaborate??

    Quote Originally Posted by loquin View Post
    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...
    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.
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I'm still trying to get a handle on your requirements!
    Quote Originally Posted by DevilsAdvocate
    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
    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.

    Quote Originally Posted by DevilsAdvocate
    Quote Originally Posted by loquin
    This would make the test presentation data base driven, as well as the responses...
    Elaborate??
    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.
    Quote Originally Posted by DevilsAdvocate
    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?
    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.
    Last edited by loquin; 04-16-10 at 15:19.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by loquin View Post
    I'm still trying to get a handle on your requirements!
    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.

    Quote Originally Posted by loquin View Post
    Will the database EVER be used for different tests?
    No. Same test for everyone, cheating is not an issue.

    Quote Originally Posted by loquin View Post
    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.
    Most likely, although it might be a once in a lifetime test, undecided.

    Quote Originally Posted by loquin View Post
    do you want to support a testing program. ... 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.
    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?



    Quote Originally Posted by loquin View Post
    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.
    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.

    Quote Originally Posted by loquin View Post
    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
      • the set of questions
      • the set of answers for each question
      • a boolean flag for each answer to specify if the answer is correct or not

      .
    • 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
      .
    • The Responses would be the user's selection of one or more answer(s) for each test question.
    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
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  9. #9
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by DevilsAdvocate View Post
    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
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  10. #10
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by DevilsAdvocate View Post
    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
    Last edited by DevilsAdvocate; 04-16-10 at 17:40. Reason: typos---lots of them :)
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  11. #11
    Join Date
    Apr 2010
    Posts
    24
    Ok, here's what I took from this thread. Am I on the right track? I'd appreciate your comments, suggestions.
    Attached Thumbnails Attached Thumbnails ERD4.jpg  
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •