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.
    ELSE
        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.
    ELSE
        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.
    ELSE
        Time for a break, what's new on Reddit?
    END IF;
ELSE
    A secret none-of-the-above option that appears here requires
    another 30 lines.
END IF;

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.