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.

64 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. 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
  4. Good post.... thanks for this
    ArohaTech

    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. Thank you so much for sharing this. I appreciate your efforts on making this collection.
    Hire PHP Developers

    ReplyDelete

  8. 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
  9. 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
  10. 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
  11. Nice article
    Thanks for sharing the informative blog.

    Linux Training In Bangalore

    ReplyDelete
  12. 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
  13. This comment has been removed by the author.

    ReplyDelete
  14. 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
  15. This comment has been removed by the author.

    ReplyDelete
  16. Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
    MEAN stack training in chennai
    MEAN stack training in bangalore

    ReplyDelete
  17. I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details.
    Hadoop Training in Chennai

    ReplyDelete
  18. Some us know all relating to the compelling medium you present powerful steps on this blog and therefore strongly encourage contribution from other ones on this subject while our own child is truly discovering a great deal. Have fun with the remaining portion of the year.
    Hadoop Training in Chennai

    ReplyDelete
  19. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Devops Training in Chennai

    Devops Training in Bangalore

    AWS Training in chennai

    AWS Training in bangalore

    ReplyDelete
  20. I am really very happy to find this particular site. I just wanted to say thank you for this huge read!! I absolutely enjoying every petite bit of it and I have you bookmarked to test out new substance you post.
    python training in chennai
    python training in chennai
    python training in Bangalore

    ReplyDelete
  21. The post is written in very a good manner and it entails many useful information for me. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept.
    python training in chennai
    python training in chennai
    python training in Bangalore

    ReplyDelete
  22. This looks absolutely perfect. All these tiny details are made with lot of background knowledge. I like it a lot. 
    java training in chennai | java training in bangalore


    java training in tambaram | java training in velachery

    ReplyDelete
  23. Fantastic work! This is the type of information that should follow collective approximately the web. Embarrassment captivating position Google for not positioning this transmit higher! Enlarge taking place greater than and visit my web situate
    angularjs Training in btm

    angularjs Training in electronic-city

    angularjs online Training

    angularjs Training in marathahalli

    angularjs interview questions and answers

    ReplyDelete
  24. It seems you are so busy in last month. The detail you shared about your work and it is really impressive that's why i am waiting for your post because i get the new ideas over here and you really write so well.
    angularjs-Training in pune

    angularjs-Training in chennai

    angularjs Training in chennai

    angularjs-Training in tambaram

    angularjs-Training in sholinganallur

    ReplyDelete
  25. Thank you for sharing your article. Great efforts put it to find the list of articles which is very useful to know, Definitely will share the same to other forums.
    best openstack training in chennai | openstack course fees in chennai
    java training in chennai | primavera training in chennai

    ReplyDelete
  26. Does your blog have a contact page? I’m having problems locating it but, I’d like to shoot you an email. I’ve got some recommendations for your blog you might be interested in hearing.
    AWS Training in Chennai |Best Amazon Web Services Training in Chennai
    Best AWS Amazon Web Services Training in Chennai | AWS Training in Chennai cost
    No.1 AWS Training in Chennai | Amazon Web Services Training Institute in Chennai

    ReplyDelete

  27. It seems you are so busy in last month. The detail you shared about your work and it is really impressive that's why i am waiting for your post because i get the new ideas over here and you really write so well.

    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training
    Selenium training in bangalore

    ReplyDelete
  28. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end. 
    microsoft azure training in bangalore
    rpa training in bangalore
    best rpa training in bangalore

    ReplyDelete
  29. Wonderful bloggers like yourself who would positively reply encouraged me to be more open and engaging in commenting.So know it's helpful.
    Best Devops online Training
    Online DevOps Certification Course - Gangboard
    Best Devops Training institute in Chennai

    ReplyDelete
  30. You are doing a great job. I would like to appreciate your work for good accuracy
    Regards,
    Best Devops Training in Chennai | Best Devops Training Institute in Chennai

    ReplyDelete
  31. Very Informative post you have done. I like the way you describe the topic.
    Devops Training in Chennai | Devops Training Institute in Chennai

    ReplyDelete
  32. The site was so nice, I found out about a lot of great things. I like the way you make your blog posts. Keep up the good work and may you gain success in the long run.

    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

    ReplyDelete
  33. I’m just always astounded concerning the remarkable things served by you. Some four facts on this page are undeniably the most effective I’ve had.
    Data science Course Training in Chennai | No.1 Data Science Training in Chennai
    RPA Course Training in Chennai | No.1 RPA Training in Chennai

    ReplyDelete
  34. A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. בטחון עצמי

    ReplyDelete
  35. You will be giving out very useful blogs to learn on blockchain online training

    ReplyDelete
  36. Really very happy to say, your post is very interesting to read. I never stop myself to say something about it. You’re doing a great job. Keep it up…

    Get Web Methods Training in Bangalore from Real Time Industry Experts with 100% Placement Assistance in MNC Companies. Book your Free Demo with Softgen Infotech.

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

    ReplyDelete
  38. Thank you for posting informative insights, I think we have got some more information to share with! Do check out Python Training In Chennai and let us know your thoughts. Let’s have great learning!

    ReplyDelete
  39. http://programmingtutorialsscript.blogspot.com/

    ReplyDelete
  40. Reach to the best Data Science Training institute in Chennai for skyrocketing your career, Infycle Technologies. It is the best Software Training & Placement institutes in and around Chennai. that also gives the best placement training for personality tests, interview preparation, and mock interviews for leveling up the candidate's grades to a professional level.

    ReplyDelete