Database design for a quiz app

Question

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 -

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).

score 8 · Accepted Answer · answered Mar 09 '13 at 16:17

8

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).

answered Mar 09 '13 at 16:17

Jack Pettinger

2,715
1
23
37

+1 for including structure for recording the users' answers. – Amy Blankenship Mar 09 '13 at 16:21
@JackPettinger what is your advice for rest of the tables ..is it ok ? +1 – Trialcoder Mar 09 '13 at 18:41
@Trialcoder The other tables look fine. However unless I'm missing something, it looks like your structure is only setup for one quiz. If you want mulltiple quizzes you'll need a Quiz table, and a QuizQuestions table. Then you could even have UserQuizzes table, limiting users to certain quizzes. – Jack Pettinger Mar 09 '13 at 19:03

score 1 · Answer 2 · answered Mar 09 '13 at 16:21

First, it's kind of redundant to repeat the name of a table in every field contained in the table. So I'd suggest not doing that. The only possible exception is the primary key/auto-increment field, as some people find it confusing or extra work to have to type myTable.id when two tables both have an ID field. I'm not sure why myTable.id is more work than myTable_id, but there you have it.

What I've used in the past is

table Distractor (which is what eLearning types call the available answers for a question)
id
question_id (FK to the question table)
text (text to display on the screen for that answer)
isCorrect
displayOrder

If you want the flexibility of reusing answers, you'd want to go with a junction table instead of having the question id in the distractors table. However, since you want to nail down the creation date of the question, you probably want to keep the answer strictly pointing to one question.

Database design for a quiz app

2 Answers2