Tracking School Volunteers

Some of my children attend a charter school. The school asks — but by law cannot require — that each family with students at the school volunteer at least 30 hours during the school year. The school has opportunities for service in administrative tasks, the classroom, field trips, the playground, the lunchroom, technical work, maintenance, moving furniture, handling special events, etc.

Two years ago, I designed a database that the school used to track time volunteered by family members. While the idea was to provide school administration with information about how each family was doing on its suggested volunteer hours, it turns out that it is also important for the school to track volunteers and the time they donate for legal purposes.

When I built the database, I used Microsoft Access because I already had it and it is a reliable single-user database. Volunteers recorded their time on sheets at the school. The data was transcribed into the database by a volunteer on a home computer.

This method worked, but it did not provide current information. There was a time lag between the entry of time on paper and the entry of data in the database. By the end of the school year, I had a better idea of how the database was actually used. I realized that I would have designed some things differently were I to do it again.

Last year, the opportunity to redesign the database came along. There was a desire to have the data in house at the school instead of on somebody’s home machine, since it included personal information. Also, it was felt that it would be better for volunteers to enter their own data live instead of having it transcribed by somebody else. Then administrators could get an up-to-the minute current report anytime.

My first thought was to put the database on the school’s server so that it could be accessed from any computer on the school’s intranet. You can do this with a MS Access database, but you really don’t want to. MS Access is not really robust enough to handle multiple simultaneous user connections.

Also, this method would have required a copy of the MS Access software for each client machine. That runs into real money. Our charter school minimizes administrative expenses, centering its resources chiefly on factors that directly impact the students instead. The budget didn’t allow for buying a bunch of copies of MS Access.

I could have rewritten the application using the MySQL database, PHP scripting language, and Apache application server — open source tools that are available for free. But that’s a very roll-your-own kind of process. I just didn’t have the time to do it.

In the end, I resorted to rebuilding the volunteer tracking database using MS Access 2007. The school purchased one copy of the application and put it on a computer in the office that is dedicated to that purpose. It is not even connected to the school intranet. Volunteers step into the office and enter their hours. Administrative personnel back up the data regularly, keep the family data up to date, and generate reports as necessary.

When I restructured the database, I had to build an entirely new user interface. It’s one thing to have a single person responsible for all data in a database, and it’s quite another to allow input by hundreds of people that rarely touch the system. I designed the interface to be as simple and as error proof as possible.

In my line of business, this is referred to as “dummy proofing.” While this may sound derogatory, it is important to understand that software developers are users as well. We know that we are too are sometimes prone to being the “dummy” when interacting with computer interfaces. “The problem with constantly improving the dummy proofing of our applications,” a colleague recently quipped, “is that the universe responds by continually producing better dummies.”

This system worked well enough last year that I was able to copy it this year. I cleared the data from the copied database, loaded new teacher, student, and family data provided by the school, changed the titles of a couple of screens, and put the application into service with only a few hours of work.

I would still like to rebuild the database and application using open source technologies. But I suppose that, like everything else, that project will wait until it rises from being nice to have to being necessary.