When not to use a relational database: generating HTML

Yet another post in a neglected series of riveting relational databauchery.

This past week I've been doing some maintenance work on the execrable system that runs the College (bear with me, I'm working on a Google bomb). This system happens to have a web-based interface that allows staff and students to view final marks, paystubs, class schedules, and so forth.

If you've ever dabbled in web applications, you may know of some of the popular development platforms: PHP (which powers Facebook, Digg, What He Said, and the College's public site), ASP.NET (MySpace, Lego), JSP (Globe and Mail), Ruby on Rails (mostly apps from the folks that created RoR in the first place), and a zillion others.

These technologies query relational databases in order to render the HTML web pages that you see when you visit the site. None of them are built in to the relational database itself, using the SQL query language from within to spit out the web's angle-bracket-laden content.

But that's exactly how the College's self-service site works.

It appears to have been built atop relational-database-maker Oracle's HTML DB product, which features PL/SQL as its programming language -- a bondage-and-discipline language that requires you to first declare that you will later again declare your intention to define a value as being equal to 3.

Unfortunately, declarative query languages like SQL don't easily jump through the hoops and loops required to emit web pages. For example, a single section of code to extract a list of students based on whether the user is an instructor, an advisor, or both, and whether the user wishes to view students that are instructed, advised, or both, looks something like this.

IF student is instructed and advised THEN
    IF user is instructor and advisor THEN
        30-line SQL query to retrieve students that are both instructed
        and advised by this advisor and instructor
    ELSIF user is advisor THEN
        Same 30-line query, with one change to look for instructed
        students that are only advised.
        Once again, the same 30 lines, this time looking for instructed
        students that are just instructed.
    END IF;
ELSIF student is advised THEN
    IF user is instructor and advisor THEN
        Yet another copy of the 30-line SQL query to retrieve students
        advised by this instructor/advisor.
    ELSIF user is advisor THEN
        You guessed it.
        Hoo boy.
    END IF;
ELSIF student is instructed THEN
    IF user is instructor and advisor THEN
        This is getting tedious.
    ELSIF user is advisor THEN
        And annoying.
        Time for a break, what's new on Reddit?
    END IF;
    A secret none-of-the-above option that appears here requires
    another 30 lines.

Leaving aside that most of that doesn't even make sense -- I lost an hour to wondering how a student could only be advised and not instructed -- it's just plain hideous. Some 300 lines of code are involved, all but 30 or so seemingly redundant.

This was not an isolated case. Determining whether a user is an instructor or an advisor uses the same pattern. The PL/SQL "logic" that determines whether a user's account and PIN are valid is a multi-screen riot.

Conclusion: keep the code out of the database.

Update, Jan. 28

Of course, I managed to screw up the example code so that the ELSE clause of each decision would never execute. It's fixed now, just in case anybody out there was planning on implementing their own twisted instructor/advisor system.

Archived Comments

  1. Geof Harries on 20080125.Friday:
    Amen to that brother. Do your very best at all times to respect the layers: structure, content, presentation and behaviour (relevant terms when it comes to web apps).
  2. Dave on 20080125.Friday:
    Yes, and each layer tends to have a language or languages best suited to its implementation. Just as you wouldn't want to query a relational database using JavaScript, you wouldn't want to build a website's behaviour using a crufty pseudo-imperative dialect of SQL.
  3. AOF on 20080330.Sunday:
    Was this stuff *generated* from some higher level tool or did somebody really sit down and write this... erm.. ?code?
  4. Dave on 20080331.Monday:
    I wondered if it was auto-generated too. But much of it leaves the distinct impression of a human's touch, and judging by the comments, bug fixes are performed on this code rather than at a higher level.