Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Structure

    i am developing an application which deals with the collection of multiple choice questions (MCQs).

    the MCQs are stored in an online mysql database, to which a local vb .net client connects and retrieves the questions with all its parameters (possible answers, correct answers, and so on)


    a MCQ table must consist of:
    -ID (int)
    -Question (string)
    -Picture For Question (image optional)
    -Possible Answers (*)
    -Correct Answers (string)
    -Courses (*)
    -Categories (*)
    -Tags (*)
    -Flags (*)
    -Variant (string)
    -Information (string)


    Those entities marked with an (*) asteric are ment to be placed in different tables. at least, i consider that correct, because otherise these things would be repeatetly writen in the DB.

    With my limited knowledge about DB and mysql im missing right now an idea how i can 'connect' the different tables. In a way that it could be writen:
    table MCQ:
    [..]
    -Possible Answers (5)
    -Courses (11, 13, 9)
    -Categories (1, 5)
    -Tags (1, 89)
    -Flags (3)
    [..]


    Where the numbers represent the associated IDs in the tables.


    "Possible Answers", is a table with 6 columns. (ID, A, B, C, D, E)
    "Categories" and the rest, are pre-writen tables in the DB.



    Right now, i really cant think of a way how to 'split' the tables to make it possible to have finally one table "MCQ" which contains all data like given in the example. So that i can just load this table into my .net application and work with tables rows.


    any advices or help would be greatly appreciated. thank you

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    I'll show you one example:
    Code:
    SELECT MCQ.questionID, MCQ.Question, GROUP_CONCAT(PA.answer SEPARATOR '##') AS answers
    FROM MCQ LEFT JOIN PossibleAnswers AS P ON MCQ.questionID = PA.questionID
    WHERE MCQ.questionID = 7
    That would put all the possible answer to question 7 into a SINGLE TEXT FIELD, named "answers" here, and then your VB.NET code could use SPLIT( answers, "##" ) to create an array of all the answers.

    This is not the traditional way to do this, as you know. Traditionally, you'd just get back N records where N is the number of possible answers.

    The problem with that is that you can't then *also* get all the associated categories in the same query. If you do, and you had (say) 4 possible answers and 3 categories, you would get back 12 records! Ugly.

    With MySQL's GROUP_CONCAT [not found in SQL Server, by the by!], you could get do something like this:
    Code:
    SELECT MCQ.questionID, MCQ.Question, 
                GROUP_CONCAT(DISTINCT PA.answer SEPARATOR '##') AS answers,
                GROUP_CONCAT(DISTINCT C.category SEPARATOR '##') AS categories
    FROM MCQ LEFT JOIN PossibleAnswers AS P ON MCQ.questionID = PA.questionID
    FROM MCQ LEFT JOIN Categories AS C ON MCQ.questionID = C.questionID
    WHERE MCQ.questionID = 7
    Notice the DISTINCT usages in there: Without them, you would indeed get 12 answers, 12 categories. But MySQL's GROUP_CONCAT is pretty powerful, allowing the use of DISTINCT and even ORDER BY within the function, so you have a lot of control.

    Not sure this is what you are looking for, but if it is, it's a great MySQL-only solution.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Oh, another nicety of MySQL: Unlike SQL Server, you don't *have* to specify *ALL* the non-aggregate fields in your GROUP BY. If you know that only one field is needed to distinguish the records (typically the ID field), you can get away with using just it.

    So, for example, to get *all* the questions, possible answers, and categories:
    Code:
    SELECT MCQ.questionID, MCQ.Question, 
                GROUP_CONCAT(DISTINCT PA.answer SEPARATOR '##') AS answers,
                GROUP_CONCAT(DISTINCT C.category SEPARATOR '##') AS categories
    FROM MCQ LEFT JOIN PossibleAnswers AS P ON MCQ.questionID = PA.questionID
    FROM MCQ LEFT JOIN Categories AS C ON MCQ.questionID = C.questionID
    GROUP BY MCQ.questionID 
    ORDER BY MCQ.questionID
    Try it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, thanks for ur help..


    i think i understood now how it is possible to join different tables and output it as one. the group_concat seems to work kinda straight forward..

    but i am now struggling in the structure-logic of the tables. Because when i have the possibility to join for example..

    in order to be able to have one table_MCQ:
    -ID
    -PossibleAnswers (all of PAs associated with MCQ.ID)
    -Categories (all Cs associated w/ MCQ.ID)
    -[..]

    doesnt this mean, that

    i have to fill table_Categories like:
    -localID
    -MCQID
    -CategoryName ("medicine")

    and so, i would have to create such an entry for EACH category an MCQ has. this would write unecessary data to the DB.


    is there somebody who can point me in the right direction.... because im totally stuck there...... thanks.

  • #5
    New to the CF scene
    Join Date
    Jul 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PS: i forgot to write that i think i need to use primary and foreign keys to achieve what i want to do somehow. but im still researching about how to use them...

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    i have to fill table_Categories like:
    -localID
    -MCQID
    -CategoryName ("medicine")
    No, it's worse than that. <grin/>

    You should have
    Code:
    CREATE TABLE Categories (
        catid INT AUTO_INCREMENT PRIMARY KEY,
        category VARCHAR(100)
    ) Engine INNODB;
    
    CREATE TABLE mcq_categories (
        catid INT,
        mcqid INT,
        CONSTRAINT FOREIGN KEY (catid) REFERENCES Categories(catid),
        CONSTRAINT FOREIGN KEY (mcqid) REFERENCES MCQ(id)
    ) Engine INNODB;
    That is, Categories is just a lookup table and mcq_categories is a MANY-TO-MANY table. (Look that up on google, and look up "normalization" while you are at it.)

    Meaning that the actual query then becomes:
    Code:
    SELECT MCQ.questionID, MCQ.Question, GROUP_CONCAT(C.category SEPARATOR '##') AS category_names
    FROM MCQ 
    LEFT JOIN mcq_categories AS MC ON MCQ.id = MC.mcqid
    LEFT JOIN Categories AS C ON MC.catid = C.catid
    WHERE MCQ.questionID = 7
    And so on.

    *THAT* is the truly right way to do this.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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