Saturday, March 28, 2009

Window-on-Data applications

Up till now I have been focussing on technology. We have seen DBMS´s evolve, the web and n-tier architectures come into existence, Yafets prosper, and developer productivity go down the drain. I also spent some time discussing the Java/JEE bandwagon. And used MVC to discuss various technical application architectures. Ohhh, if only there were just technology. Life as an application developer would be so wonderful.

But wait. We have end users too. Shoot...

Let's focus on our customers for a bit: the end users. What is it that they want? Well, I claim that they still want the same (as twenty years ago). In essence that is. They want application frontends that enable them to do their job efficiently. The look & feel of the frontend obviously has evolved (observation two) but they still want to be able to:
  • find a customer
  • look at the details of that customer
  • enter new orders for that customer
  • bill that customer
  • etc.
I personally know people working in organisations that today do in a Ajax-enabled web browser application, what they did five years ago in a poor html application, and ten years ago using a GUI fat client application, and fifteen years ago using a character mode client application, and twenty years ago using a blockmode device attached to a mainframe. These end users still want what I call: Window-on-Data (WoD) applications. These applications provide "windows" on top of data that is relevant for them. A window (or page, or screen, or form, or whatever) inside a WoD application enables an end user to do two things. First one being: to query data.

Typically the user would enter some search criteria on the page, then hit a search button. The server processes this request and returns data that's then displayed in the 'window'. Sounds familiar?

And the second thing a WoD application enables an end user to do: to transact data.

In this scenario the user navigates through already displayed data, marks some to be deleted, maybe changes some values, or enters new data. Then hits a save button. Server processes this request again, and comes back with message indicating whether the transacation was successful.

I claim that the majority of the "database web applications" that are being built today (in more complex ways every five years), are still these type of applications: applications that enable users to query and/or transact data.

By the way... It might be interesting to note now that DBMS's were born to do exactly this. The number one design criterium for a DBMS is: to query and transact data. This is why DBMS's came about. Another interesting fact to note now is that data can be "modelled" in many ways. After hierarchical (triangular wheels) and network models (square wheels), E.F. Codd gave us the relational model of data. And guess what? The wheel doesn't get any rounder. The relational model of data is the end station. It is how we should develop database designs. Now and in the far future. SQL DBMS's are made to deal with (query and transact) lots and lots of data in a manageable and scalable way. These DBMS's nowadays can do this really good, provided (big if here) the data sits in a sound relational database design. Put in another way: a WoD application should have as its foundation a relational database design, for it to reap the benefits a SQL DBMS can provide.

Now don't tell me that we should model the real world in terms of objects... That to me is just going backwards to a network/hierarhical way of designing the data. Not just that, it also merges (hardwires) data and behavior back together again. History has shown that this creates an inproductive and inflexible status quo.

Let's now gradually start developing the Helsinki declaration (or Helsinki in short). The scope of Helsinki is WoD applications. Which doesn't bother me, as they represent by far the majority of the applications that are being built. By the way, technically a WoD application doesn't require end users. More than once I've been challenged with an application that surely couldn't be classified as a WoD application. But the application in those cases did provide a "window on data". Only the window was used by some other piece of software. That to me is still a WoD application. I may go into this in more detail, when I feel like talking about SOA.

Like JEE, Helsinki also has sort of an MVC classification for code. There are some subtle differences though.

I declare that every line of code that is written to implement a WoD application can be classified into one of these three classes:
  • User interface code
  • Business logic code
  • Data logic code
Let's discuss each of these three in some more detail.

Helsinki User Interface (UI) code

All code that you write to:
  • create user interface that displays row(s) of data, and/or code you write to,
  • respond to events triggered by the end user using the user interface, which then typically changes the user interface, most likely after first calling business logic code,
is UI code.

And you know what? I can actually see how object-oriented programming concepts might help us here. For instance a button object that has a 'when-pushed' method, or a multi-record display grid object that has methods like 'sort on column', 'give next set of records', etc.

Compared to JEE's MVC, Helsinki UI-code equals the V and the C together.

In my presentation I then always skip the Business Logic class, and first tell the audience what Helsinki means by Data Logic (DL) code.

Helsinki DL code

As stated above, a WoD application should be based on a sound relational database design. As a crucial part of that database design we typically will have data integrity constraints. These are assertions that tell us what data is and is not allowed inside the table structures that together make up the database design. When discussing integrity constraints with other people I find there are two tribes.
  • Those who think all (and no more) that can be dealt with declaratively falls within the realm of a constraint. In Oracle this would result into check constraints, primary keys, unique keys and foreign keys.
  • Those who know that there is a broader more generic concept of a constraint introduced in E.F. Codd's relational model of data. SQL implementations just happen to give us declarative constructs for above common constraint types that appear in almost every database design.
Helsinki deals with the latter (broader) concept of a constraint. So for instance in an EMP-DEPT database design, statements (sometimes referred to as business rules) like "we do not allow two or more presidents in the EMP-table" or "a department that employs a manager should also at least employ an administrator" are considered to be data integrity constraints. WoD application database designs will have, sometimes many of, these type of constraints.

Actually the SQL standard has the concept of an assertion to implement these other constraints declaratively too. Theoretically what we are dealing with here is predicate logic: boolean expressions over the data held in the table structures that must evaluate to true at all times. Btw. this is a main theme of the book I wrote with Lex de Haan.

