The Quandary of the Single Table Web Site

Posted on December 15, 2005

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.

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 an ini 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.

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 enourmous amount of functionality you could build with these. See this email exchange between he and I for some of the possibilities.

(I will admit to a crude hack for data I maintain myself: using PHP’s ini 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?

This is item #253 in a sequence of 357 items.

You can use your left/right arrow keys or swipe left/right to navigate