Quiz structure in a db

[jsmoke]

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?

 

[jsmoke]

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.

 

[jsmoke]

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?

 

[jsmoke]

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]

Think I'll try JSON instead for now, leave this on the sidelines, don't have the time.

 

[jsmoke]

Looks like I'm going to use XML, the easiest format and least hassle to work with.

 

[jsmoke]

nm

 

[jsmoke]

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,

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.

 

[jsmoke]

How do you assign a var to the return of a SELECT statement?

For ex,

SELECT ids, names, dates FROM T1 LIMIT 0,1

Then say,

int ID = 0
ID = ids

 

[jsmoke]

Well string or int, just generally speaking. What format does a SELECT return, is it an array you can parse out for ex,

Dim ar() as Array

Ar = SELECT ids, names, dates FROM T1 LIMIT 0,1
Ar(0) = ids
Ar(1) = names

......

etc

 

[jsmoke]

No questions and answers.

 

[jsmoke]

What language are you using?

The simplest way to do it is build the query in your code with the parameters and send it to the database server.

Creating the query will be similar in any language:
int questionID = 3;
string query = "SELECT id, name, date FROM questions WHERE id = " + questionID + " ORDER BY RAND()";

Executing that query on the database will be more specific to your language + database engine, could be something like:

var dbresult = db.EXEC(query);

You could then access the different properties of the result from the database:

print dbresult["id"];
print dbresult["name"];
print dbresult["date"];

Basic. So it's an array, what I needed to know.

 

[jsmoke]

I managed it without JOIN. Very simple.

Just

SELECT ID, question FROM questions

and

SELECT answer FROM answers WHERE ID = ' " ID " ' "

There are more conditions put in such as category and a couple of other things.

As for randomising, I just built an array of the rowcount, I put this into a list then used rnd rnd removeAT on each cycle. There's a Fisher-yates shuffle algo, https://en.wikipedia.org/wiki/Fisher–Yates_shuffle but this seems to be working fine so far. A bit hacky though, recipe for bugs.

 

[jsmoke]

Good advise I will look into it.