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
(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).
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!