Database table data with different set of values

Question

I am trying to create a database table for my project but I stuck in this type of data.

The purpose is the admin can add, modify or insert types of questions and choices

example:

Do you like pokemon?

• Yes
• No

Pick the pokemons you like

[] charmander

[] pikachu

[] squirtle

[] bulbasaur

Pick your starting pokemon:

• charmander
• pikachu
• squirtle
• bulbasaur

Why pikachu is so cute?

---

Explanation here

---

Here is my table

Questions table

id | question | type

Choices table

id | choice | question_id

UserAnswers table

id | choice_id | question_id | answer | userid

my problem is where to put the explanation of the user since it would be a large set of text? I am still very new in database designing and don't know what to do with this type of scenario. Please any recommendation for a good way to design my table.

Answer 1

I think you're on the right track.

I presume questions.type (NB type might be a reserved word and cause problems) will have the check constraint check type in ('multiple_choice','free_text') or something similar.

Then all you would need to do is rename your proposed UserAnswers to UserMultipleChoiceAnswers and drop the column answer, and create another table, UserFreeTextAnswers with columns id, question_id, answer, user_id.