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
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?
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!
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.