Creating online test

[cmccully]

Hi


  I am creating an online test and would like some suggestions on how to proceed.

  Let me first state that I am new to web programming so if you respond please be as descriptive as posible, this will help me fully understand what you are talking about.

  The basic scenario is that someone will view an article then be shown a multiple choice test. The test will be imeadiatly scored and if the student fails they will be allowed to retake the test untill a passing score is reached. The test's will have between 20 and 100 questions each and from 2 to 5 options to select for each question.

  The way the site currently works, the article is a static HTML page. A PHP page retrieves some information about the article from MYSQL, updates a MYSQL article count field then displayes the article file and a link to the test.

  The question I have is how to design the test pages.

[color=red]Option 1:[/color]
[b]  Hard code each test and store it as a sepaerate file. [/b]I dont really like this option because if I later decide to change how something works then every test page will need to be changed.

[color=red]Option 2:[/color]
  [b]Create a massive database table with room for up to 100 questions and 5 responses per question. [/b]  This is obviously a rediculous way to code the problem. The table would waste a great deal of space and be very difficult to manage.

[color=red]Option 3:[/color]
[b]  Create a databse table with 1 row for each test and store the test questions, reponses, and answers in a VARCHAR column, essentially as a flat file.[/b] A PHP file would be passed the ID number for the article that was read and then pull out and create the test on the fly. I like this option because it would be easy to modify the tests layout later. Also I can store per question test result data to identify problem questions or tests. This would of coures require content managment pages to store or modify the test data but I would want to do this anyway to ensure consistancy and to allow laymen to create the tests.


  As I stated before I am new to web programming so I have to wonder, is there an easyer or better way to do this? Any ideas are appreciated.


cmccully

[Jenk]

Contrary to your belief, option 2 is the best.

[cmccully]

Jenk,


  Please tell me why you think option 2 is best. This table would have over 700 fields for each record. This seams like it would be a problem.



cmccully

[neylitalo]

I wouldn't say option 2 is the absolute best, although it is certainly the best of the options you've picked, and very close to what I would suggest. I think a database layout as follows would be optimal:

[u]question[/u] (database table)
question_id int4 not null
question_wording text (varchar(255) may not be enough, here)

[u]answer[/u] (database table, again)
answer_id int4
answer_question_id int4
answer_value varchar(255)
answer_is_correct boolean

And then, when you create the question, you also pull the associated rows from answer to build your question -> answers relationships.

cmmcully: You can save yourself a heartache and not worry about your database server. :) Something so small as this will surely be an easy task for any database engine. Databases are designed to handle data and to handle a lot of it, so you're well in the clear on this one.

[cmccully]

neylitalo

  Thank you for your response. I am a bit unclear, are you saying that each test should have two tables dedicated to it?


cmccully

[Daniel0]

Yeah, and perhaps three:

