Database Design Inquiry

Question

I'm making a trivia webapp that will feature both standalone questions, and 5+ question quizzes. I'm looking for suggestions for designing this model.

Should a quiz and its questions be stored in separate tables/objects, with a key to tie them together, or am I better off creating the quiz as a standalone entity, with lists stored for each of a question's characteristics? Or perhaps someone has another idea...

Thank you in advance. It would probably help to say that I am using Google App Engine, which typically frowns upon relational db models, but I'm willing to go my own route if it makes sense.

Answer 1

It's hard to say without more information, but having the following relations would be sensible, based on what you've said:

Quiz (id, title)
Question (id, question, answer)
QuizQuestion (quiz_id, question_id)

That way questions can appear in multiple quizzes.

Answer 2

Have a table of questions, a table of quizzes and a mapping table between them. That will give you the most flexibility. This is simple enough that you wouldn't even necessarily need a whole relational database management system. I think people tend to forget that relations are pretty simple mathematical/logical concepts. An RDBMS just handles a lot of the messy book keeping for you.

Answer 3

My first cut (I assumed the questions were multiple choice):

• I'd have a table of Questions, with ID_Question as the PK, the question text, and a category (if you want).
• I'd have a table of Answers, with ID_Answer as the PK, QuestionID as a FK back to the Questions table, the answer text, and a flag as to whether it's the correct answer or not.
• I'd have a table of Quizzes, with ID_Quiz as the PK, and a description of the quiz, and a category (if you want).
• I'd have a table of QuizQuestions, with ID_QuizQuestion as the PK, QuizID as a FK back to the Quizzes table, and QuestionID as a FK back to the Questions table.

This model lets you:

• Use questions standalone or in quizzes
• Lets you have as many or few questions in a quiz as you want
• Lets you have as many of few choices for questions as you want (or even multiple correct answers)
• Use questions in several different quizzes

Answer 4

I recently completed an App Engine app for taking personality quizzes.

I would say go the super simple route and store everything about each quiz in a single Quiz entity. If you don't need to reuse questions between quizzes, don't need to search or in any other way access the structure of a quiz besides taking the quiz, you could simply do:

class Quiz(db.Model):
    data = db.TextProperty(default=None)

Then data can be a JSON structure like:

data = {
    "title" : "Capitals quiz",
    "questions" : [
        {
            "text" : "What is the capital of Finland?"
            "options" : ["Stockholm, Helsinki, London"],
            "correct" : 1
        }
        ...
    ]
}

Things for which you want indexes you will want to leave out of this data structure. For example in my app I found I need to leave ID of the quiz creator outside the data so that I can make a data store query for all quizzes created by a certain person. Also I have creation date outside of the data, so that I can query for latest quizzes.

created = db.DateTimeProperty(auto_now_add=True)

You might have some other fields like this. Like I said this is a very simple way to store quizzes without needing to have multiple data store entities or queries for a quiz. However it has worked well in practice in my own personality tests app and is an increasingly popular way of storing data.

Answer 5

Here's a design that will account for having a many-to-many relationship between Quizess and Questions. In other words, a Quiz can have many questions, and a Question can belong to many Quizzes. There is only one copy of a Question, so changes can be made to it that will then be reflected in each Quiz to which the Question belongs.

class Quiz(db.Model):
    # Data specific to your Quiz: number, name, times shown, etc
    questions = db.ListProperty(db.Key)

class Questions(db.Model):
    question = db.StringProperty()
    choices = db.StringListProperty() # List of possible anwsers
    correct = db.IntegerpProperty() # index of string in choices that is correct

The questions property of a Quiz entity holds the Key of each Question entity which is assigned to the Quiz. Look up by key is speedy, and it allows any one Question to be assigned to any number of individual Quizzes.