Database Reference
In-Depth Information
Let's take a look at what makes this a bad design and how that affects
data retrieval. The first four columns are OK; they store information about
the question, such as the test where it appears and the question's category.
The problems start to become obvious in the next five columns. Columns
a, b, c, and d store the text that is displayed to the user for the multiple-
choice options. The Answer column contains the correct letter or letters
that make up the correct answer. How do you determine the correct an-
swer for the question? It's not too hard for a human to figure out, but com-
puters have a hard time comparing rows to columns.
The other problem with this table is that there are only four options;
you simply cannot have a question with five options unless you add a col-
umn to the table. When delivering the test, instead of getting a nice neat
result set, I had to write code to walk the columns for each row to get the
options for each question. Data retrieval ease was not one of this table's
strong suits.
It gets even better (or worse, depending on how you look at it); take a
look at Figure 1.6. This is the table that held the students' responses to
the questions. When you are finished rolling on the floor laughing, we will
continue.
This table is an example of one of the worst data modeling traps you
can fall into: using columns when you should be using rows. It is similar to
the problem we saw earlier in Figure 1.3. This table not only contains the
answer the student provided (in a string format)—I was literally storing the
letters they picked—but it also has a column for each question. You can't
see it in the figure, but this table goes all the way up to a column called
Ques61. In fact, my application dynamically added columns if you were
creating a test with more questions than the database could support.
To be honest, I don't remember how I made any use of this data. The
application is a bunch of spaghetti code that I can't even follow anymore.
That's enough self-deprecation for now, but I wanted to show you how a
bad model can make data retrieval very difficult.
Search WWH ::
Custom Search