Database design for quiz application

Question

2.00/5 (1 vote)

See more:

I am designing database for a quiz, and I am stuck. I do not know how to do it.

Here is the relevant information:

Player answers 10 questions.

Each question has 3 answers. Only one answer is correct, other 2 are wrong.

I need help with database design.

What I have tried:

Here is my design:

Table Players:

ID bigint (primary key, identity)
Name nvarchar(MAX)

Table Questions:

ID bigint (primary key, identity)
TextOfTheQuestion nvarchar(MAX)

Table Answers:

ID bigint (primary key, identity)
TextOfTheAnswer nvarchar(MAX)
QuestionID bigint (foreign key to Questions.ID)
isCorrectAnswer bit

Table PlayerChoices:

PlayerID bigint (primary key, foreign key to Players.ID)
AnswerID bigint (primary key, foreign key to Answers.ID)

Posted 26-Jun-16 2:34am

MyOldAccount

Updated 26-Jun-16 3:38am

v2

Add a Solution

Comments

phil.o 26-Jun-16 8:36am

What is the problem? "Finishing the design" is not an issue by itself.

MyOldAccount 26-Jun-16 9:12am

I do not know how to design database for my specific data model. I have changed the title to remove the confusion.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-06-26T03:38:00

Solution 1

Well...there are things I'd change.
First off, why BIGINT? How many questions are you planning of having?
Second, why have a bit associated with each answer which says "this is the right one"?
The way I'd do it is to have a "CorrectAnswer" ID as part of the question, and I'd remove the link from the answer to the question, replacing it with three indexes to Answers. Since you are fixed at three answers to each question, that just makes it easier to check answers later. And you can "recycle" answers by using them for several questions.
I'd also add another table linking Player, question, and answer given - so that you have a means of recording his results. I'd probably also add a date to this table so that your user can repeat the test, potentially with different questions and / or answers.

Other than that, the way to find out if it works is to try it. On paper if necessary perform all the actions you need the user to be able to perform and prove that the system works. If you find something difficult, then this is a good point at which to "rethink" elements of your design - try to get it right before you commit to the actual database and things should go faster.

Posted 26-Jun-16 3:38am

OriginalGriff

Comments

MyOldAccount 26-Jun-16 10:00am

Thank you for helping.

First off, why BIGINT? How many questions are you planning of having?

I guess I did went overboard... Number of questions is 20.

Second, why have a bit associated with each answer which says "this is the right one"?
The way I'd do it is to have a "CorrectAnswer" ID as part of the question, and I'd remove the link from the answer to the question, replacing it with three indexes to Answers.

I agree on moving the "correct answer" id to the question table, but I do not understand what you have meant by "remove the link from the answer to the question, replacing it with three indexes to Answers." I do not know how to achieve this, can you please clarify?

I'd also add another table linking Player, question, and answer given - so that you have a means of recording his results.

I thought table PlayerChoices does that already...

OriginalGriff 26-Jun-16 10:22am

Instead of holding a QuestionID in the Answer, hold three AnswerIDs in the Question.

I forgot the PlayerChoices... :O
Expand that to reference the Question as well (otherwise you don't know which question it was an answer to!) - and I'd still put a datetime on each entry. That also allows you to order the data in the sequence they answered in.

MyOldAccount 26-Jun-16 10:31am

I'd still put a datetime on each entry. That also allows you to order the data in the sequence they answered in.

Ofc... I will do that.

Expand that to reference the Question as well (otherwise you don't know which question it was an answer to!)

Yes, after altering my design according to your suggestions this will be necessary.

I forgot the PlayerChoices... :O

Happens :)

Instead of holding a QuestionID in the Answer, hold three AnswerIDs in the Question.

I have assumed that this was your idea.

Still, I have one concern about integrity of the data, regarding answers:

ID    TextOfTheQuestion CorrectAnswerID WrondAnswer1ID WrongAnswer2ID

1                bla                 1              1               1

We can see that all 3 references can hold 1, but should hold 3 different values.

How should I protect myself from this mistake ( I hope you understand my point, if not I will clarify)?

Thanks again!

OriginalGriff 26-Jun-16 10:54am

Don't call them "correct" and "wrong": call them AnswerA, Answer B, ... and have a separate column which holds the "right" ID. That way, you can insert the "Right" answer in any column and that makes it easier to show them - the right answer isn't always the first!
Protect yourself by making sure your code doesn't do that! :laugh:
It's not really worth trying to force the system to disallow combinations at the DB level - this is really a "higher level" function, and should be done in your C# code where it's A) easier, and B) more logical. Why should the DB know that some combinations are forbidden?

MyOldAccount 26-Jun-16 11:47am

Don't call them "correct" and "wrong": call them AnswerA, Answer B, ... and have a separate column which holds the "right" ID. That way, you can insert the "Right" answer in any column and that makes it easier to show them - the right answer isn't always the first!

OK, thanks for the advice.

Protect yourself by making sure your code doesn't do that! :laugh:
It's not really worth trying to force the system to disallow combinations at the DB level - this is really a "higher level" function, and should be done in your C# code where it's A) easier, and B) more logical. Why should the DB know that some combinations are forbidden?

I guess I am overly paranoid :) I will do as you have said and validate in code.

------------------------

I am still having problems translating your advice into concrete design.

Below is the current design I have.

Can you please take a quick look and offer any improvements/corrections? Thank you!

--------------------------

Table Players:

ID int (primary key, identity)
Name nvarchar(100)

Table Questions:

ID int (primary key, identity)
TextOfTheQuestion nvarchar(100)
correctAnswer int (foreign key to Answers.ID)
a int (foreign key to Answers.ID)
b int (foreign key to Answers.ID)
c int (foreign key to Answers.ID)

Table Answers:

ID int (primary key, identity)
TextOfTheAnswer nvarchar(100)

Table PlayerChoices:

PlayerID int (foreign key to Players.ID)
QuestionID int (foreign key to Questions.ID)
AnswerID int (foreign key to Answers.ID)

OriginalGriff 26-Jun-16 11:56am

Don't call them "a", "b", and "c" - call them "Answer1ID", "Answer2ID" and "Answer3ID" - it's a little more typing, but it makes the use a lot clearer.
Similarly, don't "over decorate" your names: "Answers.Text" is clearer than "Answers.TextOfTheAnswer" - and the same for the Questions.
And I'd still add a datestamp to the PlayerChoices table.

MyOldAccount 26-Jun-16 12:04pm

And I'd still add a datestamp to the PlayerChoices table.

I just keep forgetting to do that! :) Do not worry, I will do as you have suggested.

Don't call them "a", "b", and "c" - call them "Answer1ID", "Answer2ID" and "Answer3ID" - it's a little more typing, but it makes the use a lot clearer.
Similarly, don't "over decorate" your names: "Answers.Text" is clearer than "Answers.TextOfTheAnswer" - and the same for the Questions.

No problem, I just wanted to make comments/OP crystal clear.

---------

So that was the design you were suggesting? Sorry for asking, but this is my first time to meet "3 foreign keys to the same ID" scenario (table Questions have 4 fields that reference Answer.ID).

Again, if you could carefully verify that I have no other mistakes, I would be grateful. Thanks again for all the help!

OriginalGriff 26-Jun-16 12:13pm

Sounds about right.
As I said: give it a try on paper and see how it goes.

MyOldAccount 27-Jun-16 10:03am

It seems that this design will do. I have 5ed you solution and officially accepted it. Thanks again.