Quiz structure in a db

Associate
Joined
10 Nov 2013
Posts
1,804
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.
 
Permabanned
Joined
9 Aug 2009
Posts
12,236
Location
UK
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.
 
Soldato
OP
Joined
17 Jun 2012
Posts
11,259
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
 
Soldato
Joined
28 Oct 2006
Posts
12,456
Location
Sufferlandria
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:
Soldato
OP
Joined
17 Jun 2012
Posts
11,259
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.
 
Soldato
Joined
28 Oct 2006
Posts
12,456
Location
Sufferlandria
Basic. So it's an array, what I needed to know.

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)
 
Soldato
Joined
3 Jun 2005
Posts
3,047
Location
The South
@jsmoke Out of curiosity, what BASIC and database are you using for this?

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

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.
 
Soldato
OP
Joined
17 Jun 2012
Posts
11,259
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.
 
Soldato
Joined
28 Oct 2006
Posts
12,456
Location
Sufferlandria
I managed it without JOIN. Very simple.

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.
 
Back
Top Bottom