SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Question and answer tables for a quiz app...

    I'm a bit confused about how I need to store the answers to a multiple question type of exercise coming from an app I'm trying to create.

    I have 2 tables pertaining to this:
    "Question" and "Answer"

    In Question, I have the following general structure:
    id BIGINT(20) NOT_NULL AUTO_INC
    q_question TEXT NOT_NULL

    In Answer, I have the following general structure:
    id (QUESTION ID) BIGINT(20) NOT_NULL
    a_mc_answer TEXT NULL

    I'm unsure about how to store the wrong answers of the multiple choices in the answers table.

    Here's the thing: the user will have the ability to create any number of possible answers to each question they decide to submit. Each question can have any number of possible answers due to its multiple choice nature. Because of this, I'll need a JavaScript-powered form that can populate the page with text inputs the user designates. Each input will correspond to a given answer and with each text input, there will be a respective check box beside it to tick and this will indicate which answer is the correct answer.

    How should I store the incorrect answers, though? I thought that using some sort of string separator, but I'm not sure this would be a good idea...

    Advice?

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what about something like this?

    tblQuestions

    fldQuestId
    fldQuestNo
    fldQuestion
    fldCorrectAnswer

    tblOptions

    fldOptionId
    fldQuestId
    fldOption

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    first of all, you really only need to use INTEGER instead of BIGINT, because if you are ever in danger of having close to two billion questions, other things will have to be redesigned long before you run out of numbers

    to store your answers, you need a simple one-to-many relationship

    CREATE TABLE questions
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , question TEXT NOT NULL
    );

    CREATE TABLE answers
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , question_id INTEGER NOT NULL REFERENCES questions ( id )
    , answer TEXT NOT NULL
    , correct CHAR(1) -- 'Y' or 'N'
    );

    the correct column will be used to indicate which of the multiple choice answers is correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So when would a MySQL database begin to show signs of fatigue? The first 100k or so records or 1 million?

    As far as the 2 billion capacity is concerned, I guess I was just trying to be accommodating. I honestly have no idea what data type to use half the time because it seems so up in the air. It's like with text or varchar... Int vs. tinyint or whatever... There's so many types to select from that half the time I find myself wasting time just trying to decide between them.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Wolf_22 View Post
    So when would a MySQL database begin to show signs of fatigue? The first 100k or so records or 1 million?
    mysql can easily handle hundreds of millions of rows, it'll be your hard drive that craps out first



    regarding which datatype to choose, have you seen the page in the manual which describes the storage requirements for each different type?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I've seen them. I just wish they were a bit more organized and easier to understand.

    ...but nobody said it would be easy, right? I felt like this back before I knew anything about the PHP manual, so I suppose it's just the same thing with the MySQL manual, too.


Bookmarks

Posting Permissions

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