In Helsinki every WoD application code line that you write to validate (i.e. guarantee the continued truth of) your data integrity constraints, is classified as a Data Logic code line. In a WoD application, a lot of code will sit in this class and not in the business logic class discussed hereafter.

Compared to JEE's MVC, Helsinki DL-code equals a well defined subset of M.

Ohh, and one final remark: I really fail to see how object orientation can help me implement DL code. This is all set theory and predicate logic: OO concepts just do not fit in here.

Helsinki Business Logic (BL) code

I left this one for last on purpose. This is because I always define business logic code by saying what it is not. In Helsinki, if you write a line of code for a WoD application, and that line of code does not classify as either UI-code nor DL-code, then that line of code must be BL-code. In short BL-code is the rest. If it ain't UI-code and it ain't DL-code, then is must be BL-code.

Now if you prune UI and DL code, you'll see that what is left is either:
  • code that composes and executes queries in a way the business of the end user requires it(I refer to this as read-BL code), or
  • code that composes and executes transactions in a way the business of the end user requires it (I refer to this as write-BL code).
This is pure procedural code: involving if-then-else, loop-endloop etc. I'll show you this in future posts. Point being made: again OO-concepts cannot help me here at all.

Compared to JEE's MVC, Helsinki BL-code equals a well defined subset (the other, remaining part) of M.

Code classes interaction

I'll conclude today's post with a picture of how the three Helsinki code classes interact with eachother.

This figure illustrates the following relations between the code classes:
  • UI-code holds calls to rBL-code (queries) or wBL-code (transactions)
  • rBL-code holds embedded SELECT statements that read data from the database
  • wBL-code holds embedded DML statements that write to the database
  • wBL-code often also holds embedded queries reading the database
  • DL-code often requires the execution SELECT statements
  • DL-code is typically called from wBL-code
  • DL-code can also be called by the DBMS via database triggers that fire as a result of the execution of DML-statements originating from wBL-code
  • Sometimes you'll see that UI-code calls DL-code directly; this is often done to create a responsive, more user friendly, user interface
This post has introduced you to the main concepts used in the Helsinki declaration. Try to remember them, as I will refer back to these quite a lot.


  1. "The relational model of data is the end station." I think this part of the argument is very weak. You can't prove improvements can't be made.

    1. This is a quote from an interview with Fabian Pascal:
      (Google translate from

      "I spent a few pleasant days with Fabian Pascal. The evening before departure, before a glass of wine, I put in ultimately fatal question: And yet there is nothing beyond the relational model?

      He laughed. What may be beyond mathematics and formal logic?"

      If you can find some better tool than mathematics, well, just do it...

  2. Bruno,

    You are absolutely right: I can't prove that.
    But that is not the challenge. The challenge is for someone to prove that improvements *can* be made... :-)


  3. You're preaching to the choir, as far as I'm concerned. Isn't everybody already building apps this way? ;-)
    (joking; I've seen my share of OO database desings and am painfully aware of the O-R impedance)

  4. I absolutely agree with you that UI-Code is code that can take big profit of OO-design (and, hence, should be written object oriented).
    I also agree that both BL-Code (great definition you gave for what BL-Code is!) and DL-Code should not use object oriented methods.

    I do not understand the last picture 100%
    I don't like the arrow from UI-Code into DL-Code and think you can do without it: have you an example for
    "this is often done to create a responsive, more user friendly, user interface" ?

  5. Sokrates,

    To give you a Forms UI-code example.
    Say there is a SEX column in some PERSONS table. It will very likely have as a constraint -- in SQL speak -- CHECK(SEX in ('M','F')). UI-code might call a DL-code module that performs this check using the value just entered inside the item of the GUI (while the user is tabbing out of that item). In Forms this was typically done using a POST-CHANGE, or WHEN-VALIDATE-ITEM trigger that would fire at tab-out of that item. UI-code can then give feedback to the user before, it (UI-code) calls out BL-code that actually composes and executes a transaction.

    In Web-GUI's we might do this with Javascript (AJAX-call to the server), before the user presses the SAVE button.


  6. ok, got it
    ON-VALIDATE-FIELD in Forms 3.0 (used to love it !)

  7. toon,

    Suppose we have a registration form on our web site, and the web guys want to check if the username supplied in the form is already in use.

    This would be a stored procedure in my UI-api, right ? (one in parameter and a return value to represent true or false)

  8. Assuming user-information is maintained inside your database design: absolutely yes.

  9. Toon,
    I've attented your seminar on the Helsinki declaration and I found it intresting. But there were some blanks for me.
    Can you explain this excerpt for me a little more, please (specially the comparison with the hierarchical and network design).

    "Now don't tell me that we should model the real world in terms of objects... That to me is just going backwards to a network/hierarhical way of designing the data. Not just that, it also merges (hardwires) data and behavior back together again. History has shown that this creates an inproductive and inflexible status quo.

    thanks in advance


  10. Toon, it seems a few years since posing the challenge. Are you any closer to a solution?

    Have you looked at this?

  11. Not sure what challenge you are referring to.

  12. This comment has been removed by the author.


Note: Only a member of this blog may post a comment.