Jump to content


Photo

Creating online test


  • Please log in to reply
31 replies to this topic

#21 Zane

Zane
  • Administrators
  • Advanced Member
  • 4,134 posts

Posted 03 October 2006 - 06:40 AM

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

questions table
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

answers table
a_id     a_body                                                
  0     "2006"
  1     "Hypertext Preprocessor"
  2     "4"
  3     "Bill Gates"
  4     "George Washington"

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

btn_donate_SM.gif Want to thank me? Contribute to my PayPal piggy-bank
 

172938.png

#22 Daniel0

Daniel0
  • Staff Alumni
  • Advanced Member
  • 11,956 posts

Posted 03 October 2006 - 01:35 PM

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.

#23 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 03 October 2006 - 04:42 PM

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


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


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.
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#24 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 03 October 2006 - 04:50 PM

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
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#25 Daniel0

Daniel0
  • Staff Alumni
  • Advanced Member
  • 11,956 posts

Posted 03 October 2006 - 05:12 PM

That is an excellent way to explain it.

#26 cmccully

cmccully
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 04 October 2006 - 01:52 AM

Oh the embarrassment. :o

I knew when I originally thought about this problem that having a table with hundreds of fields was not practical. However, I incorrectly interpreted the advice you guys gave me and started down that road anyway. Of course this scenario would waste space, be way to complex to implement and be completely inflexible.

I see now that the ID numbers are my key to reconstructing each part of any test. As I read your responses I am beginning to remember the process of normalization, something I should have apparently brushed up on before starting this project.

You guys have saved me from many headaches with this project and a fortune in Excedrin and Rolaids. Thank you all for your contributions, especially neylitalo. If you are not an instructor Sir, you should be.


cmccully

#27 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 04 October 2006 - 02:17 AM

Three cheers for analogies!

And thank you for your vote of confidence, cmccully, it's good to know that all that typing was good for something. :)
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.

#28 cmccully

cmccully
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 04 October 2006 - 06:56 AM

Oh my God, what was I thinking!


I have been thinking about what we have been discussing and I would like to talk about some of the mistakes I was making. Partly to organize my thoughts and partly to help any other newbies that were brave enough to follow along this far.

The first mistake that I made violates one of the core principals of relational databases. Fields in a relational database table should be related to each other but not be the same. This is what neylitalo so patiently explained. Let's look at an analogy that illustrates this:

Let's say we want to store information about students in a classroom-
john  betty  bobby  sue

These fields are all related in that they are all in the same class. However, the problem is that all of these fields contain the same type of data, they are all students and so should not be in individual collumns but individual rows like so-
student_id  student_name
1                  john
2                  betty
3                  bobby
4                  sue

In my project I was trying to layout the data as in the first example.


The second mistake is that I was trying to organize or relate the data linearly rather than letting the relational databse engine do its job. I was blobbing all the test questions together on the same row instead of letting each question reside in a seperate row. My thinking was that all the questions for a given test would be accessed at once in basicly a linear fashion. This of course is not how relational databases work. It really does not matter if the individual questions for a given test are stored next to each other or are hundreds of rows apart. It is their ID numbers that will link or relate them back to our particular test that we are interested in.

One thing that helped lead me down the wrong road is that I want to preserve the order of the individual tests. In other words I want to be able to designate question #4 on test #6 to always appear as the forth question. That is why I was trying to store the whole test as a big blob of data rather than as individual elements. This is of course easilly fixed by adding a question number field to the table. The table below shows how data for a questions table may be stored in the database:

question_id  article_id  question_number  my_question
1                  2              6                            What is the capital of California?
2                  6              4                            What color is the sky?
3                  2              1                            What is the capital of Kansas?

The data appears to be all mixed up but the ID numbers relate the question to what article it belongs too and what order the questions should be displayed in.


Again, I want to thank everyone who was patient and helped me cut down enough trees that I could finally see the forest.




cmccully

#29 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 04 October 2006 - 07:07 AM

I'm not entirely sure what you mean by "The first mistake I made violates [...] Fields in a relational database table should be related to each other but not be the same." Fields in a table should most certainly be related (in the less strict sense - they belong to the same thing), and they should most certainly not be the same, but I don't see how your layout violated that. The problems I saw with your original layout were

a) Information about completely different objects were contained in one table, and
b) Columns were being used where rows & id numbers would work much better.

And it appears that you understand what you're doing now, so I'm wondering if you meant something else - can you explain?
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.

#30 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 04 October 2006 - 10:18 AM

As long as you insert the questions in the order you want them to appear, you can use the auto incrementing ID column of the questions table to order them upon retrieval.  Not saying this is the best approach though!
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#31 cmccully

cmccully
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 04 October 2006 - 05:34 PM

neylitalo

I was refering to the First Normal Form of database normalization, see here: http://en.wikipedia....rst_normal_form

I didn't explain myself clearly, what I was trying to say is that each question for example should be on a seperate row and not globbed together, all on the same row.


cmccully


#32 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 04 October 2006 - 07:10 PM

ah, ok - it all makes sense now.
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users