The Quandary of the Single Table Web Site

By Deane Barker 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.

  • 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 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.

  • Related to the above, there are PEAR modules that strive to do just this. DBDataObject with HTMLQuickForm, 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 enourmous amount of functionality you could build with these. See this email exchange between he and I for some of the possibilities.

  • 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 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?

Gadgetopia
What This Links To
What Links Here

Comments

  1. I usually write a little desktop app (usually in REALbasic) to talk to the database and give them that. Quicker and easier to write and debug than a web app, and usually quicker for them to use.

  2. you could install a very simple CMS/blogging system – i’m thinking WordPress here – and just integrate a page or two into the theme for the rest of your site. Then let them log into WordPress and edit pages or stories in there.

  3. you could install a very simple CMS/blogging system – i’m thinking WordPress here

    This is great for things that fit that data model, but what about things that don’t? What if they want to maintain a list of Supreme Court cases, complete with about 10 extra fields of data.

    (I can always manage to bring this topic up, can’t I?)

  4. You could use phpcodegenie for building a crud interface and it’s done with a little styiling (if you want to).

    or maybe qcodo, i saw the example video and it seems to generate code from the tables themselves.

    however, code generation it’s the way to go for.

  5. Django with its auto-generated admin interface might be a good solution. It’s at http://www.djangoproject.com, and if you skim pages 1 and 2 of the tutorial you’ll see it in action. I’m using it for sites that non-technical users need to keep updated, and it’s saved me tons of hours writing CRUD pages. It can be kind of a pain to config the webserver, though.

  6. 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.

    “You are not authorized to view this page”

  7. I would get over the “I need a database for any kind of active content”

    I would go for a text file and plug it somehow (name the method) in the required page.

    How they want to maintain it is the key point.

    On the easiest (and crappiest) way would be to have a place holder on the page and they can export their own tables from Excel or Word to html and upload it to the server.

    • From Excel to CVS and process it with PHP leaving a nice formatting
    • A single table maintenance page
    • XML and XSLT
    • ….

    Not a big deal.. depends on how much developer time can afford…

  8. Sounds like that application falls a bit outside your stated goals here.

    No it’s not. One table, several columns (plaintiff, defendant, year, ruling, vote, who wrote the opinion, etc.), many rows. This is exactly what I’m talking about.

    Yes, you can get much more complicated with it, but — for the purposes of this example — let’s not.

  9. A simple approach for small amounts of data is to store the PHP array using PHP’s serialize, and then unserialize it as needed, store it in session. when the session times out the array goes away.

    Everyone is going to yell when I say this, but if there are not a huge number of records you could also use XML. This presumes that the developer knows there way around XML, XSL, XPath, XQuery; which I do, so no problem.

  10. Yeah, PHP serialization is handy. But, this just addresses the data storage. How about the interface? No matter how you store the database, you still have to build an interface.

  11. When I’ve had to do this sort of job, normally I’ve just saved the data in a text file either using XML (if I had a parser on hand or felt like throwing a small one togeather) or a standard “config-style” format. The interface, then, is very simple: a simple form which has the fields to update and a password box for simple authentication. The password is hardcoded (and encrypted) into the PHP file that updates the text file. That PHP file doesn’t care what the fields are – it just takes the field name, takes the content, and uses that to construct the data file, without caring what they are (although wit enogh security to make sure that we’re not getting any nasty stuff sneaking in). The whole job normally takes about 15 minutes to code – maybe 30 if I haven’t had my caffine yet.

    I wouldn’t bother with a database, as it is too much like overkill, as is a complex authentication system. And I like phpMyAdmin, but I would never let most of my clients near it. :)

  12. Several Wiki systems allow you to create and publish a static site from wiki editable contents . (For example, twiki has a plugin for this.) But installing a wiki at customer site is not usually an option – especially when all they want is a static site.

    However, website provider could use wiki to produce the site and create a static version. With some additional enhancements, this static version could also include some standard HTML interfaces (along with relevant scripts) to update structured parts of web pages.

    Such a publishing feature would further enhance value of wiki systems…

  13. I should add – a few times, I’ve stord the data directly in the HTML file. By using specialised comments tags, you can parse the HTML file to grab the data. The have a PHP interface (normally a single file) allow editing of the HTML page. It is a tad odd, but it has the advantage that clients can edit the HTML in Dreamweaver if they want, and so long as the comments remain intact the interface will still work for updating. You also don’t have to manage a separate data file, and the page doesn’t need to be parsed when requested. It was probably a dumb idea, but it was fun, especially when extended dramatically, and worked well pre-PHP.

  14. I liked Deane’s TXT file solution so far; im not a fan of the complex wiki&wp hacks.

    Since you seem to talk about an application that you will want to distribute to other customers as well, I’d think of the features through the big picture eye. So far we are yet on a need to know basis, I can say that on the client side I would simply build a caching script; that caches HTML coming from a centralized storage and maintenance engine; located on your own website. On your side, a small general application ( authentication via http headers f.e.) that allows fields definition, field value definition, even rendering options via a small template system (editable as well). This way you have 100% control over new fields, maintenance crap, validation and user control. The local cache has to be independant written, so that if you website is down, users of your clients see old content until 1st refresh is possible. You could even push update times server side if you do it right.

  15. It seems like the most simple approach would be to ftp the data file (excel or csv) to specific locations in server, and access a “update” URL that runs update script and returns a single “updated” message. This script, run from a URL, shouldn’t require password. It will only do full job only if it detects changes in data file. It should be a very standard solution – i.e. should not have to customize it for a site (except for configuration files.)

    This script does only one job: Take templates, mix the new data, and create and replace static HTML files. Any template engine can be used; but if templates need to authored and re-authored, then it would be appropriate to use a template language that works at DOM level (i.e. replace inner-html content of a node identified by its ID.)

    The data model can be standardized too, ideally within a single excel sheet: One worksheet per template, one table per selected table ID within HTML. A standard table for property-value pairs. To make things simple, this excel file should be auto-generated from the templates.

    More I think of it, more interesting the problem gets. For example, I would like to have updates done by simple SMS by the end-user client. They can be available as emails. A fetchmail script can pull emails from POP account, create/update the excel files and run update script. (SMS is very popular here in Asia, and phone is a device found with everyone.)

  16. Deane, is this one of those Coffee Talk “discuss amongst y’selves” starters? Seems to be working, but where’s the doughnuts? And, since when do you have anything to do with static websites, anyway?

    I happen to see this one yesterday while trolling Hotscripts: http://www.dbqwiksite.com

    Sounds like something similar to phprunner. They have a free version that seems to accomplish the nuts and bolts of providing basic table editing/viewing in a slick manner and more specifically for the stated goals: fast and easy. It spits out PHP/Asp code that you drop into pages. Obviously, you’d still need a db table (or several for that matter), but after that, their visual query builder and checkbox options for output (edit, add, detail pages, filtering, multipage view, etc) seem to make things go pretty easily, though I haven’t used it yet. I think the paid versions have user authentication built in.

    Seems like the only way to make it simpler is to use one of the tools that does the same thing, but uses text files instead.

Comments are closed. If you have something you really want to say, email editors@gadgetopia.com and we‘ll get it added for you.