3

I'm currently building an 'online examination' type of web app using Ruby on Rails and PostgreSQL (or MySQL), which is going to be part of a bigger application once pushed to production. Since I just recently got into RD design I'd appreciate some feedback on the following design.

App Specifications

  • There are 1000+ different questions. Each question can have 2-5 different options, only 1 option is correct.
  • The user is presented with 10 questions at a time.
  • If a user answers a question correctly 2 times, the question will not be shown to him again (let's call those questions now completed).
  • If a user answers a question incorrectly then he needs to answer the question 2 MORE times correctly.
  • The user passes the exam when there are no more questions left to show.

My current database schema is:

user
-user_id
-name etc

question
-question_id
-text
-correct_id (or option_id, stores the correct option id)

option
-option_id
-text
-question_id

user_answer
-user_id
-question_id
-is_correct

So in order to create a '10 question test' we could:

  1. create a list of every completed question querying the user_answer table

  2. create a list of possible questions (all questions - completed questions)

  3. finally serve the user with 10 random questions from the last list.

Is there a better approach to represent "the remaining times a question needs to be answered" inside the database? Please feel free to suggest even a completely different design.

What concerns me is that for each single user there would be more than 2x1000 user_answer rows. This quickly becomes a huge number, so I guess there must be a better way.

Would using a json array be okay in this case? But as far as I understand it'd quickly become a hassle to add more questions etc.

|improve this question
2

As you are at the DBA site, it is likely we will give you database solutions :-)

As far as I can see, you are stuck with your two options - a row for every users answer or a large list. Best bet in terms of database design is to go for a row for every users answer and to make the columns as narrow as possible.

My solution to this would be to change your field "is_correct" in the "user_answer" table to "answer_status" and make it a tinyint. If the user gets the answer wrong, this is set to 0. If the user gets the answer right, it is incremented by 1. For displaying non correctly answered questions, you would scan for all questions where "answer_status" is < 2. Now the column would represent:

  • 0 = question not yet answered or answered incorrectly
  • 1 = question answered correctly once
  • 2 = question answered correctly

When the user starts their exam, you will make an entry for every question in the question table for that user, with the default answer_status = 0.

If your "question_id" column and "user_id" column are both ints, you will store 9 bytes per row - which means if you use 100MB for this table, you would be storing ~119 million rows. If 10,000 rows per user, you will be storing data for ~12,000 users. You would most likely want to cluster this table on "user_id".

In addition, you could clean up the "user_answer" table by setting a flag in a "user_exam" table. It is likely you will need this table to encapsulate the questions into an exam and to list which exams users are enrolled for. This table could have a "status" field which could be set to "Completed" when a user has passed the exam. Subsequently all of the rows in the "user_answer" table can be deleted for that exam, for that user, once they have completed all the answers (save possibly getting some statistics like length of time taken). This would reclaim space in your database when the tables clustered index is rebuilt.

|improve this answer
  • 1
    Exactly the kind of answer I was hoping for, I can't thank you enough for taking the time to write this. Everything you said really makes sense. I had already thought about cleaning up the user_answer table when a user successfully completes an exam, but you suggesting it as well only makes me more confident to stick to this approach. Also big thanks for suggesting clustering the table. I hadn't thought of that yet and it forced me to do some more reading on the matter. – Nikos D Mar 13 '17 at 15:10
  • 1
    There could have been no better and optimized solution than the 0,1,2 approach for the two time problem... – Vishal Kumar Sahu Mar 13 '17 at 15:15
  • Even though I accepted your answer, I'd like to ask if you would still choose to continue with this approach if we assume that the question table rarely changes? And by rarely I mean adding new questions just once every 5 or more years. In that case, could storing a json array ( questionID: answer_status ) be considered a good solution? Thank you again! – Nikos D Mar 13 '17 at 15:19
  • Hi Nikos, I aren't an expert on JSON arrays, but I think I understand what you mean - that you would be storing a delimited list of values? Sure, you can use this approach, but it will mean you have to de compile the list when you want to do aggregations (like a breakdown of how many questions all users have not answered, for instance). You could work around this by doing things like counting the delimiters or storing correct and incorrect answers in different arrays, but to me it seems like hard work. – blobbles Mar 13 '17 at 20:08
  • If you are using a relational database, use it right and you will be able to leverage all its benefits (fast aggregations especially). – blobbles Mar 13 '17 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.