Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Quiz database design (with categories)

    I am trying to come up with a design for a simple quiz database.

    My goal would be to type a simple query for the database to generate 20 (or 25 or 30 or 47 ....) random questions from my selected categories.

    Code:
    <pseudo code>
    SELECT from 'questions' LIMIT to 20  (20 random questions)
    SELECT 15 questions and 1 'history' question and 1 'geography' question and 1 'music' question and 1 'astrophysics' question (15 random and 4 fixed)
    SELECT 5 'music' questions and 5 'language' questions and 5 'computer' questions and 5 'art-history' questions (20 random questions in 4 different categories)
    </pseudo code>
    One question could have several categories attached to it:
    "What was Elvis Presley's middle name?" ('Elvis', 'USA', 'Middle Names', '20th century', 'Famous Performers')
    "Name the 8 planets" ('Science', 'Astronomy', 'Solar System', 'Planets', 'Milky Way')

    And I would also preferably like a user system with "last_used" attached to any question to avoid presenting the same question to the same user too often.

    I have startet with a rough idea. The categories_link_table is to get several categories to one question... but something is wrong, I know...

    Click image for larger version. 

Name:	2016-06-27_210431.png 
Views:	2 
Size:	11.0 KB 
ID:	16962

  2. #2
    Join Date
    May 2016
    Posts
    81
    Hi anno1095

    Below the conceptual Data model (CDM)
    Click image for larger version. 

Name:	mcd2.jpg 
Views:	10 
Size:	84.4 KB 
ID:	16969

    Then the Logical Data model (LDM)
    Click image for larger version. 

Name:	mpd2.jpg 
Views:	7 
Size:	101.0 KB 
ID:	16970

    About fixed categories , two solutions

    1. Modify categorize relation
      1. cardinalities (0,n) - (1,5) considering the hypothesis below, no fixed category is the right category
      2. Add status right to define which one is the right category

    2. In the code but I don't recommend this solution
    Last edited by informer; 06-27-16 at 21:32.

Tags for this Thread

Posting Permissions

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