Search code examples
mysqlsqldatabasedatabase-designdatabase-table

Database design for a quiz app

I am little stuck with the database design for my quiz app. Kindly guide me on this -

My app UI will go something like this -

Image

My DB Tables -

User -

user_id
user_name
user_email
user_password
user_registration_date
user_active

Question -

question_id
question_title
question_category_id
question_level_id
question_author_id
question_status
question_create_date

Category -

cat_id
cat_name
cat_author
cat_create-date
cat_status

Level -

level_id
level_name

Answer -

?????????

Queries -

I am stuck with the answer table..means I am not sure what columns to be set in answer table with the data type for this.

MY answer will only going to be in option format means 1,2,3,4 options or true false in an option NO text type answer ..just a choice selection.

Kindly guide me as I am stuck with this.

Please signify if there is something wrong in the above mentioned tables as well and let me know if crating a separate table for Level is OK or its a bad (Level will only be - Beginner, Intermediate, Advance).

Solution

  • In the past when I have done this I have handled answers in this way:

    Table: QuestionAnswers
    Fields: ID, QuestionID, AnswerText (string), AnswerCorrect (bool)

    Table: QuestionAnswerResponses
    Fields: ID, QuestionAnswerID (linking to the ID table above), Userid, AnswerSelected (bool), TimeAnswered (datetimr).