Quiz Database

[slindstr]

Here's a scenario:

I've made an online quiz for students to take. The answers to the questions can be a mix of true/false, whole number addition, division, or essay questions. What would be the best way to design this db so I can get all of the results in one query (using MYSQL)?

My thoughts:

1. You would want to split it up by data type so you don't have a bunch of null rows - that way can you do joins easily

2. Design the db something like this maybe:

`true_false`
--------------------------------
question_id   |     int
answer          |     tiny_int

`addition`
--------------------------------
question_id   |  int
answer          | int

`division`
--------------------------------
question_id   | int
answer          | double

`essays`
--------------------------------
question_id     |   int
answer            |  text


`question_text` (actually stores the questions)
--------------------------------
question_id        |   int
question_text     |  text

...although I suppose you could probably store all the numeric answers in one table as a double (or floating point number), but how would I get all of the data into one query so I get one question id and one answer per row retrieved?

Your thoughts are appreciated
Steve

[Piranha]

I think that I would use one table with it all as varchar or text. Maybe add a column with the type of answer. That way you don't have to worry about designing strange databases, you can easily let the script check the datatype and that the inserted value is correct.

[rsmith]

I agree with Piranha. I would setup the DB like this:

table name: questions
columns:
id - tinyint
type - text
question - text
answer - text

That way when you go to query you can simply do:

$query = "SELECT * FROM question";
$result = mysql_query($query);
$info = mysql_fetch_assoc($result) // this will throw the information into an assoc. array that is easy to work with.

Hope that helps.

[slindstr]

Thanks for your replies - one more question for you: if I need to use mathematical functions on the stored values do I need to convert from text somehow before using the function or can you not do it at all?

Thanks
Steve

[Piranha]

You can't (as far as I am aware of) do mathematic functions in SQL with strings. But it is possible in PHP.

But why would you like to do mathematic functions? If you have a mathematic question it is better to store the correct answer somewhere.