Search code examples
mysqldatabasedatabase-normalization

SQL - Normalizing a table containing multiple choices

I trying to create a database that includes a table, which will have the answers to a multiple choice quiz.. My problem is that, is it normal to create a column for every question?

I mean like if I have 100 questions do I create a column for every one of them?

What is the best approach for this?

Thank you

Solution

  • You can refer this link Table: User

    • user_id auto integer

    • regtime datetime

    • username varchar

    • useremail varchar

    • userpass varchar

    Table: Questions

    • question_id auto integer

    • question varchar

    • is_active enum(0,1)

    Table: Question_choices

    • choice_id auto integer

    • question_id integer

    • is_correct_choice enum(0,1)

    • choice varchar

    Table: User_question_answer

    • user_id integer

    • question_id integer

    • choice_id integer

    • is_correct enum(0,1)

    • answer_time datetime

    https://www.quora.com/What-is-a-good-database-schema-using-MySQL-for-storing-multiple-choice-questions