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?"
Follow the priority list and go from there.
If the priority is minimize development time, that might tell you one thing, if it is to maximize portability that might suggest another.
For example if you are a pure Oracle shop, have been for 10 years, would never port off of it - why should you care that your logic is implemented inside Oracle. If you are prototyping on a new database, and have no history with it (or any other product) you had better come up with a different answer
I have mod points and I am not afraid to use them
Eh -
Just last week I was offered a job porting a bunch of business rules written in MS SQL stored procedures to Oracle stored procedures at a nice fat billing rate.
Fight unemployment! Use stored procedures!
What is even better is when they break between releases from the same vendor. Ka-Ching!!
(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).
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
Couple of things. First off, if you're a publicly traded company go score points with your managers by suggesting that you should talk to the Risk Management and Comp Sec guys at your accounting firm. They'll give you criteria that the project should meet (usually very low impact to implement early but nightmarish to implement later) if you want the data to meet financial disclosure and Sorbains Oxley (SOX) criteria. If you're publicly traded and you don't meet SOX criteria at (or by) the end of this fiscal year then you stand to pay HUGE fines and possibly get de-listed. (Trust me, not meeting SOX criteria by the end of this year would be BAD.)
Business Intelligence and many widespread corporate reporting tools are often subject to SOX regulations. The Risk Management guys will usually have an opinion about the portability vs performance issue for you. (Also, these guys are way way way too busy this year to artificially create more work for themselves. Every publically traded company in the US is trying to get these guys to sign off before the year end.)
Second, look into some of the XML data portability ideas and solutions out there... I can't name anything off the top of my head, but there are companies out there with enterprise and off the shelf data portability options. Also, if you're a flexible development team you probably have someone who can cobble together something good using XML. Google XML Data Portability and you'll get started on pointers.
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.
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.
"Provided by the management for your protection."
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
the answer depends: is your app a product? if so, it should probably support whatever rdbmses that your potential clients use. if it's a custom app for a client, do they use more than one rdbms? these are the only two cases in which it makes sense to spend substantial effort on isolating your database layer. otherwise, take the stored procedures and run -- this is exactly what they were designed for. there are a couple other advantages (other than performance) which may not have been mentioned:
1) abstraction. yes, abstracting the data layer at the database means accepting that you're tied to that database, which isn't a bad thing: i grew up with oracle and still swear by it. as a result, you only write one db layer api, not one per supported dbms per target business logic language.
2) depending on which rdbms and how you set up the user access, you can also probably push a large chunk of the connection pooling onto the db engine rather than having to manage it yourself.
3) access control. microsoft mentioned this several times at their devdays convention. i was surprised that it was news to them (hint: fewer doors means fewer ways in). regardless, it's a good idea: i've always seen allowing execution of arbitrary sql statements by joe random as an invitation for bad things.
4) one place to go to for developer support requests.
hope this helps.
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.
The best place for buiness logic in in Access Macros!!! It's even better when you have access link to Excell tables for it's data!!!!
Don't use Access Visual Basic - clippy will come down hard on you!!!! Macros are where it's at!!! Marcos are even unicode compliant - so localisation is really EZ!!!!!
DONT USE ACCESS 2000/XP/2003 - it's buggy!!! Aceess 2.0 or 97 is where all the stable apps are!!!!
WORD 97 has great macro support too - don't be afraid to put some business logice there.
There's a rounding bug in Access that you can use to get more money out of your customers!!!! It inflates the sales tax - keep the change!!! You deserve it!!!!
Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.
Not only that, the only guarantee is that the business logic will change. In my experience the changes will be somewhere between "modest updates" to "complete rewrite". That means you put them where you can get at them easiest, have the easiest time implementing (i.e. more like Perl or SQL and less like assembly language), will be able to understand/verify/explain what you've coded, where it will look the best if you have to show it to someone else (again, SQL over assembly), etc etc.
In some cases it will be easier to maintain in an application than on the database, in other cases vice versa. Also consider which spot gets you the easiest access to version control. The only other guarantee besides change seems to be the reversed decision.
I do not have a signature
I don't see how putting business rules into the database limits accessability to them. Enterprise applications have database connectors/adapters.
How is a business rule in an application cleaner and "API-like"? Do you mean creating a library (e.g. perl module) of routines that implement policy? How is that "cleaner" than calling a stored procedure in a package? Have you used stored procedures and databases as back ends to applications?
Regarding your last paragraph, that's the whole reason why this topic is here. Everyone knows not to build business rules into the interface. The question is where to put them instead. That's what we are discussing here.
If I can boil down your post, I think you are saying that you prefer coding business logic in a library of a particular language. What language?
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
So what you're saying is: I don't know about any other databases and have never ported anything from Postgres to anything else, but Postgres supports several languages so it must be portable.
I LOVE Postgres, but the stored procedure portability is not its strong point.
To the best of my knowledge, Oracle and MS-SQL server do not support Perl, TCL, etc. I know Oracle supports Java, but I haven't ventured into that territory.
Oracle uses PL/SQL, MS-SQL server uses T-SQL, and Postgres uses PL/PG-SQL. All of these are different enough that you would have no hope in hell of being able to do a straight recompile. Esepcially when it comes to date handling and things like that each database does it differently.
You will have to decide what's more important: speed or portability. If it's speed, choose the native stored procedures in PL/SQL or whatever your database uses. If portability is your main concern, stick as close as possible to the subset of ANSI SQL that most databases support. Check and see if the SQL constructs you're using work in other commonly used databases.
Finally, keep in mind that Oracle does not have the notion of "databases" the way that MySQL, Postgres, and MS-SQL server do. The closest thing is to have a seperate instance of Oracle running in its own memory space with its own "SID" identifier (Oracle is a wonderful but complex beast!) The reason this may be important is because you might find down the road (as we did) that when you try to mix your 8 unique databases into one Oracle instance that you have conflicting table names. We solved this by putting a three-letter prefix and underscore on each table name to describe what logical application it belongs to. Also, Oracle table names can't be longer than 32 characters, so keep that in mind as well.
My recommendation for portability is to use one database with prefixes on the table names, then have a Perl (Java, whatever you're using) API that returns / accepts the appropriate data structures.
Good luck!
According to the CFO, it belongs in India. See you in the unemployment line.
Show me on the doll where his noodly appendage touched you.
Ahh, there's your problem right there. Now, the reports are static, meaning they are standards, run at specific intervals.
When a business asks for "some reports", it's an indicator that they only have an implicit, at best, understanding of their own business process. They treat the computer as a big bin in which to dump "facts" and generate some output in standard formats which they use to do manual analysis and processing. If your application consists primarily of reports, this is a smell because:
One of the things to realize about reporting is that the type of queries made to a database for reports is very different from the queries made during use of the application.
A solution to your performance problem probably lies in creating some views into your database that represent the data in a less transactional way - create the views such that they are not normalized for transactions but rather for the queries you expect, and the report generation speed problems will take care of itself, and you can leave the business logic code portable.
For example, somebody might want a report like "sales by state", and this report takes age to run against your transactional database because it's normalized for inserting a sale across many tables, and a select to retrieve this data might take a bunch of joins across all tables. If you create views, or replicate the data to another database with a different scheme, your select might be "select * from sales where state=''" and return very quickly.