Where Does the Business Logic Belong?

logic-Dilemma queries: "I'm currently working in a big project that involves creating tons of reports. These reports require extensively data operation and manipulation in order to be build, and most of that can be handled directly by the DBMS (which would greatly increase performance and implementation time). However, letting all business logic sit in the database implies that we will be extremely attached to one vendor, which kills any attempt at portability. What would you do to tackle this dilemma? Have you ever faced a situation in which the choice between clean design/portability versus performance would change dramatically the whole system design? What have you chosen?"

Comments

psql, perl, tcl

[n1ywb]

Procedural sql is reasonably portable. I don't know about other RDBMs, but postgres supports perl, tcl, and others. That portable enough for ya?

Re:psql, perl, tcl

[dark_panda]

fwiw, postgres as of version 7.4 has schema support. i haven't had a chance to use it yet as we've previously been using 7.3.x, but it looks pretty good.

J

Don't worry about portability

[notfancy]

(Bear with me, it's not a troll).

10+ years of experience in the Financial/Banking sector might not tell you much, but it has taught me that business/domain logic changes so frequently so as to make any possibility of portability be remote, in the best case. Stored Procedures just save your life, period. Also, and perhaps more relevantly, your client probably has made a substantial investment on the RDBMS, and they won't even dream of switching DB layers down the line.

If you're confident about the choice of RDBMS vis-à-vis its architectonic permanence (Is the client happy with it? Are you sure it's gonna sustain the load you plan?), you shouldn't worry about portability in, say, mid-termish 3 years after installation. However, if what you want is to re-sell (that is, productize) the code to another potential client who might have a different RDBMS, your design goals should be adjusted accordingly (for instance, you can insist on portability, by building a middle-tier; or you can push for a RDBMS you know you can pitch together with your system to whomever you want to sell it afterwards).

Use Hibernate

[revscat]

If you are concerned about vendor lock in, I would suggest Hibernate as a data persistence layer. It lets you abstract out interactions with your database so that switching over from one vendor to another is a simple matter of changing a configuration file.

Further, Hibernate is battle tested, and used in the real world.

Clean `n Portable - At Least to Start

[4of12]

Have you ever faced a situation in which the choice between clean design/portability versus performance would change dramatically the whole system design?

Yes, I have.

What have you chosen?

Clean and portable.

Reasonable performance right now when you first write your code is good enough.

More important is to write maintainable (readable and understandable) and extendable code.

We have high performing applications written 10 years ago that sit on the scrap heap that few people know or love these days because they were tuned too tightly to getting performance in a very specific situation. But situations are always changing. Highly optimized applications are too specific and too fragile to be useful very long.

Meanwhile, an originally clean design that was of only adequate performance has been used and used and used. And it's become bloated and creeping with all kinds of ugly useful features simply because people could easily add them on. Once there was a rewrite to improve performance when resources were available because a lot of people were using the code.

Here's the irony: lots of surgical scars on old code is a testament to its successful conception and to its continued usefulness, as well as being a motivation for replacing it with the next generation code, which is what you usually hear about at the watercooler.

Definately depends

[sbryant]

You didn't say enough about your requirements to be able to say for sure, but here are some points that may help you:

• Logic in the database almost always gives you more performance. It's less portable, but if your database is one that scales well, its a much simpler solution.
• We've found that some types of lookup could be done quicker when they were coming out of hash tables in an application server, rather than queries from the database. These cases are definately the minority though.
• Moving logic out onto an application server gives you alternate ways of spreading your load. If your database is very heavily loaded, doing this will free DB resources and actually speed things up, as some of the work is done on another machine (or machines). Some database systems don't scale very well (ie: clustering), so using a different application architecture can help a lot.

I tend to do things using application servers, but this is because I have to combine data from different sources, not all of them databases. It makes sense to have the business logic all in one place - including user rights, audit trail/logging etc. I also like the fact that users don't have direct access to the database.

The right answer for you depends more on your requirements than anything else. There is no answer which is always right for all situations.

-- Steve