Usable Data
By Hal Straus The Washington Post
Straus provided this handout at the National Computer-Assisted Reporting Conference held in Boston this past March.
Three Ways to Build Compelling Web Databases
Web databases aren't CAR databases. And the fact that you need different software to deliver them is only one of the reasons. Here are three un-CAR things you can do to keep your work from landing on the Web with a loud thud.
1. Use the Right Software
Perl and VBA/Active Server pages are good options for small and medium-sized sites. They don't work well when your volume is high (10 concurrent queries, 100,000 page views of a given feature). The problem: Forking.
You can speed things up a lot (and minimize forking) by building a Unix system from scratch. If you're AOL or Yahoo, with lots of programmers and a "tech" culture, that's a good plan.
If you're a newspaper or local broadcast Web site, a better solution is to go out and buy a Unix-based Web Application Server. The biggest names are Sapphire/Web and Net Dynamics. At Washingtonpost.com, we decided those cost too much. We bought Oracle Application Server and Netscape Application Server. From a database editor's point of view, they're about as easy to use as ASP or Cold Fusion, but they're faster.
2. Forget All That Relational-Database Nonsense
This is simple stuff: You're probably already doing it. But if you're not, you can really speed up your Web databases and make them a lot more pleasant for users if you break some database design rules.
Don't use joins. Joins work fine when you're using Access to do a CAR project. They're recommended in all the relational-database books. But even if they're perfectly indexed and you're using grossly overpowered hardware, they take too long on the Web.
Put data for different Web pages in separate tables. Then, store the tables in system memory or put each of them on different hard drives. If possible, put the indexes your queries use on different drives as well. For example, if you're posting a school's database and have the test scores on a different page than the principal's name, make a different table for the scores that only has the scores and the basics that you need on that page (school name, etc.).
Try to avoid ODBC drivers to access your database. Native drivers are faster.
3. Keep It Simple
Most of us in the news business got into using databases to produce stories. We're used to mining them to death to make sure that we've gotten every last bit of information out of them, then distilling the results down to understandable chunks when we write our stories. Or we are letting another reporter or an editor do it. That's great, but on the Web, you'll inevitably run into one big problem: 2.5 seconds.
Our server logs tell us that site visitors spend less than three seconds on an average interior Web page, which is where your database will end up. If you want Web users to actually explore your databases, you've got to do a few simple things:
Design your features so users can go somewhere productive with a single click. You can give them lots of radio buttons and text boxes and search options, but you can't count on them because 2.5 seconds isn't a lot of time. Example of what not to do: The General Social Survey http://gort.ucsd.edu/gss/extract.html. Example of what to do: The Washington Post Poll Vault http://www.washingtonpost.com/wp-srv/politics/polls/vault/vault.htm.
Number-heavy pages don't work any better on the Web than they work in print, even if every number is a link. Example of what not to do: The Toxic Release Inventory Scorecard http://scorecard.org/env-releases/us.tcl#major_chemical_releases. Example of what to do: The Seattle Times School Guide http://texis.seattletimes.com/schoolguide.
Hal Straus can be reached by e-mail at Hal.Straus@washingtonpost.com.