Slashdot Mirror
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?"
In general, I would put it into the place that is easiest to maintain. I've put it into stored procedures and views in the past. SQL is relatively portable as are stored procedures due to their conciseness.
Definately don't put it into the application.
Is this an internal app? Is it going to sit on an Oracle or DB2 box forever? Then toss the business logic in the database if it can be done more efficiently that way.
.NET or PHP. People spend more money migrating their data than they ever do migrating their code. If you put the code next to the data on the database, your likely to get yourself a high performance app that will provide you support contracts till the end of days.
.NET and MS tech.
If you're going to try to market this as a product, then concentrate on devleoping the business logic, after all, that's what businesses want to buy. They'll pay you to port it to their database or just buy a copy of the database you've written it for, if it's valuable enough to them.
I've been working on apps like this for years. Just stick it in the database. It's so much easier than maintaining a bunch of query engine code or mappings so you can keep your precious business logic in a "programming language". If your using Oracle, you could just write it in Java and install it on the database. PL/SQL or whatever you're writing the business logic in will probably be around longer than any app language like
All that being said, this approach works best if you're using a database that has support for stored procedures, embedded code and custom types, either one of the commercial biggies (Oracle, DB2) or PostgreSQL. Firebird (or whatever they're calling it this week) might work too. I wouldn't trust MySQL for this type of work yet though, I don't think it supports code in the database all that well yet.
Personally, I think databases are going to wind up absorbing application servers like J2EE containers and will eventually look like a relational/object hybrid with interfaces to various protocols and container environments. After all, those engines are pretty simple to slap on top of a good database. Oracle and IBM are already moving in this direction somewhat. Oracle more than IBM. I think MS is going to move this way with SQL Server as well, but of course it will only be for
Does that help?
Arrogance is Confidence which lacks integrity. -- me
>I'm currently working in a big project that involves creating tons of reports
.net, since you are platform dependent.
Ahh, there's your problem right there.
Now, the reports are static, meaning they are standards, run at specific intervals.
Later, though, and much sooner that you expect, the requirements will change to doing a lot of data manipulation on the fly.
If you want to make a truly workable system, design an interface for user-wuery creation, and allow them to save their queries, in custom/personal folders. Also allow them to share their queries with others.
I know, it's a pain, but it gives you extreme flexibility in the long run.
A plus is to allow the reports to be converted to png graphics on the fly, so that they are very hard to modify.
Another plus is that you can standardize the SQL and thus ensure compatibility across multiple data sources.
Watch out: make sure the presentation language is portable itself and has a long run-life. PHP and PERL are best. Python is good, but it's harder to find programmers. Java is poor for that, but serviceable. Also, SUN isn't in the best shape these days, so Java's future is shaky 5+ years from now. You can also use jsp, but same applies.
Go for Apache on the web server. Can't go wrong there. If Java then Tomcat.
Stay clear of asp and
As far as stored procedures, ok, but be really careful: they are hard as hell to debug after a while.
If you must use stored procedures, document the hell out of them , make nice diagrams, and do separate text documentation explaining the reasoning behind each one. (this in fact applie to all code written by your shop. But you knew that.)
Finally, take a look at openoffice and its db access. It might be that what you are trying to do might be feasible with it.
"Piter, too, is dead."
My experience is this works best:
Reports and other visual output generated at the client using simple data structures spit out by a procedure (middle-tier) layer.
Java/Ruby/Perl/Python/C++ (gasp! adjust for efficiency) through XML-RPC or SOAP works well as a middle-tier layer. Export the grunt work of searching and sorting as SQL statements to your database. Give the layer an SQL-pass-through command so you can prototype code in your client easily, then port/move the code into the middle layer.
Do it over HTTP or pipe it through SSH.
The database can be anything, MySQL/Oracle/MS SQL. Don't write any procedures or anything to them. They are not very portable; it's very easy to get blocked in, at least in my experience. I'm sure lots of people are good at not getting blocked in, but it's not automatic.
This is what the fascade pattern is for. I wouldn't worry too much about RDBMS portability. However, putting your SP calls behind some kind of fascade at least gives you portability for the code which will utilize the report data. Let the DBMS do what DBMS's are good at.
reech bee-yond ur clip-0n
After 20+ years of wroking with DBMSes, including 10+ as a DBA, I can tell you that you want your business logic in the DBMS - EVERY TIME YOU POSSIBLY CAN DO IT. This is especially true of RI in the DBMS.
If you think that application developers are going to implement the business logic in the application, you better think again. I have watched many systems over the years, and NOT ONCE has the application group implemented the business logic in the application and gotten it right.
In one shop (RI not available in the DBMS, thank you to THAT vendor) we had 1 guy whose job it was to fix data errors. I tracked usage, and he was responsible for 1/3 of the resources used day in and day out just fixing errors in the data every day.
...and that's why so many business apps these days suck. Seriously, Relational Database Management Systems ROCK for business data and business logic. I agree it is a shame that every vendor has tried to "differentiate" themselves with insane proprietary add-ons - but the core TABLES+VIEWS can do more than most people think, completely declaratively - particularly in postgres or oracle (updatable views).
.com boom and replacing it by reliable, versatile RDBMS with a thin web frontend. My systems do what people want, not what some idiot with too much Rose thinks they want.
Too many people comprehensively fail to understand Relational theory, while mucking about with the ill-defined and hype-ridden OO. OO SUCKS for business stuff - makes data analysis horrendously hard. Even if the OO system backends onto an RDBMS, people always use abysmal anti-designed schemas, making later analysis almost impossible, and producing large volumes of low-value data, instead of a few pages of ultra-high-value data.
I make a small fortune RIPPING OUT lame-ass OO stuff installed in the
portability is a great thing, but only if the app is meant to be distributed. if it's meant to be in-house save yourself the headache and do it all in PL/SQL. the app will be simpler and it'll perform better. the odds that your employer will switch platforms are, realistically, very low. besides, it's likely that speed of development and speed of execution are management's top priorities for the project. and even if they do, then you get to rewrite the app, which gives you a chance to clean it up down the road and provides you with plenty of work.
Applications that have used all the facilities of the database GENERALLY take less time and have fewer bugs.
Typically we could have rewritten the entire application for a different platform in equal or less time than trying to maintain platform or DB independance. As well I have NEVER seen a project designed to be independant actually take advantage of that and move to a different platform, NEVER.
Other good reasons for externalizing the business logic is if a significant part of the data is external to the database. In that case, you may want to calculate what you can in the database and store it in a table, then use those calculated values with the external data to get the final values.
The first question to ask yourself when deciding where to put something is where did it come from? Often, this is the best place to put it.
I have been part of the whole where-does-the-business-logic-belong process many times, and if your company is anything like the ones I've seen, some middle manager with training in a field unrelated to anything you're dealing with pulled the "business logic" out of someplace, and you might suggest that they stuff it back in the same place when they are finished with it.
In any case, bright light often is bad for business logic, so it should be kept someplace like that anyway.
-- MarkusQ
It has the notion of "schemas," (aka users) which are analogous to "databases" in mysql and postgres. In fact, from a user's perspective, they're almost identical. There's no need to put a three-letter prefix on your table names- you can just do schemaname.object, just like in other DBs.
Furthermore, you can create synonyms to tables in other schemas, or even PUBLIC synonyms that allow you to access a table from any schema without specifying the owner's schema ("object" vs. "schema.object").
Reports are usually the "ugly" part of a system precisely because performance is usually extremely important, which leads the developer to break some design rules or duplicate code that is already in the app, putting it in stored procedures so that the reports run faster.
However, once you get to the reports, the system is usually at a stable stage as far as business rules are concerned, so they won't change much (if at all).
Finally, that whole thing about migrating from one database to another, well... it just doesn't happen that often and when it does, it will take time and patience and a lot of testing, no matter how well you built your application, and like some other poster said (jokingly) before, you will be giving someone a job when it's time to change DBMS.
Go hug some trees.