Quiz structure in a db

Typically in databases (but not always) the ID field would be an integer. Most databases have an auto-increment feature that you can then use to automatically increment the ID field when a new record is added - you don't actually supply the ID yourself when you add records. Your question record could have a name string field that you can use as a short description if you want, but if you're just wanting to pick questions at random there's not much use for it. Obviously you need to store the actual question text in your question record as well.

When you add answers to the database you need to get the ID that was used when your question was created, and then include that in a QuestionID column on the Answers table. That will then allow you to get all of the answers that share a given question ID.
 
For IDs, let your database automatically generate them.

For randomisation, select the answers from the database and load them into objects, add a property to each object which is a random number, sort the objects by that property, then print them.
 
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
 
A select normally returns a recordset to iterate through, but I'm not sure what language your even using, so difficult to help
 
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;

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"];
 
Last edited:
touch said:
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"];
Click to expand...


Basic. So it's an array, what I needed to know.
 
jsmoke said:
Basic. So it's an array, what I needed to know.
Click to expand...

That's just pseudo-code type stuff to show an example of a way to pass the parameter. In your case, it will probably return something like a DataSet or DataTable rather than an array (but it's essentially the same thing so if you understand arrays you'll understand DataSets)
 
@jsmoke Out of curiosity, what BASIC and database are you using for this?

Armageus said:
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
Click to expand...

Just my tuppence - whilst you could go this approach for the correct answer, it does mean you'd be duplicating fields (and in turn increase storage) as well as the potential for fields to go out of sync.

Using two tables (eg - 'Questions' and 'Answers'; having the 'Questions' row ID as a foreign key within the 'Answers' table) and joining them during querying/SELECT is the slightly better option and offers a lot more flexibility in design.
Plus checking an answer would be a simple query to the 'Answers' table with the row ID for the 'IsCorrect' BOOLEAN.

@jsmoke - Only downside to using multiple/relationship tables is the JOIN as, in this case, the returned dataset will have duplicated data and you'll have to iterate through it all accordingly.
 
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 said:
I managed it without JOIN. Very simple.
Click to expand...

I'd recommend using a JOIN:
1- It's a more efficient way of doing it (but unlikely to make a noticeable difference for your use-case)
2- JOINs are an important part of database design and query writing and you need to learn how to do them. I know it's all still new to you and including the join will mean more to learn but it's a bad habit to get into of avoiding stuff like that.
3- Synchronicity. You have 2 separate queries so there will be a time delay between executing. That means the data could change between queries and give unexpected results. Eg a user playing the quiz executes the first query and gets the question from the questions table, an administrator deletes that question and its answers at the same split second, so the user's second query then returns no results for answers to their question and they get shown the question with no possible answers.
 
You must log in or register to reply here.
Back
Top Bottom