2/22/2010

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.

32 comments:

  1. Tags,functions and nice logic are big point of your blog.greatttttt

    ReplyDelete
  2. Is it possible to share a sample DB values for each of these tables? That would make this tutorial complete and best. This is the only tutorial on this subject and your help is really appreciated.

    ReplyDelete
  3. Rust Preventive oil CNC lubricant is prime manufacture and marketer of industrial lubricants, knitting oil, semi synthetic cutting oil, rust preventive oil, metal working fluids, hydraulic oil and CNC cutting oil.

    ReplyDelete
  4. Drawing App :MyBrushes is the Best drawing app for ipad to paint on infinite canvas and PLAYBACK each drawing stroke on iPad, iPhone. Download best drawing app for iPad Now.

    ReplyDelete
  5. How can i change the image on the banner and Main OG Banner? Every time i change the image it will go back to the original file.....Web Design Training

    ReplyDelete
  6. Thanks for a great information in your blog.I have read all the post of your blog.Great work on PHP

    ReplyDelete
  7. Thanks for sharing great information in your blog. Got to learn new things from your Blog . It was very nice blog to learn about Php.
    http://thecreatingexperts.com/php-training-in-chennai/

    ReplyDelete
  8. Thank you so much for sharing this. I appreciate your efforts on making this collection.
    Hire PHP Developers

    ReplyDelete
  9. I just see the post i am so happy the post of information's.So I have really enjoyed and reading your blogs for these posts.Any way I’ll be subscribing to your feed and I hope you post again soon.

    PPC Services in Chennai

    ReplyDelete

  10. What an awesome post, I just read it from start to end. Learned something new after a long time.


    SAP FICO Training in Chennai

    SAP SD training in Chennai

    ReplyDelete
  11. My friend Suggest me this blog and I can say this is the best blog to get the basic knowledge.Thank you so much for this Selenium Training in Chennai

    ReplyDelete
  12. Wow!! What a great blog!! I really liked your article and appreciate your work.I have found this article while searching on internet and I would recommend this to everyone. PHP online tutorial

    ReplyDelete
  13. it is very nice article and good post thanks for sharing this article to us
    i really like this article because i got good info about this article thanks for haring this article to us .best regards.

    sap netweaver online training

    ReplyDelete
  14. Well explained. Got to learn new things from your Blog on Appium.Appium training in chennai

    ReplyDelete
  15. This is a great post. I like this topic.This site has lots of advantage.I found many interesting things from this site. It helps me in many ways.Thanks for posting go to this site school branding uk

    ReplyDelete

  16. Thank you for sharing such a nice and interesting blog with us. Hope it might be much useful for us. keep on updating...!!
    seo company in india
    digital marketing company in india
    seo company in chennai
    digital marketing company in chennai

    ReplyDelete
  17. Nice article
    Thanks for sharing the informative blog.

    Linux Training In Bangalore

    ReplyDelete
  18. Webtrackker technology is the best IT training institute in NCR. Webtrackker provide training on all latest technology such as Php training. Webtrackker is not only training institute but also it also provide best IT solution to his client. Webtrackker provide training by experienced and working in the industry on same technology.Webtrackker Technology C-67 Sector-63 Noida 8802820025

    Php training institute in indirapuram


    Php training institute in Noida


    Php training institute in Ghaziabad


    Php training institute in Vaishali


    Php training institute in Vasundhara


    Php training institute in Delhi South Ex

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge.
    PHP Training in Chennai

    ReplyDelete
  21. Really Good blog post.provided a helpful information .keep updating...
    Digital marketing company in Chennai

    ReplyDelete
  22. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  23. Really Good blog post about designing database for personality quiz.provided a helpful information.I hope that you will post more updates like this.
    MSBI Training in Chennai

    ReplyDelete