Search code examples
djangodatabasedjango-modelsdatabase-designdatabase-schema

Questions with multiple choices database schema design

Let's say we want to create a quiz application, where a user can create a quiz with the following characteristics:

  • Users can select questions for their quiz from a set of predefined questions.
  • Each question can have multiple options (choices); 2, 3, 5, 6, 9, it doesn't matter.
  • The questions and it's choices are added by the admins (through Django Admin API)

I have come up with a fairly simple schema design:

enter image description here

This is what's happening in the schema design above:

  • Each Question can have multiple choices
  • The QA maps the question to the answer selected by the user who did/took the quiz
  • Each submitted quiz can have multiple questions together with their answers

One of the downsides of this approach is that I can map questions to the answers which do not belong in the choices list of that particular question. For example, I create two questions;

  1. Are you Married?
  2. What is your favorite drink?

For the first question, I have the following options:

  1. Yes
  2. No

And for the second question, I have the following choices:

  1. Water
  2. Soda
  3. Fresh Juice

From the Django Admin API, I can map the first question; Are you married? to the choice of the second option; soda

How can I avoid this? And what other downsides do you see with this approach? Is there a better way to design this kind of schema???

I am using Django for this particular project.

EDIT: As you can see in the image below 👇, all of the choices are appearing, even the ones that do not belong to the question.

enter image description here

Solution

  • There are different design options, but your schema fits its purpose well (assuming you want to have flexibility regarding the whole question/answer set).

    I recommend using a ModelMultipleChoiceField in Django for the choice field in your QA model. It would contain a queryset containing the related choices (which you obtain by querying all choices related to the question in the Question model). That way you restrict the choices to those related to the question.

    If the question/answer set is fixed and only chosen questions/answers change, then there might be better options (for example, you could use Django's choices method to define the choices for a question, instead of putting them in a separate model).