Quiz structure in a db

jsmoke

jsmoke

jsmoke

Soldato
Joined
17 Jun 2012
Posts
11,259
Any tips on this, say 20 questions with 4 answers each. How would you store a set of 4 records that can be retrieved all at once and the same 4 for each question.

Also the questions will be chosen at random and the 4 answers for each question be displayed in a random order each time the quiz is played.

You could create 20 tables each with the question as the first entry then the following four answers next. Then apply some kind of random generator algorithm?

If I had a lot more than 20 questions then this would be a lot of tables, is this good design or would it slow things down rather than storing most data as records?
 
Last edited:
Randomising the questions is easy - just a:

Select QuestionText From Questions Order By Rand() Limit 20;

would give you 20 questions from your presumably larger pool of questions.


For the answers part, I would guess your Answers table would have 4 Rows linked against the QuestionID.
You would then need a subquery/join to do something like select Rand() above, but somehow pivot it so that the 4 rows become fields (AnswerA,AnswerB,AnswerC,AnswerD)
 
Only need 2 Tables for an almost infinite(not really) number of questions. Questions can also have many answers.

Question
QuestionId, QuestionText,

Answers
AnswerId, QuestionId, AnswerText, IsCorrectAnswer


*edited with correct answer flag
 
Also got to take into account that each question can't be displayed twice(or more) in the same quiz, so for your random generator you would have to somehow flag which questions had already been retrieved and ignore them till all questions and correspond answers had been displayed.

An array sounds good but that doesn't seem to be the best way for db's.
 
chroniclard said:
Only need 2 Tables for an almost infinite(not really) number of questions. Questions can also have many answers.

*edited with correct answer flag
Click to expand...

Could also move the correct answer to the questions table, which gives you the option just to a quick string comparison against the selected answer

e.g.

Questions TABLE
QuestionId, QuestionText, CorrectAnswerText

Answers TABLE
AnswerId, QuestionId, AnswerText


jsmoke said:
You could create 20 tables each with the question as the first entry then the following four answers next. Then apply some kind of random generator algorithm?

If I had a lot more than 20 questions then this would be a lot of tables, is this good design or would it slow things down rather than storing most data as records?
Click to expand...

It's very bad design - you don't need 20 tables for 20 questions - you only really have 2 "objects", a question and associated answers. Therefore generally you would use 2 tables.


You could even get by with 1 table, but you wouldn't be able to randomise the answers easily with a database query

QuestionsAndAnswers TABLE
QuestionID, QuestionText, CorrectAnswerText, AnswerTextA, AnswerTextB, AnswerTextC, AnswerTextD
 
I could do this with a text file, arrays and a bit of parsing, I believe I could also do this with JSON, but I'd like to try the db way first.

Why is multi tables bad design, is it slower?
 
Because multiple tables is just insane.....:p Its not scalable, its not maintainable, it wont be easy to query, would be a total mess. To add a new question you would have to add a new table! Literally no good reason to use multiple tables.
 
As long as each question is in the database only once and you do a select top 20 from the table you won't get duplicates, the issue is, you may always get the same 20 questions

As well as Armageus's RAND(), you could add an INT column that records how many times that has been used and then factor that column into your select to only pick the questions that have been asked the fewest times.

Do you need to sort the questions by subject in case you want to target a certain subject?

Following on from Chroniclard:

Question
QuestionId, QuestionSubject, QuestionText, TimesUsed

Answers
AnswerId, QuestionId, AnswerText, IsCorrectAnswer

As for using multiple tables, what happens if you've 1000 questions? also, when joining between lots of tables it'll slow the query. What if you want to check for duplicates? much easier in a single table than over many.
 
jsmoke said:
I could do this with a text file, arrays and a bit of parsing, I believe I could also do this with JSON, but I'd like to try the db way first.

Why is multi tables bad design, is it slower?
Click to expand...

Because there is little value in them being in a database if they are in separate tables. A database's main purpose is to store related data - you can filter it easily and change the format easily.

If you were using text files, then 1 text file per question+answer is likely a good option - your programming (arrays, parsing etc) has to deal with the data.

However with a database, you can (re)move a lot of the programming, by getting the database to give you data in the format you want it:-

