Creating online test

[cmccully]

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

[neylitalo]

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. :)

[cmccully]

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-
[b][u]john[/u][/b]  [b][u]betty[/u][/b]  [b][u]bobby[/u][/b]  [b][u]sue[/u][/b]

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-
[u][b]student_id[/b][/u]  [u][b]student_name[/b][/u]
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:

[u][b]question_id[/b][/u]  [u][b]article_id[/b][/u]  [u][b]question_number[/b][/u]  [u][b]my_question[/b][/u]
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

[neylitalo]

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?

[roopurt18]

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!

[cmccully]

neylitalo

I was refering to the First Normal Form of database normalization, see here: http://en.wikipedia.org/wiki/Database_normalization#First_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

[neylitalo]

ah, ok - it all makes sense now.