Piloting packets for this year are starting to come in and the data from these packets needs to be entered into the items utility. Because the questions in the packet have changed from previous years, and due to the rigid structure of the piloting data entry form and data tables, I decided to rewrite the data entry form. The new format allows more flexible on the front-end without requiring modification of the back-end. In addition I modified the way PHP interacts with the packet data from MySQL by utilizing a structured array to hold the data.
Updating the interface
First up is the form organization. Previously the forms were designed, I believe, with the researchers in mind. The most important data entry sections were placed at the top of the page with everything else following. Once we started using temps to enter the data, however, this format proved to be a hindrance to efficiency. So to improve efficiency I regrouped the form into logical sections: student responses, student demographics, and researcher notes. Plus, to allow for easier scanning I organized the student responses section so that it resembles the paper form.
In addition to organizing the form more logically, I wanted to make portions of the form collapsible. Since a lot of the data entry is performed by temps there is no reason for them to have to wade through the research notes, which are at the top of the page by request of the researchers. Brian W. has commented on the utility of Dreamweaver’s Spry widgets so I thought I’d give them a try. I must admit that they work quite nicely and are easy to implement, and I found just what I needed in the Collapsible Panel widget. The benefit of using the panels is that I could position the researcher notes at the top of the page without significantly hindering the ability of the temps to access the student responses by having this panel closed by default.
The final interface modification was a small script that makes it possible to deselect radio buttons. The main purpose of developing this feature was to streamline the form which, in previous revisions, had an extra option to clear the selected response.
Improving the foundation
As I mentioned, while the form is still hard-coded (each question coded by hand) I did lay out the foundation for a more dynamic approach. First I redeveloped the table that stores the data related to a packet. I reused the packets and packet_items tables, but created a new table called packet_data that stores only the question number, answer, and any comments. The benefit here is that forms with varying numbers of questions can be stored in this one table. I also moved the student meta data into a separate table, a design decision more in line with database normalization practice.
Another modification I made was to use a structured array to define the questions in the packet. While this has not been fully developed as of yet, I have used it to significantly simplify the script that saves the data. Rather than specify each question and corresponding HTML form element and writing the query to insert that information in the database I designed the PHP to loop through the array and generate the SQL code on the fly.
Finally, I used another structured array to hold data related to the packet, packet items, and students while generating the packet data entry page. I do not utilize any methods to automate the form generation at present, though there is little to prevent this from being developed. One reason I decided to code the form by hand is to allow for a level of customization that is difficult to accommodate when automating the visual side of things. Even so, utilizing structured arrays allows for more consistency between PHP pages.
Data from years prior has not yet been ported into the new structure. I do not expect this task to require too much work, but I am not sure when I’ll be able to perform the task. At the very least this will be part of the redevelopment taking place around subversion if not sooner.
To fully accommodate the data in the utility I’ll need to also update the summary tables, the school report generator, and packet data export. These updates will need to be completed fairly soon in order to support the item review that will begin taking place fairly soon.
One thought on “Items Utility: Piloting Data Entry Updates”
A quick look at the database after a few days of data entry and I was beginning to think may have to reconsider the table structure. Data for 1,030 test items had been entered (packet+student+item). That results in 19,570 rows in the table weighing in at 61.2MB.
For comparison, the previous table structure contains 60,864 rows and weighs in at 13.8 MB. Since a row in the old table correlates with all the data for a test item it was significantly more efficient in terms of storage. The new table, in order to accommodate the same number of items and growing at the current rate, would take up 60 times as much space.
After some research I found that that the trouble has to do with the format of the row. The current format is static (fixed) length, but for variable-length data a dynamic row is better suited for space considerations. Most of our tables use the dynamic format. Updating this table to dynamic cuts the size of the table down by a factor of 100.
There are some drawbacks to dynamic rows, however. I’ll be adding some documentation regarding this setting to the upcoming post “Optimizing MySQL Table Structure and Queries.” In the meantime you can find out more information about this setting from the MySQL Reference Manual. See 13.1.3. MyISAM Table Storage Formats.
Comments are closed.