[u]article[/u]
article_id int4 not null
article_title varchar(255)
article_author varchar(255 (may not be needed depending on what you mean with article)
article_body text

[u]question[/u]
question_id int4 not null
question_wording text
question_article_id int4

[u]answer[/u]
answer_id int4
answer_question_id int4
answer_value varchar(255)
answer_is_correct boolean

[448191]

I'd have to support Daniels layout.

Typical data as taken from your example:

article: 10 rows(arbitrary number), question: 10x20 rows, answer: 200x5 rows.

[cmccully]

Well, now you all have me thinking.

Some of the issues I was concerned about with storing all the test information in a single table with hundreds of fields were wasted space, speed and complexity. Jenk prompted me to re-consider this as an option.

Upon further consideration I believe the use of VARCHAR fields will result in little wasted space.

To test the speed penalty of spanning a large chunk of data accross hundreds of fields I performed the following benchmark, which was inspired by Talented's famous, or should we say, infamous echo benchmark:
Two test tables. One with 100 fields, each storing 100 characters. One with a single field storing 10000 characters. Then I measured the response time of retrieving the entire table. As I suspected the single field table was retrieved faster, on the order of three times faster. However, the slower multi-field table was still retrieved in 0.0030078887939453 seconds, score one for neylitalo. I think I can live with that.

Finally, I think the issue of complexity is a mute point since all of the scemes we have discussed are going to be a bit complicated to implement and maintain due to the large number of loosly related data items I will be storing.

Now, neylitalo, Daniel0, and 448191 are suggesting creating 2 or 3 tables for each article / test pair. This would result in hundreds of tables. Can you guys elaborate on why you prefer this method over others that were discussed? What benefits would I get from this approach?


cmccully

[roopurt18]

Make sure that whatever you do, the final test is javascript and has all of the answers embedded within the HTML.

Those are my favorite online tests.

[neylitalo]

No, don't create a table for each test. Tables should be very static, and tasks such as adding a new test should not warrant the creation of another table. Use Daniel0's suggestion - you can pretend 'article' is 'test'. I'll elaborate a bit further, in hopes that something will click.

I'm going to teach a very small bit of object oriented design here. Your idea would indeed work, but it's rather poor design. Your design completely ignores one of the most important concepts of designing a data structure: abstraction. Abstraction, in this case, means that you're keeping related data together, and unrelated data separate. (You can see this [url=http://en.wikipedia.org/wiki/Data_abstraction]Wikipedia article[/url] for more info.)

I use the terms "related" and "unrelated" in the absolute strictest sense. Yes, the questions for the tests are "related" to the test itself, but the level of relativity we're talking about is expressed by these questions: Is a question the same as a test, and is a test the same as a question? If those conditions are [b]always[/b] true, then they are indeed related, and you can put them into the same database tables. However, in this case, those conditions are not always true, thus they are not related. So we're going to want to separate them in the data structure, to simplify the structure and make it easier for you to work with.

One little bit of advice for you, to understand this next part: Think of these objects simply as ideas. Don't think of them as what they'll be in the end, don't think of them as concrete objects. Think of them as [u]ideas[/u]. Use your imagination - it's very powerful. :) Anything can happen in your imagination; not so, in the real world.

Now we're going to figure out the level of abstraction we're going to need for this.

[b]Step 1:[/b] Figure out the object we're working with. In this case, we're working with "tests". Remember, this "test" is just an idea.
[b]Step 2:[/b] Figure out the core components of a "test". I think that would consist of: A) The questions, and B) The test itself. The test is just a wrapper for all the questions, simply a container.
[b]Step 3:[/b] For each core component, you need an object. Remember, these objects are ideas in your head. If you have an idea, you can apply it to anything. In this case, these objects (ideas) are going to be applied to database tables.
[b]Step 4:[/b] Now, we usually record these objects (paper has a purpose again!), but in this case, I think that you can remember the objects we came up with: "tests" and "questions".
[b]Step 5:[/b] For each of those objects, we need to go back to step 1 and figure out if these new objects can be abstracted at all. "Tests" are good the way they are, but there's another level of abstraction when it comes to "questions". So we hop back up to Step 1 and plug in "questions" as the object we're working with.
The objects that we come up with this time around should be "questions" and "answers". I determined that by figuring that each question has an answer or multiple answers.
[b]Step 6:[/b] And, if applicable, repeat again with the results of the second round of abstraction. However, in this case, it's unnecessary - we've broken it down into all the elements that we're worried about.
[b]Step 7:[/b] You know all the different objects that you need to make something out of, and you know what their relationships are. Now all it takes is to put it into place. In this case, we're concerned with database tables. Figure out what tables you're going to need, what fields you're going to need for each object, and how you're going to relate the tables to one another. This is where foreign keys are going to be used. A foreign key is just a field in one table that references a field in another table (usually an ID), so as to "link" one row in a table to a row in another table. See this [url=http://en.wikipedia.org/wiki/Foreign_key]Wikipedia article[/url] for more info.

Don't worry, after a while, this entire process will become second nature, and you'll be able to do it for very complex systems in a matter of seconds or minutes.

And again, don't worry about your database server and its speed - it knows what it's doing, and I [b]assure[/b] you that a few tables is not going to bog it down. Our database at work is over 100 tables, and they probably average [i]x[/i] thousands of rows. And that's a small database, relative to some. Don't worry one bit.

[quote author=roopurt18 link=topic=109742.msg443328#msg443328 date=1159481173]
Make sure that whatever you do, the final test is javascript and has all of the answers embedded within the HTML.

Those are my favorite online tests.
[/quote]

Woohoo!

[448191]

Very solid explanation neylitalo!  :)

[quote]Woohoo![/quote]

Lol...  :D

If you do embed the anwers into the served page, make sure the right answer is not viewable by looking at the source or open the cached javascript file, that'd be unfair.. :P

[cmccully]

Thanks to everyone for your responses and especially to neylitalo for his thorough explanation. I know what I need to do now, the only think left is to make it come alive. 8)



