Database table data with different set of values

database-designdatabase-recommendation

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.

Best Answer

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.

Related Question