December 15, 2005

Application logic in the database

I’m highly in favor of modularity in application development, but suspicious of folks who promote it to extremes as a panacea. (Perhaps another legacy of my exaggerated infatuation with LISP in the 1980s?) Thus, I was one of the chief drumbeaters for OO programming before Java made it de rigeur, but I also was one of the chief mockers of Philippe Kahn’s claims that Borland would outdevelop Microsoft in office productivity tools just because it used OO tools. (Analyst Michelle Preston bought that pitch lock, stock, and barrel, and basically was never heard from again.)

I’ve held similar views on stored procedures. A transactional DBMS without stored procedures is for many purposes not a serious product. CASE tools that use stored procedures to declaratively implement integrity constraints have been highly valuable for a decade. But more general use of stored procedures has been very problematic, due to the lack of development support for writing and maintaining them in any comprehensive way. Basically, stored procedures have been database-resident spaghetti.

Microsoft claims to have changed all this with the relationship between the new releases of SQL Server and Visual Studio, and have touted this as one of the few “game changers” in SQL Server 2005. I haven’t actually looked at their offering, but I’m inclined to give them the benefit of the doubt — i.e., absent verification I tentatively believe they are making it almost as practical from a team development standpoint to implement code in the database as it is on the middle tier.

Between the Microsoft announcement and the ongoing rumblings of the business rules folks, there’s considerable discussion of putting application logic in the database, including by the usual suspects over on Alf Pedersen’s blog. (Eric’s response in that thread is particularly good.) Here are some of my thoughts:

1. As noted above, putting logic in the database, to the extent the tools are good, has been a good thing. If the tools are indeed better now, it may become a better thing.

2. The myth that an application is just database-logic-plus-the-obvious-UI has been with us for a LONG time. It’s indeed a myth, for several reasons. There’s business process, for one thing. For another, UIs aren’t as trivial as that story would make them sound. (I keep promising to write on the UI point and never get around to it. I will. Stay tuned. For one thing, I have a white paper in the works on portals. For another, I’m not writing enough about analytics, and UI is one of the most interesting things going in analytics these days.) Plus there are many apps for which a straightforward relational/tabular database design doesn’t make sense anyway. (That’s a primary theme of this blog.)

3. It’s really regrettable that the term “business rules” is used so carelessly. It conflates integrity constraints and general application logic. Within application logic, it conflates those which are well served by a development and/or implementation paradigm along the line of a rules engine, and those for which a rules engine would make little sense. It’s just bad semantics.

4. Besides everything else, I mainly agree with SAP’s belief that the DBMS is the wrong place to look for module interfaces.

Comments

2 Responses to “Application logic in the database”

  1. Eric on December 23rd, 2005 11:27 am

    But more general use of stored procedures has been very problematic, due to the lack of development support for writing and maintaining them in any comprehensive way. Basically, stored procedures have been database-resident spaghetti.

    Agreed. It’s easy to do wrong, and takes discipline to do right. With better languages for declaring constraints, some of it would go away; and better pattern-based languages (e.g. Haskell and its like) might also make it easier to determine which function to call, for example based on a “resultset” (relation/query result).

    (Eric’s response in that thread is particularly good.)

    Thanks!

    1. As noted above, putting logic in the database, to the extent the tools are good, has been a good thing. If the tools are indeed better now, it may become a better thing.

    Certainly constraints need to be associated closely with the data, for correctness. Those constraints have different (though I think largely derivable and predictable) implications to application and UI tiers… yet while the CASE tools you mention have been around, I’m disappointed there aren’t more frameworks in the mainstream languages for generating code to consistently manifest those constraints across tiers.

    2. The myth that an application is just database-logic-plus-the-obvious-UI has been with us for a LONG time. It’s indeed a myth, for several reasons. There’s business process, for one thing.

    Although that’s also a tricky term. Are you referring to a sequence of CRUD operations?

    For another, UIs aren’t as trivial as that story would make them sound.

    Not at all… and while objects might be good for building UI solutions, that doesn’t hold that UIs somehow need to have data expressed as “objects” (rather than, say, relations). A sufficiently powerful data abstraction like relations would work well for UIs, and for application code.

    Plus there are many apps for which a straightforward relational/tabular database design doesn’t make sense anyway. (That’s a primary theme of this blog.)

    Even with such apps (though we’d probably disagree on their needs), declarative constraints would be useful. For example, with an XML schema one can derive a basic functional editor for documents of that type. If XML Schema were stronger, you’d have even more such capabilities (although you arrive quickly at embedding relational-type notions, like keys (ID/IDREF), which becomes a near-abomination).

    3. It’s really regrettable that the term “business rules” is used so carelessly. It conflates integrity constraints and general application logic.

    Couldn’t agree more – it’s almost meaningless, and can be used with terms like “pattern” to try to justify utter nonsense.

    Within application logic, it conflates those which are well served by a development and/or implementation paradigm along the line of a rules engine, and those for which a rules engine would make little sense. It’s just bad semantics.

    A rules engine is an interesting topic – how do you see a rules engine different from a DBMS with full/better declarative integrity constraints? Or from “table-driven programming”? One difference I can think of is data transitions – while you can write DBMS triggers to control data state transitions, it’s awkward. A better DBMS would have support for that.

    4. Besides everything else, I mainly agree with SAP’s belief that the DBMS is the wrong place to look for module interfaces

    I think I agree – are you saying that the data cross over functional module boundaries?

  2. Curt Monash on December 23rd, 2005 12:35 pm

    Eric,

    A “rules engine” is basically what used to be an “expert system shell.” Indeed, some of the products of that era, like Aion’s and Neuron Data’s, are still around in some form (the latter at Fair Isaac). They were designed for diagnosis, selection, configuration, and similar classes of problems. E.g.:

    1. What disease does this person have?
    2. What antibiotic should I give this person (a classic one, underlying MYCIN and thus EMYCIN and thus Teknowledge’s product)?
    3. What wine should I serve (the stupid industry default demo, popularized by Teknowledge — I proposed combining #2 and #3 and having an “Illegal Drug Advisor”, but nobody took me up on it)?
    4. What parts need to be included in the shipment of this VAX? (A hugely successful one. The engine was DEC’s R1, as in “I wanted to be Knowledge Engineer and now I are one.)
    5. Who should I grant credit to? (A hugely successful one at American Express, powered by Inference’s RETE-algorithm engine ART. They’re the guys whose glossary had the definition “Recursion: See recursion.“)
    6. How should I price airline seats? (A hugely successful one.)

    Despite some big wins, this technology never went mainstream, but it’s still with us in some niches today.

Leave a Reply




Feed: DBMS (database management system), DW (data warehousing), BI (business intelligence), and analytics technology Subscribe to the Monash Research feed via RSS or email:

Login

Search our blogs and white papers

Monash Research blogs

User consulting

Building a short list? Refining your strategic plan? We can help.

Vendor advisory

We tell vendors what's happening -- and, more important, what they should do about it.

Monash Research highlights

Learn about white papers, webcasts, and blog highlights, by RSS or email.