September 2000 || Volume 01, Issue 02

In my first column on my database design last month I explained the importance of partitioning a design. I also tried to illustrate the major relationships that are in the system. In this article I will continue my description of three more of the databases in the system and I will explain the importance of the middleware tool that I used called Lasso from Blueworld.

More Partitioning and Relationships

The Faculty Test Database stores the unique reference numbers that form the One-Test-to-Many-Question relationship with it and the Faculty Question Database. Each test has a distinct serial number that is used for tracking inside the system. In order for students and faculty to more easily keep track of the tests though they only see the title of the test given by the professor.

In order to accomadate many different requests for the duration of a test I had to build in several features into the database. The first and simplest was the inclusion of starting Date/Time and ending Date/Time. When a student clicks on a link to take a test the system makes a check against these starting and ending dates and then returns a flag indicating if the test is live or not. If the test can't be taken at that time then a quick explanation letting the user know when it could have been taken or can be taken.

A second scenario that was quickly brought to my attention, is the need to be able to control the amount of time that a student can actually spend on a test. One professor wanted to make his tests available for a long time, but once a student starts a test they only have a preset amount of time to complete it. This was a difficult problem that took some serious thought to get a solution to.

The initial problem with the time limit to taking a test is fairly easy to understand. In the case of a start date and end date the times are static and independent of the student. But in the case of the test time limit we must track the starting time and the allowed ending time of each student on each test individually. This required a much larger investment in architecture and because of that I think this is a good time to introduce the diagram with the 3 important "Test-Taking Databases"

As you can see from the diagram above the system allows for students to take a test multiple times by merely creating multiple Student records referring back to the same Test. Think of it as having multiple answer sheets for the same test but they can be filled out completely independent of each other. One obvious problem with this setup is to find out what is the best score we have achieved on a test. Since information only flows from the Test Database to the Student Records, the test database can not hold this information. The Logging Database was created for this purpose. It sifts through all the data in every student record that applies to one particular test and comes back with the best score. It also provides a place to store the test starting and allowed finishing time for a test. Then when we want to check to see if a student can continue to work on a test, we simply check the Logging Database and return the number of minutes left to work on the Test or a zero if there is no time left.

Here is an example to illustrate this:
John is a student for Professor X's class on Mutant Trivia. Since Professor X was trying to challenge John, he decided to only allow him 5 minutes to complete the test. Now John isn't quite as fast as he should be and he only made it to the 4th question by the time his 5 minutes were up. How do we know his 5 minutes were up? When he clicked on the original test link we preformed the following checks.

As he answers each individual question, with a click of the submit button he triggers the following checks to be preformed.

In this example, when he went to submit the answer to the fourth question we passed the first check of allowing the student record to be updated because he hasn't completed the test yet, but he fails the second check because the devious Professor X set the time limit to 5 minutes.

(As an aside: see if you can answer all the questions within 5 minutes)

Now one thing that may be obvious to the especially astute readers is that we have to do a lot of database accesses. According to the above diagram we need to make at least 3 "checks or updates" with every question and 3 when we first start the test. The above diagrams also do not include the User Authentication which happens on every page and a database access for each question to bring up the question detail.

So right now, to complete a 10 question test we need to make 53 Database "checks, updates or creations". 3 to start the test, 1 authentication for each question, 1 detail access for each question and 3 checks/updates for each answer submition. Obviously by any measure this is a lot and is a potential preformance bottleneck.

About a year into the project I decided I would spend some time on trying to fix this problem. The reason I didn't tackle it sooner is that the solution I can up with requires a lot more work up front (or when the Professor is creating the test) to try and reduce the load when each student takes the test. The solution that I came up with is to use a Cache file.

Now in this case, the Cache file is simply a text file that contains a large portion of the Test Data that is contained in the Test Database. It doesn't have everything but it has what is relevant to make the operation of taking the test by the student much faster.

So what exactly does it eliminate? Well you remember just a couple of paragraphs back we discussed the 53 database actions that are involved in completing the small 10 question test. The cache file reduces that number down to 42. We eliminated the need to check the database for the starting and ending Dates as well as for all the question details. Now you say but you only reduced by about 20%... How does that even help in the long run?

That is a tougher question to answer but let me first bring in a new piece to the System. In beteen the web server and the database we have what is called a middleware tool. The middleware tool serves to allow us to accept user input (through forms), query the database, and then return custom html based on the data returned.

The middleware tool that I used is called Lasso from Blueworld. I tried out many of the competing products and found Lasso to be the fastest and the easiest to produce results with. Lasso uses a tag like metaphor that is quickly understood by anyone who has hand-coded html. (Is there any other way to really do it? ;-)

As one of its many abilities Lasso offers the feature of writing to and reading from files. This was the key to the cache puzzle that makes the reduction noted above possible.

So getting back to the difficult question of the performance of taking a test, the important thing to stress is that Lasso is much quicker at retrieving the data from a file than from a FileMaker Pro Database. This is not true with all Databases, and in fact it probably isn't true with any other kind of database but since I had already designed the backend in FileMaker Pro I was stuck with the consequences. So you ask how much faster was it? Without the cache file, depending on network performance it was typically between 1-2 seconds. Now with the cache file that is down to about .5 second. In the web world 1 second is the difference between what the user thinks is instantaneous and starting to take too long (web users have no patience.. keep that in mind! :-)

In my next column I will expand on the methods I use in grading a test and the systems that I have developed for displaying and organizing all the grades for all the tests. I also will go into more detail with the role that Lasso plays in the solution as well as including some sample code.

If you have any suggestions or comments please email me Randy Overbeck at randy@macosjournal.com.

Randy Overbeck - randy@macosjournal.com
Randy's Page