The Quandary of the Single Table Web Site

What do you do with sites that need a single table of data updated? These are sites which are totally static, except for this one thing…

For instance, we have a client for which we built a static site. But they have a page which lists all the locations where you can buy their stuff. This list changes a lot. They don’t want to keep calling (and paying) us to update this, so they want an interface where they can manage the list themselves. The list couldn’t be more simple – if you implement in SQL, it’s a flat, five-column database table.

This happens a lot – the sites are all static except for one element, be it a list of locations, events, the ubiquitous “Latest News,” etc.

Some common themes

1. The table is self-contained – it has no foreign keys to any other table (indeed, it’s the only table in the database). It is an island of data unto itself.

2. The “objects” often extend past the “page” or “post” model (more fields are needed), so this precludes using a blogging system.

3. You can do the display logic in some other manner. All you need is a way for the users to administer the data.

4. Access is binary. One password gives the bearer access to the whole thing.

So what do you do? The idea is to do something as simple and fast as possible, with as little repetitive coding as you can get away with.

• The most obvious solution is to build a little interface, but this goes against our stated goals. You have to program and debug a bunch of stuff for one little deal, which is a drag. Writing the same authentication and validation crap over and over gets a little tiring.

  BUT, if you generalize it and re-use it, then you’re in better shape. Instead of hard-coding the fields, pull them out of a config file that contains their SQL field name, display title, data type, interface type, validation regex, etc. You’d need to refine it a bit, but you could dial it in tightly.

• Related to the above, there are PEAR modules that strive to do just this. DB_DataObject with HTML_QuickForm, for instance. Propel for PHP5 is good too. But these are generally overkill, and they require a lot of code included on the backend.

• I have great luck with PHPRunner and ASPRunner – we’ve talked about both of them before. These are little code-genning tools that spit out set of files to keep a table updated.

  The latest versions of these two tools are fantastic – they have WYSIWYG support and file uploads. They’re so handy, in fact, that it’s just as easy to have them manage 10 tables as it is to have them manage one.

  Additionally, I’ve been in contact with the developer, and he’s planning on developing an event model as well, so there’s an enormous amount of functionality you could build with these.

• How about phpMyAdmin? Do you just let them loose with that?

• Development frameworks like QCodo or Ruby would seem to make the maintenance of single tables pretty simple as well.

• Do you even do it in a database? If you’re doing addresses (like in my example), could you give them a text file format and limited FTP access? Name on one line; then address on the next; then city, state, and zip; then skip a line before the next one.

  (I will admit to a crude hack for data I maintain myself: using PHP’s config file format. I started using the parse_ini function, but then switched to one someone had written that was a little more robust. It gives you very simple access to a two-dimensional data set.)

So, how do you handle it?