cmccully

[cmccully]

Hi,

  Just wanted to post my table schema to see if I am on track with what was suggested.

[b]test_questions table- Stores the questions for all tests[/b]
test_questions_id PK int(11) auto inc
test_options_id    FK int(11)            Link to test_options table.
test_answers_id  FK int(11)            Link to test_answers table.
article_id            FK int(11)            Link to article table (This is the info they are being tested on).
quest_1              VAR CHAR (250)    First test question.
quest_2              VAR CHAR (250)
.
.
.
quest_100          VAR CHAR (250)    Last question.


[b]test_options table- Stores the options (posible answers) that can be selected for all questions[/b]
test_option_id      PK  int(11)  auto inc
opt_1a                    VAR CHAR (250)                First option that can be selected for question1.
opt_1b                    VAR CHAR (250)                Second option that can be selected for question1.
opt_1c                    VAR CHAR (250)                Third option that can be selected for question1.
opt_1d                    VAR CHAR (250)                Fourth option that can be selected for question1.
opt_1e                    VAR CHAR (250)                Last option that can be selected for question1.
opt_2a                    VAR CHAR (250)                First option that can be selected for question2.
.
.
.
opt_100e                  VAR CHAR (250)                Last option that can be selected for question100.



[b]test_answers table- Stores the correct option (A,B,C,D, or E) that can be selected for all questions from the options table[/b]
test_answers_id        PK      int(11)      auto inc
ans_1                                enum ('a','b','c','d','e')              Correct option for each test question.
ans_2                                enum ('a','b','c','d','e')
.
.
.
ans_100                            enum ('a','b','c','d','e')


I also have plans for a fourth table that will store the results of each scored test. This will basicly just store the id numbers of the above tables and the customers id. I want to be able to reproduce the test if necesary and to gather statistics on individual test questions for quality control.


cmccully

[Daniel0]

I would create yet another table for the options that is "attached" to the questions table. Something like this:

oid tinyint(11)
o_key varchar(255)
o_text text
o_question_id tinyint(11)

[cmccully]

Daniel0
  I am unclear as to the purpose of this 2nd options table, can you expand?

[Daniel0]

Sure I can. Make the following database structure:

[tt]-------------------------------------
| aid | a_title | a_author | a_body | - Table 'articles'
-------------------------------------
  |
  -----------------
                  |
------------------------
| qid | q_text | q_aid | - Table 'questions'
------------------------
  |
  --------
          |
----------------------------------------
| oid | o_qid | o_value | o_is_correct | - Table 'options'
----------------------------------------[/tt]

The articles table holds all the articles and has a unique id (aid). All the questions have a unique id aswell (qid) and is connected to an article by defining the article id (q_aid). All options is connected to a question by defining the question id (o_qid).

[tt][u][b]articles:[/b][/u]
aid          tinyint(11)
a_title      varchar(255)
a_author    varchar(255)
a_body      text

[u][b]questions:[/b][/u]
qid          tinyint(11)
q_text      varchar(255)
q_aid        tinyint(11)

[u][b]options:[/b][/u]
oid          tinyint(11)
o_qid        tinyint(11)
o_value      varchar(255)
o_is_correct enum('0','1')[/tt]

