Designing a database for a personality quiz script

Since Blogger does not offer categories, I'll have to use tags for posts like this. And what's special about it? I'm not going to talk PHP this time - instead of that I'll talk databases.

Let's discuss a database for a personality quiz script - you know, one that will build tests which will say "you are a good worker", "you like to spend too much money", "you are a loving person" etc.

What is specific for the personality quizzes? The main part is that answers to questions must be matched to personality types. Some quizzes use point systems but they are less accurate because you may have answers for contrary personality types and the system may calculate that you belong to the middle one. For personality quizzes a lot more accurate is a system which will directly match the answer to the specific personality type and at the end show the personality type which collected the most answers.

So here is my proposition for a database along with short explanations:

Table Quizzes:
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
title VARCHAR(255)
description TEXT
num_users INT UNSIGNED NOT NULL

This table will contain one record for each quiz you want to run. You may want to add extra columns like date, tags etc. In num_users we will store the number of users who took the quiz.

Table Questions:
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
quiz_id INT UNSIGNED FOREIGH KEY to quizzes.id
question TEXT
question_type

This obviously is the table with questions. We need a foreign key to the table with quizzes and of course a field for the question itself. If you plan to have single choice and multiple choice questions, the field question_type will store the difference.

Table Answers:

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
quiz_id INT UNSIGNED FOREIGH KEY to quizzes.id
question_id INT UNSIGNED FOREIGH KEY to questions.id
answer TEXT
result INT UNSIGNED FOREIGH KEY to results.id

The table will have foreign keys to both Quizzes and Questions table. I know giving a key to Questions is logically enough, but I always prefer to have all the relations explicitly given in the table. This gives a lot more clarity especially if you are using ER diagrams.
The "result" column may contain things like A, B, C which will

Table Results:
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
quiz_id INT UNSIGNED FOREIGH KEY to quizzes.id
value VARCHAR
description TEXT

This is the last table you need. It will contain the personality types. And because the Answers table has a foreign key to it, it will be very easy to calculate which result (personality type) has the most answers given.

I'm leaving the PHP or other language implementation of this DB to you. If you want to check such thing in action, check out this php quiz script.

Easy PHP Date Picker

There are many fancy javascript calendars for selecting dates - for example the one that comes as a jQuery plugin is really cool. There are however some disadvantages to using such calendars:

• If you only need a date picker, it's not worth to include all these Javascript libraries and overload.


• These javascript calendars are hard to use from people who don't see well or can't use a mouse


• In admin screens etc., where you may need to manage many dates in rows of records, clicking on the calendars can be much slower than picking dates from dropdown.

In cases like these and maybe more, it's better to create a simple dropdown date picker that will allow the user to select year, month and day (in fact this will be 3 dropdowns).

Let's build such a function:

First we'll build an array of months:

$months=array('','January','February','March','April','May','June','July','August',
'September','October','November','December');

Note that I inserted the first element of the array as empty because I want the month numbers to start from 1 (for January).

Then we'll construct the three dropdowns:

$html="<select name=\"".$name."month\">";
 for($i=1;$i<=12;$i++)
 {
    $html.="<option value='$i'>$months[$i]</option>";
 }
$html.="</select> ";

This was the months dropdown. Did you notice the "name" variable? We will pass it as argument to the function so we can control the name of the dropdowns and have many of them in a page. In very similar way you can create the days dropdown - from 1st to 31st.

$html.="<select name=\"".$name."day\">";
 for($i=1;$i<=31;$i++)
 {
    $html.="<option value='$i'>$i</option>";
 }
 $html.="</select> ";

The years dropdown is just as simple. The only question in it is in what year to start and when to end. Your function can accept this as arguments or you can dynamically create start and end year accordingly to the current date. For example:

$startyear = date("Y")-100;
$endyear= date("Y")+50;

 $html.="<select name=\"".$name."year\">";
 for($i=$startyear;$i<=$endyear;$i++)
 {
   $chooser.="<option value='$i'>$i</option>";
 }
 $html.="</select> "; 

You can add some javascript to increase/reduce the number of days accordingly to the month, but this can be needlessly complicated. It's easier to solve this problem by javascript validation at the time when the form is submitted.

So Here Is The Full Code (For Those Who Don't Get It):

Then put all this code into a function which accepts the argument $name:

function date_picker($name, $startyear=NULL, $endyear=NULL)
{
    if($startyear==NULL) $startyear = date("Y")-100;
    if($endyear==NULL) $endyear=date("Y")+50; 

    $months=array('','January','February','March','April','May',
    'June','July','August', 'September','October','November','December');

    // Month dropdown
    $html="<select name=\"".$name."month\">";

    for($i=1;$i<=12;$i++)
    {
       $html.="<option value='$i'>$months[$i]</option>";
    }
    $html.="</select> ";
   
    // Day dropdown
    $html.="<select name=\"".$name."day\">";
    for($i=1;$i<=31;$i++)
    {
       $html.="<option $selected value='$i'>$i</option>";
    }
    $html.="</select> ";

    // Year dropdown
    $html.="<select name=\"".$name."year\">";

    for($i=$startyear;$i<=$endyear;$i++)
    {      
      $html.="<option value='$i'>$i</option>";
    }
    $html.="</select> ";

    return $html;
}

And use the function as echo date_picker("registration") (for example - "registration" is just a name you choose). The result that will come in $_POST after submitting such form will be in 3 variables:
$_POST['registrationmonth'], $_POST['registrationday'] and $_POST['registrationyear'].

You can easily construct a MySQL date from these variables.

Now think how you can make this dropdown read stored data and pre-select its values accordingly to it (for "Edit record" forms).