- Want it randomised? - Ask the database to order it randomly
- Want only 30 questions from your 5000? - Ask the database to limit it to 30
- Want only questions that haven't been used recently? - Add a timestamp field and ask the database sort by the oldest timestamp
- Want to only show Questions about Sport? - add a Category field and then amend your Query to SELECT FROM Questions WHERE Category='Sport'
 
So for TimesUsed you would have to update this field with 1 then the next selection you would check this value, you would then have to update all TimesUsed values to 0 at the end of the quiz?

And how do you relate QuestionID to AnswerID remember that there are 4 possible answers to be displayed in a random order each time they are selected.

I thought this was going to be straightforward.

Probably be a hack job.
 
jsmoke said:
So for TimesUsed you would have to update this field with 1 then the next selection you would check this value, you would then have to update all TimesUsed values to 0 at the end of the quiz?
Click to expand...

Depends on what you want to do with it? You could reset it at the end of that particular quiz, but what if you want to play again, or the person sat next to you also wants to play? Ideally you'd leave it set so that the next time you play you'd Select less frequently used questions.

jsmoke said:
And how do you relate QuestionID to AnswerID remember that there are 4 possible answers to be displayed in a random order each time they are selected.
Click to expand...

Randomising the answers may be easier in the front end rather than asking the database to do it.
Using the 1 table schema from above:

QuestionsAndAnswers TABLE
QuestionID, QuestionText, CorrectAnswerText, AnswerTextA, AnswerTextB, AnswerTextC, AnswerTextD

your front end would ask for 20 questions randomly (or including a times used field) - normally this is returned as a recordset that you loop through.

As you loop through each question, you would pull the 4 answer fields (AnswerTextA, B, C, D), into an array or other collection - randomise the array/collection and then display them.

When an answer is then selected, compare it to the CorrectAnswerText field.


jsmoke said:
I thought this was going to be straightforward.
Click to expand...

It is straightforward if you have knowledge of databases - but getting your head around what is possible with a database can be daunting at first.

It certainly can be done without a database, but if it's something that might scale, change, evolve going forward, are text files/JSON etc going to scale as easily?

If you're using a text file containing all the questions and answers and any other fields, what happens if you want to add another field (e.g. to store category)? Likely you need to completely rewrite the text file (e.g. adding in the extra line for every record), as well as changing the code to import the text file.
With a database you just add the extra field, and ask for it in your select statement.


jsmoke said:
Probably be a hack job.
Click to expand...
Anything can end up a hack job if you let it, but that isn't specific to using databases. If anything it's usually "less hacky", as you can reformat your data to suit your program, rather than having to make sweeping changes to code in your front end program
 
It is fairly straightforward, in the grand scheme of things. :p

In the two table example the answer table has a foreign key, QuestionId, so you can just select all answers for the appropriate QuestionID
 
Maybe you also want a "pool" of possible wrong answers (e.g. to stop people "learning" the wrong answers and succeeding through trial and error, during multiple playthroughs)?

Might be easier to leave the correct answer as a field in the Question Table i.e. CorrectAnswerText, and then just have the Answers table for "wrong" answers

With a two table approach it's easy to add multiple records to the Answers table, as they are linked via a Foreign Key, you could then just select 3 wrong answers from say 8 possibles?


With a fixed format data file like a text file, it's trickier to have a different number of wrong answers for each question

e.g.
Question 1 might have 5 wrong answers
Question 2 might only have 3 wrong answers
Question 3 might have 9 wrong answers
 
JSON and XML are not comparable to databases. They are just formats for accessing/organising data (similar to an array or something like that), not storage of data so you still need somewhere to save the data. Presumably when you say you're using XML, you're just going to type out the data in an XML structure in a text file? There's nothing wrong with doing it that way for your example but a database is much more efficient - particularly when you get into larger amounts of data.
 
I get the db now. Two tables, each with an id integer and an id string, the id string being the same for the question and 4 answers.

So something like

SELECT question, id FROM T1 WHERE id = 'abc1'
SELECT answers, id FROM T2 WHERE id = 'abc1'

So that will give me say, 'what colour is the sky', 1
And , 'blue', 'green', 'white', 'yellow, '1', '2', '3', '4'


Then I assume increment abc1 to abc2 etc.

Any suggestions on incrementing, use a loop something like,

Code: 
std::string name = "abc";
int num = 1;
std::string result;
result = name + std::to_string(num);
num++;

Sort of thing or is there a better way?

Also need to work out how to do the random choosing.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom