CODEWITHSUNDEEP
phphtmlmysqlphpmyadmin
osbt
osbt

Reputation: 131

Storing more than one string in a MySQL Database Table

Before I ask my question, I just wanted to thank everyone that replied to my question yesterday -> Countdown using javascript.

Idea:

I created a quiz using php, but I would like to create a MySQL Database and having a table with all the questions, answers and multiple choice stored inside.

Issue:

Since the quiz is multiple choice, I don't know how to go about storing the multiple choice options in the table. Could I store the options and have each answer separated by a special character and let php get the string and separate the options?

Ex: Question: What is your favorite color? Options: Blue=Red=Purple=Yellow.(Database View)

What do you folks think is the best practice for something like this?

Upvotes: 2

Views: 947

Answers (6)

John Woo
John Woo

Reputation: 263853

There could be many possible schema designs for this but my suggestion is like this:

Don't ever store values separated by comma on the tables.

This table holds the quizzes.

Quiz Table

  • QuizID (PK)
  • other columns..

This holds the questions for every quiz.

Question Table

  • QuestionID (PK)
  • QuestionDetail
  • QuizID (FK)
  • other columns...

This holds the answers.

Answer Table

  • AnswerID (PK)
  • AnswerDetail

This holds the correct answer for every question on each quiz or in other words, this is the answer key.

Question_Answer_Correct Table

  • QuestionID (FK) -- also a compound primary key with AnswerID
  • AnswerID (FK)

This contains list of users.

User Table

  • UserID (PK)
  • UserName
  • other columns...

This contains answer of users on a specified question. There is no QuizID here since the questions are already connected on the quiz table.

User_Answers Table

  • UserID (FK)
  • QuestionID (FK)
  • AnswerID (FK)

Upvotes: 2

ethrbunny
ethrbunny

Reputation: 10469

Tables:

quiz 

quiz id (pk)
quiz info (other columns)

questions

question id(pk)
quiz id
question text

answers

answer id(pk)
question id
answer text

To display a given question do a join on the quiz, question and answers.

EDIT: You could either add a column for 'right/wrong answer (0/1)' or have another table:

solutions

solution id(pk)
question id
answer id

I didn't put the 'correct answer' in the answers table as that's not good normalization.

Upvotes: 2

mishmash
mishmash

Reputation: 4458

You'd have a questions table, like this:

id  |            question
0             Do you even lift?

id would be INT(11) PRIMARY_KEY AUTO_INCREMENT while question would just be TEXT. Then, you would have an answers table:

id  | question_id | answer
0          0         Yes
1          0         No
2          0         Maybe

Here, question_id refers to the ID of the question in the questions table. These answers all belong to one question. This is called a Has many relationship, as one question has many answers.

This is how its usually done. Implementing it is not that hard, even if you're not using a framework (most of them do the work for you).

Hope this helps

Upvotes: 2

Nick Andriopoulos
Nick Andriopoulos

Reputation: 10643

There are a couple of ways around this :

  • The "proper" way is to create another table ( so that you have a table called "questions", each of which have a unique id, and another called "answers", where each has the question id )
  • The "simple" way, which is to use JSON ( see json_encode and json_decode ) which takes care of using special characters etc in a field

Upvotes: 2

user1646111
user1646111

Reputation:

simply, create two tables:

  • questions

    question_id, question

  • answers

    answer_id, question_id, answer

Now, you can link these two tables using question_id

Upvotes: 2

kero
kero

Reputation: 10638

I think the best practice would be to use multiple tables. One for the question and one for answers. The answer table would contain question_id as well as a flag whether or not it is the correct answer

It could look like this

TABLE questions
FIELDS: id, text

TABLE answers
FIELDS: id, question_id, text, correct

The problem with using one field for all the answers is that you could accidently use the character you use for splitting inside the text of an answer

Upvotes: 2

Related Questions