Queries: [code]CREATE TABLE articles (
  aid tinyint(11) NOT NULL,
  a_title varchar(255) NOT NULL,
  a_author varchar(255) NOT NULL,
  a_body text NOT NULL,
  UNIQUE KEY aid (aid)
) TYPE=MyISAM COMMENT='Articles table';

CREATE TABLE options (
  oid tinyint(11) NOT NULL,
  o_qid tinyint(11) NOT NULL,
  o_value varchar(255) NOT NULL,
  o_is_correct enum('0','1') NOT NULL default '0',
  UNIQUE KEY oid (oid)
) TYPE=MyISAM COMMENT='Question options table';

CREATE TABLE questions (
  qid tinyint(11) NOT NULL,
  q_text varchar(255) NOT NULL,
  q_aid tinyint(11) NOT NULL,
  UNIQUE KEY qid (qid)
) TYPE=MyISAM COMMENT='Questions table';[/code]

[Daniel0]

I've been nice to you and made a sample script:

Database contents:
[code]INSERT INTO articles (aid, a_title, a_author, a_body) VALUES (1, 'Test article', 'Daniel Egeberg', 'This is a test article!!');

INSERT INTO options (oid, o_qid, o_value, o_is_correct) VALUES (1, 1, 'Yes', '1'),
(2, 1, 'No', '0'),
(3, 2, 'Yes', '1'),
(4, 2, 'No', '0');

INSERT INTO questions (qid, q_text, q_aid) VALUES (1, 'Do this work?', 1),
(2, 'Is Daniel a great PHP programmer?', 1);[/code]

The script:
[code]<?php
$article_id = empty($_GET['article']) ? 1 : intval($_GET['article']);

$link = mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("test");

$a_query = mysql_query("SELECT * FROM articles WHERE aid={$article_id} LIMIT 1");
if(mysql_num_rows($a_query) <= 0)
{
echo "Sorry, there is no article with the id {$article_id}";
}
else {
$q_query = mysql_query("SELECT * FROM questions WHERE q_aid={$article_id}") or die(mysql_error());
$o_query = mysql_query("SELECT * FROM options");
$options = array();
while($o = mysql_fetch_assoc($o_query))
{
$options[$o['o_qid']][] = $o;
}

if(count($_POST) > 0)
{
$correct_answers = 0;
while($question = mysql_fetch_assoc($q_query))
{
foreach($options[$question['qid']] as $option)
{
if($_POST["question_{$question['qid']}"] == $option['oid'] && $option['o_is_correct'])
{
$correct_answers++;
}
}
}

$correct = round($correct_answers/mysql_num_rows($q_query)*100,2);

echo "You had {$correct}% of the questions correct!";
}
else {
$article_info = mysql_fetch_assoc($a_query);
echo <<<EOF
<h1 style='margin-bottom: 2px;'>{$article_info['a_title']}</h1>
<p style='margin-top: 2px;'><em>By: {$article_info['a_author']}</em></p>

<p>{$article_info['a_body']}</p>

<hr />

EOF;

if(mysql_num_rows($q_query) <= 0)
{
echo "<p>ERROR: The article contains no questions</p>";
}
else {
echo "<form method='post'>";

echo "<ol>";
while($question = mysql_fetch_assoc($q_query))
{
echo "<li><p><strong>{$question['q_text']}</strong></li>";
if(key_exists($question['qid'],$options))
{
echo "<br /><ol style='margin-top: 0px;'>";
foreach($options[$question['qid']] as $option)
{
echo "<li><label><input type='radio' name='question_{$question['qid']}' value='{$option['oid']}' /> {$option['o_value']}</label></li>";
}
echo "</ol>";
}
else {
echo "<br /><em>Error: no options!</em>";
}
echo "</p>";
}
echo <<<EOF
</ol>
<button type='submit'>Submit answers</button>
</form>

EOF;
}
}
}

mysql_close($link);
?>[/code]

[roopurt18]

You're not seriously going to stick 100 extra columns into your [b]test_questions[/b] table, are you?
You're also not going to stick 500 extra columns into your [b]test_options[/b] table?

