Database model single table or multiple tables

#1

Hello everyone!

So I want to create a test prep web app. I know there are a lot of similar ideas already in the market but I want to do it by myself.

The user will log in, choose a topic + # of questions and start a test.

So far I have:

user table
question table
category table
attempt table

Since a question will have 4 o 5 possible answers and these choices won’t be used in another question, i believe I don’t need to create a choice’s table. Am I right? So basically I need to understand if I need to create a table just for the choices or no.

There is also another features I want and I still don’t know how to design it:

  • Every time a user do another attempt in the same subject, random questions should be presented avoiding the ones already used in previous attempts.

  • User can choose if he wants to have the questions where he didn’t succeed showing up again in the next attempts.

I appreciate any help/tips.

#2

If I understand you correct: You can do this with both one and two tables.

  1. Using one table you have to store a “type” (1=Question and 2=Answer). Besides that you must have to store the “main” (question) id and the “sub” (answer) id in this table. In order to connect the answers to the correct question. A single table that holds connections to itself. It is possible but is more complicated.

  2. Using two tables (question and answers) you only have to store the “main” id in the question table. It is way simpler, but basically you have 2 almost identical tables.

#3

Or you have a “test” table with the columns “question”, “correct answer”, “incorrect answer 1” (and 2/3/4/5)

#4

I assumed a dynamic solution. Add questions and answers on the fly. Maybe I did not read the question that carefully…

#5

I don’t think there’s enough info either way, I just assumed differently! Can we get some clarification @RodMorais ? Because it affects how you structure things.

If I assume a relational dB in the form I thought it was:

Every time a user do another attempt in the same subject, random questions should be presented avoiding the ones already used in previous attempts.
User can choose if he wants to have the questions where he didn’t succeed showing up again in the next attempts.

So you can record (probably in another table, as this is a cross-cutting concern, but it could be on the user table) the IDs of the questions a user has completed successfully. So a relation mapping user to completed questions. And a relation mapping user to questions attempted unsuccessfully.

When you query the questions to show, you can do it via those relations.

1 Like
#6

Thanks all guys for replying to this. Let me share what I got so this can clarify things a bit. I still don’t know if the links/relations are ok (probably missing something).

So far, I have this:

  • User: store account data
  • Profile: store user data
  • Questions: store question title
  • Category: store category names
  • Answers: store answer choices
  • Attempt: store an user attempt, incluing category picked, questions used, wrong questions, time taken etc
  • History: store all attemps

User
_id:
e-mail:
hashedpw:
role:
profile: ProfileID

Profile
_id:
fistname:
lastname:
phone:
address:
picture:
user: UserID

Questions
_id:
title:
category: CategoryID

Category
_id:
name:

Answers
_id:
text:
iscorrect: Boolean
question: QuestionID

Attempt
_id:
datecreated:
timetaken:
result:

History
_id:

1 Like
#7

What kind of database is this?

Also, if each question has a set of unique answers, then I would put them in the same table – they aren’t disconnected, so any benefit you would get from having them in seperate tables is lost, it just makes it much more complicated to code.

1 Like
#8

Yes I am trying to figure out any benefit I would get from having the answers separated from the questions.

At this moment I am just thinking about the data structure, still not decided about which db I will use.

1 Like
#9

As you not have decided database yet, I suggest using Postgresql. By using a fiddle, you may get some ideas how to find the structure.

https://www.db-fiddle.com/f/f8nZn5LQfbmKxAdxX2fdej/63#&togetherjs=3uqTTLODlz

With a fiddle it is way simpler to get answers to more specific questions.