All of those extra columns are declared as VARCHAR(250).  If every single test is going to have 100 questions with 5 choices, that's fine.  But even then you're going to have ridiculously long queries, which you've already stated you won't.  So what you're really going to do is waste [b]a ton[/b] of space.

1) Start with an articles table and give each entry a unique ID, called [b]article_id[/b].

2) Create a questions table and give each question a unique ID.  In this table you will need a column to link each question to an article; this column will be titled [b]article_id[/b].

Given this setup, you can query the database for all of the questions related to a particular article.
"SELECT * FROM articles a, questions q WHERE [b]q.article_id = a.article_id[/b]"
Notice the part in bold; it is that unique ID that allows you to link the questions to the article.

3) Now create your answers table giving each answer a unique ID, answer_id.  Each answer will also have a column linking it to at least a question, question_id.

Now you can select all of the answers for a given question:
"SELECT * FROM questions q, answers a WHERE [b]q.question_id = a.question_id[/b]"

If you need to select all answers for an article:
"SELECT * FROM questions q, answers an, articles ar WHERE ar.article_id = q.article_id AND q.question_id = an.question_id"

[Daniel0]

[quote author=roopurt18 link=topic=109742.msg445495#msg445495 date=1159815079]
You're not seriously going to stick 100 extra columns into your [b]test_questions[/b] table, are you?
You're also not going to stick 500 extra columns into your [b]test_options[/b] table?
[/quote]

Do you mean me? Because there is only 4 fields in my options table. And for each option there is inserted another row.

[neylitalo]

[quote author=roopurt18 link=topic=109742.msg445495#msg445495 date=1159815079]
You're not seriously going to stick 100 extra columns into your [b]test_questions[/b] table, are you?
You're also not going to stick 500 extra columns into your [b]test_options[/b] table?
[/quote]

Oh, my... good catch. I completely missed the whole "100" thing in the column name.

Get ready for lesson two of object oriented design.

If I had to pick a concept that I thought of as the core of OO design, I think I would pick modularity. Most people with no training or experience know very little about modularity. Everybody should, and probably will, learn about it sooner or later, but it's much better to learn it via training than to spend the hours re-discovering the concept after realizing that there has to be a better way. Modularity is the idea that you build a system in parts, and connect the parts together, as opposed to building one great big system. I give to you an example, in hopes that it'll communicate better than just theories and ideas can, and it'll be a lot more fun. :)

Legos. Or Lincoln Logs. Or Tinker Toys. We've all played with 'em. Castles were among my list of favorites, and it was not uncommon to stumble over the cities composed of docks, police stations, gas stations, and of course, the wild west-era Sheriff's office. (Let us not forget the little stick of Lego dynamite you put into the conveniently loose Lego brick to free Black Bart from the Lego jail.) All those toys are a prime example of modularity. How much fun would it have been if they had assembled them in the factory and then shipped them to you, made them as a single piece of plastic, or glued the Lincoln Logs together? And what if they had implemented a policy of making each particular set a different size, or used squares instead of dots on some Legos, and triangles on others? Had they implemented the "send it as one piece" idea, you would have had a log house or a plastic submersible, scientist locked inside with plastic. Had they decided not to make their toys universally interchangable, you would have had dozens or hundreds of Legos, Lincoln Logs, or Tinker Toys, but you certainly couldn't build the floor-sized log fortress or the ocean liner of Legos that weighed more than you did.

But they decided not to make them like that. They chose to make their toys in [b]modules[/b]. That is, they were individual pieces that fit together if you asked them nicely, and you could do anything with them. If you wanted to add another wing to your castle, you could do just that. Everything worked with everything. They were designed to allow for future expansion, and for unlimited possibilities.

One of the bonuses (purposes) of modularity is the... umm... modularity it provides. You can pick and choose parts to use. For example: What happens if you decide to build a new system that has questions, but not tests? If your questions are fully integrated into your test system, then you'll have to a) modify it, or b) make a new one, neither of which are particularly fun. However, if you modularized it from the beginning, you could say "Hey, I know! I'll just use the 'question' and 'answer' tables, and the systems I built for them!" And voila! you've got yourself another system with minimal work.

If you've got any experience with *nix, then you'll know all about modularity - for example, the music players don't all have their own audio engine. They probably use a library that's been designed to be used in multiple different ways. For example, there's the libxine library. xine is a popular media library, and dozens of programs use it. Had it been integrated into a particular program, everybody would have to waste time making their own.

Now that I've covered most of the ideas, let's see how you can apply this to your problem.

I swear I covered this already, but you want your database table to be a representation of ONE type of object. [b]ONE.[/b] Not two. Not seven. Not even if one type of object belongs to the other. Tests in one table, questions in another, answers in another. They are completely different objects. They do not belong in the same table. You can have questions without tests, tests without questions, and questions without answers. This fits into the "bits-and-pieces-are-better-than-one-big-blob" part of modularity. You want them [b]separate[/b]. Keep them as their own individual units. This will allow you to pick and choose which parts to use for a particular implementation.

You're definitely not allowing for future expansion. What happens if you decide that you want to add an extra hundred questions, and randomly pick half of them, to mix it up a bit? I think that's a fairly reasonable feature. You would have to go into your table and add 100 extra columns to the already cluttered table. Let's see how many headaches that would bring on: One for creating the columns, maybe two or three. I just developed carpal tunnel thinking about all the mouse clicks and typing. And then, don't forget the headaches that would come from updating, inserting, and everything else. Who wants to type a query to insert 100 values into a row? "Not I", said the cat. Any time you find yourself about to create more than 15 or 20 columns in a table, you'll want to seriously try to find a better way to do it. I imagine there are a few applications that it might be easier with all those columns, but it's certainly a very small number.

And so, as I find myself falling asleep at the keyboard, I just want you to do one other thing. Use your common sense. Common sense should tell you that you don't want to type out queries to insert 100+ values into a row. Common sense should tell you that it's not always going to be the same, and that you should allow for the future.

Excuse me as I retire for the night - I want to be able to finish my life-size Lego giraffe in the morning.

[Zane]

If you haven't grasped the idea completely yet...here's a visual

[size=14pt][color=red]questions table[/color][/size]
[code]q_id    q_body                                                q_answer
  0    "What is 2 + 2?"                                            2
  1    "Who was the first president of the US?"                    4
  2    "Who founded Microsoft?"                                    3
  3    "What is PHP?"                                              1
  4    "What year is it?"                                          0[/code]

[color=red][size=14pt]answers table[/size][/color]
[code]a_id    a_body                                               
  0    "2006"
  1    "Hypertext Preprocessor"
  2    "4"
  3    "Bill Gates"
  4    "George Washington"[/code]

Then when setting up your test it's a matter of matching which quesitons you want for it

[Daniel0]

The way it is going to be depends on if it's a multiple-choice quiz/test or if the user has to actually enter something.

[roopurt18]

[quote author=Daniel0 link=topic=109742.msg445801#msg445801 date=1159854438]
[quote author=roopurt18 link=topic=109742.msg445495#msg445495 date=1159815079]
You're not seriously going to stick 100 extra columns into your [b]test_questions[/b] table, are you?
You're also not going to stick 500 extra columns into your [b]test_options[/b] table?
[/quote]

Do you mean me? Because there is only 4 fields in my options table. And for each option there is inserted another row.
[/quote]

Nope, sorry for the scare.  I was looking at the OP's post further down the first page and hadn't yet read any of the replies.

[roopurt18]

Just an analogy for the OP.

If 20 people throw their drivers license into a basket and I start picking licenses at random, I can easily give the correct license back to the proper person.  The reason I can do so is because of the photo.  Think of the license and the person as representing separate but related data entities, the photo is what connects them together.

A DB should be designed in a similar fashion.  You have related pieces of data linked together in some manner; but you don't store them together in the same table because an article doesn't necessarily have questions.  You can put all your articles in one table, all of your questions in another, and as long as each article has a unique integer ID and each question stores the ID of the article it's for, you can link them together.

(EDIT) Typos > Me

[Daniel0]

That is an excellent way to explain it.