Follow Slashdot stories on Twitter

 


Forgot your password?
Close
typodupeerror
×
Programming IT Technology

Where Does the Business Logic Belong? 92

Posted by Cliff from the chosing-a-design-strategy dept.
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?"
This discussion has been archived. No new comments can be posted.

Where Does the Business Logic Belong? More

Where Does the Business Logic Belong?

Comments Filter:
  • Quite simply.

  • psql, perl, tcl (Score:2, Informative)

    by n1ywb ( 555767 )
    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 (Score:5, Insightful)

      by Anonymous Coward on Tuesday May 11, 2004 @11:15PM (#9123958)
      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!

      • Re:psql, perl, tcl (Score:2, Interesting)

        by ntr0py ( 205472 )

        Finally, keep in mind that Oracle does not have the notion of "databases"

        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

        • BINGO! We have a winner.

          You can go so far as to code your application to execute "ALTER SESSION SET CURRENT_SCHEMA=blah" right up front and from then on out, your "database", i.e. schema is configurable. You can have a schema called BLAH01 with a version 1 database and a schema called BLAH02 with a version 2 database and your client can just access either one as needed. Schemas are MUCH more elegant than "databases" in SQL Server. They force ownership of data to a user account instead of the system. I
          • and two-level, even. Every object has an associated user as well as a database. If you just look for database.object, it looks for database.yourlogin.object, and if that isn't there it uses database.dbo.object. For that matter, you can also just qualify it outright.

        • Re:psql, perl, tcl (Score:3, Informative)

          by dark_panda ( 177006 )
          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
      • The most important thing here is that you have an abstraction from your database. If you write the business logic entirely into your application as a series of, say, java classes, you must be sure that they are not wrapped up in queries and expected data collections that can only work in your database. Some ability to store the queries in a place that can be accessed for rewrite when the database changes, and require minimal rewrite of the business logic's use of them is best. I use the RedHat WAF which c
      • You can connect to Oracle with both Perl and PHP.

        I don't know if you were referring to connectivity or to being able to have the database understand the languages within the database, though.

    • Re:psql, perl, tcl (Score:3, Insightful)

      by Phouk ( 118940 )

      Procedural sql is reasonably portable.

      Oh? You did understand that the discussion was about portability between database products (e.g.: port your application from Oracle to SAP DB), not between operating systems, didn't you?

      I don't know about other RDBMs, but postgres supports perl, tcl, and others.

      Well, the others don't. Almost every db that I know of has their own choice of stored procedure language.

      That portable enough for ya?

      No, what other RDBMs would you port that to?

  • Very simple (Score:5, Insightful)

    by MerlynEmrys67 ( 583469 ) on Tuesday May 11, 2004 @03:24PM (#9120188)
    What are the project priorities...
    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

    • <Butthead Voice>
      Huh.. He just said Business Intelligence!
      </Butthead Voice>

      <Bevis Voice>
      Yeah! Heh! Sounds really stupid! Intelligence! Intelligence!
      </Bevis Voice>

      <Butthead Voice>
      Shutup, Bevis! You oxymoron!
      </Butthead Voice>

    • Re:Very simple (Score:3, Insightful)

      by Mahrtian ( 238199 )
      In software there is no never. And with Oracle there _definately_ is no never. All it takes is one change to the Oracle license and your total cost of operations could double... or worse. Without an abstraction of the DBMS, you are stuck with paying the difference.

      Now this may be acceptable. The OP is correct that it all depends on priorities. You may decide that having a tight, fast system today is worth a potential (even if unlikely) hair-pulling refactoring if the DBMS must be swapped.
    • Why is it that nobody seems to pay attention to the fact that an upgrade to a piece of software can be as bad as switching vendors??

      It's not like a company is never going to upgrade their databases! Once an upgrade happens, the underlying code has changed... This requires you to retest your application.

      Would you rather retest reading data from a table and making sure it hasn't translated into Swahili or retesting all the complicated Business Logic of your application?

      I prefer the former...

  • PL/SQL (Score:2, Interesting)

    by David_Reno ( 17956 )
    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.

    • Re:PL/SQL (Score:2, Insightful)

      by David_Reno ( 17956 )
      I should clarify my above point, definitely don't put business logic into the presentation layer of the application.
      • Oh geez. I think there's more to an application than the DB and the Presentation Layer.

        Ideally, any system should have a Datalayer, the Engine and the Presentation.

        Start from the assumption that you can do all this (and thereby do the BL in the Engine instead of the Dlayer), and modify only if it's necessary (e.g. boss won't give resources to write the necessary Engine components.) If all/most of the necessary logic is in the DB already, that's another thing to think about (you can also of course just rip

    • Re:PL/SQL (Score:4, Insightful)

      by ichimunki ( 194887 ) on Tuesday May 11, 2004 @04:34PM (#9120820)
      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.
    • "Definately don't put it into the application."

      Putting it in the application and using a generic db interface should make it portable, but in practice it never does because there are inevitably lots of little quirks.

      Using a db's procedural language is bound not to be compatible, but is far easier to port, IMHO, than get the bugs out of an application.

      I second the concept ;-)

    • Re:PL/SQL (Score:2, Interesting)

      by mcdrewski42 ( 623680 )
      I can't help but disagree here. Putting business rules into "The Database" is a sure way to limit the accessibility of those rules to external systems. Putting them into "The Application" allows allows a cleaner API-like access, available from other enterprise (we are talking enterprise software here, right?) apps.

      Remember that the business rule you build into a report will probably be needed in an interface one day (ie: taxation on a charge of some kind), and that the tax rules will change much more qui

      • Re:PL/SQL (Score:4, Insightful)

        by David_Reno ( 17956 ) on Tuesday May 11, 2004 @06:13PM (#9121950)
        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?
      • Putting them into "The Application"

        Of course you mean: The Applications. In many cases, databases get accessed by more than one application. In these cases you have multiple problems when you put the business rules in the applications, not in the database.

        • You have to update/test/redeploy all applications when the business rules change.
        • When most applications implement the business rules correctly, but one ill-behaving application doesn't, then that ill-behaving application can/will corrupt the data, an

  • Jobs (Score:5, Funny)

    by the eric conspiracy ( 20178 ) on Tuesday May 11, 2004 @03:28PM (#9120230)
    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!!

  • Don't worry about portability (Score:5, Informative)

    by notfancy ( 113542 ) <matias@nOSpam.k-bell.com> on Tuesday May 11, 2004 @03:39PM (#9120341) Homepage

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

    • Might I suggest you look into PegaRULES [pega.com]? Its an extremely flexible business logic layer.
    • Well said. I would also point out that as far as I know, most decent RDBMSs allow you to write your SPs in Java. Not quite as efficient as PL/SQL or whatever but less clunky and more portable should the port be required.
    • Good design usually means slightly more initial coding work, and insanely less maintenance. I learned this from this book [relisoft.com], particularly this chapter [relisoft.com]. Admittedly, they are porting to Windows. They are also using C++, which I thoroughly despise. They have also designed their code so well that it only takes one section -- while we're learning new gui concepts -- to port a commandline program to a Windows GUI.

      I admit that developing for something portable to begin with (java, parrot, wxwindows, gtk+, or wh
    • 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.

      Not only does the back-end change less frequently, I find the front-end changing every now and then, depending on what's the latest you can hire cheap developers for. Often the type of reports and their mode of presentation also changes. Pushing business logic into the back-end allows you to keep up with the most appropriate front-end techno
    • Upgrading software is just as bad as changing vendors. Obviously, the compatibility will be much higher within one vendor's products, but when you change versions of anything (Compiler, RDBMS, WebServer, OS), you have to restest everything! Some places don't do this for patches and such (to their great detriment for that 0.1% of the time when they get f*cked).

      Eventually, an upgrade will break your software. So, portability is a concern even if you're CIO is blowing Larry Ellison in his spare time. Diff

  • Do you need portability? (Score:5, Interesting)

    by GOD_ALMIGHTY ( 17678 ) <(curt.johnson) (at) (gmail.com)> on Tuesday May 11, 2004 @03:41PM (#9120360) Homepage
    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.

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

    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 .NET and MS tech.

    Does that help?

    • 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.

      Does anyone have any experience with Microsoft's new ObjectSpaces persistent object initiative for .NET? Some overview here:

      The ["scientific"] data we're generating is very large, and mu

      • So, code your own 64-bit classes? Large-file-support for filesystems is around, so the database files shouldn't be a problem.

        Oh, wait, I forgot: most database vendors don't care much about giving you user-defined-types. Maybe functions, if they're feeling nice.

        I work with Interbase/Firebird (on-topic: plenty of SP, trigger stuff available, though I rarely use it) -- I know they say that, well, basically, you can do this yourself as a BLOB (hey, it's binary data, right?) and massage your own stuff however

        • Relational database theory in no way prevents you from having any types you like in the database (current maintainers of the academic theory would point out that "object/relational" databases are just relational databases working the way they should.)

          Except for one thing: SQL [like Java] is a 32-bit language!!!

          SQL BLOBs are 32-bit entities!

          Java arrays are 32-bit entities!

          WE NEED 64-BIT DATA TYPES AND 64-BIT PROGRAMMING LANGUAGES!!!

          • Implementation-wise, you might first complain about 32-bit memory addresses when dealing with your operating system, hardware, etc. In many languages, the size of a pointer (which in turn generally limits addressable memory and disk space) is based on the architecture. Obviously, in some, it's not. Heck, the size of a byte may vary from system to system. And the size of "int", "long", "long double", "long long" may be defined as "more than the size of ..." or as "4 or more bytes" or any other not-quite-usef

  • Couple of things (Score:4, Insightful)

    by Inexile2002 ( 540368 ) * on Tuesday May 11, 2004 @03:41PM (#9120361) Homepage Journal
    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.

  • Use Hibernate (Score:5, Informative)

    by revscat ( 35618 ) * on Tuesday May 11, 2004 @03:41PM (#9120366) Journal
    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.
  • That way different 'vendors' products are similar in features, if not identical. After a few years, most GPL projects cross-seed each other enough to make switching pretty painless. And if you really have to switch before then, you can take your implementation *and* your features with you.

  • i like logic in the DBMS (Score:1, Insightful)

    by Anonymous Coward
    I think the best place for business logic is the database server.

    The COM middle tier business logic MS was promoting in the 90's was a joke. I found it to just be another layer of abstration with no benifit.

    If you place everything in stored procedures you get a very centralized app that any client application can connect to (web, java, etc...)

    If you're worried about being locked into one DBMS platform, try to write all your queries and stored procs with standard sql. Avoid database specific features, the
    • I hate responding to ACs, but...

      You obviously have no friggin idea what good software is. MSFT wasn't the inventor of this architecture. It is a very old concept that was simplified (some would say not so well by COM and I agree...)

      Putting everything in the database sets you up for failure in so many ways... First of all, what happens when the DB Server needs to be upgraded???? YOU NEED TO REGRESSION TEST EVERY SINGLE APPLICATION IN YOUR CORPORATION!!!! That's ridiculous!!

      You should have Unit tests

      • Putting everything in the database sets you up for failure in so many ways... First of all, what happens when the DB Server needs to be upgraded???? YOU NEED TO REGRESSION TEST EVERY SINGLE APPLICATION IN YOUR CORPORATION!!!! That's ridiculous!!

        You should have Unit tests for each layer in your software... You just fire of JUnit, NUnit, whatever on your DATA Layer and wait for the results. No need to retest the Business Logic at all..

        ...until you upgrade the application server, right? And you're sure t

        • If anything "sets you up for failure in so many ways", it's replacing analysis of your project with some "thou shalt not" rules you picked up in a magazine somewhere.

          Everyone knows the sign of a debater who is confident in his argument is a parting insult... I've done software design for over a decade. I didn't pick this stuff up from a magazine.

          When you scale an application by adding "application servers", you re-test all the software on that server before making it live. The same thing is done for u
  • Remember, when you are running a stored procedure, you are ceding control of your application to the database. It may or may not be appropriate to do this. Often this is an issue that doesn't get enough consideration.

  • Tons of reports? (Score:3, Interesting)

    by chris_mahan ( 256577 ) <chris.mahan@gmail.com> on Tuesday May 11, 2004 @03:49PM (#9120443) Homepage
    >I'm currently working in a big project that involves creating tons of reports

    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 .net, since you are platform dependent.

    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.

    • >I'm currently working in a big project that involves creating tons of reports

      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 processin

  • Clean `n Portable - At Least to Start (Score:4, Informative)

    by 4of12 ( 97621 ) on Tuesday May 11, 2004 @03:59PM (#9120524) Homepage Journal

    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.

  • Portability (Score:3, Interesting)

    by Cranx ( 456394 ) on Tuesday May 11, 2004 @04:00PM (#9120537)
    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.
    • i'd be careful -- depends on the number of users. xml rpc / soap is hideously inefficient when it comes down to it. you're either wasting moderate amounts of time and huge amounts of bandwidth marshalling / unmarshalling requests or huge amounts of time and moderate amounts of bandwidth if you include compression in that process. if there aren't a lot of users, well who cares? however, if this app is supposed to support a substantial number of people, you run into problems. xml is not a silver bullet.
      • It doesn't have to be XML-RPC or SOAP, per se. I just meant to say that a middle-tier is very helpful. Especially if you write the middle tier to use semaphores through the SQL server instead of using the local file system. You can place your middle tier on many different servers to spread the load out, so the SQL server is only servicing queries (some SQL servers can scale as well). It's not too hard to implement; you can pass through SQL queries from the client to prototype, and place it into the midd
    • If it's portability between databases that you want, then think about the following:
      - build a database abstraction layer into your application. From the application, access the database only through this abstraction layer; that way you'll only need to recode the abstraction layer to support other databases
      - make database data visible to users *only* through views and stored procedures. If you ever need to migrate to another database platform, it's generally much easier to recode views and stored procedure

  • Fascade Pattern (Score:4, Interesting)

    by HaiLHaiL ( 250648 ) on Tuesday May 11, 2004 @04:07PM (#9120587) Homepage
    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.

  • depends... (Score:4, Insightful)

    by blackcoot ( 124938 ) on Tuesday May 11, 2004 @04:18PM (#9120693)
    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.

  • There is no "choice" here (Score:4, Interesting)

    by Mycroft_514 ( 701676 ) on Tuesday May 11, 2004 @04:22PM (#9120728) Journal
    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.
    • Why is it that every person on here who jumps up and down and says "DATABASE!!! DATABASE!!!! YOU MUST!!!" just _happens_ to be a DBA?? Hmm...

      Well, I do Software Architecture/Design and implementation for a living (12 years experience) and I would say you have to do it in the Application/Business/Middle Tier(s) because middle tiers can be distributed and scaled much easier than databases. I would use Stored Procs and Views for certain things, but only to join tables and massage the data, I would _NOT_ put

  • I know where! (Score:5, Funny)

    by zulux ( 112259 ) on Tuesday May 11, 2004 @04:33PM (#9120818) Homepage Journal

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

    • Newbie. (Score:1, Redundant)

      by KnightStalker ( 1929 )
      Business logic is *IMPORTANT*. Therefore, it should be replicated in as many places as possible. Anything that touches the database should have a separate copy of the same logic in it. A handy way to achieve this state, which functions both as a distributed versioning system and a set of backups which is both effective and affective, is, when writing some new code, to find some old code sitting around that does more or less what you want and use the "cut and paste" feature of your editor.

      You'll find it mos
  • I'd never use a database for anything more than storing data and doing simple fast queries to get it out. You are usually tied to one big database machine/cluster and it is the most expensive to scale. So more logic you put into the DB the more you will pay if you need to scale it (not to mention all the expensive DBA you will need to hire to manage the stored procedures, et al).

    Fetch the data into business objects of some sort (or a local data store), work the data as you need then send it up to the pre

    • Re:layering (Score:1, Interesting)

      by Anonymous Coward
      ...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).

      Too many people comprehensively fail to understand Relational theory, while mucking about with the ill-d
      • You wouldn't happen to hang out with that Tablizer dude?

        Tablizer has an entire OOP-is-mad Web site, and I could never figure out what he was talking about because I do a lot of GUI work where OO is as essential as air (although Tablizer thinks there could be some kind of table-driven GUI, but I think that is reinventing OO in the style of C-level Gnome programming).

        I believe the point is that in certain domains, OO is the hammer that thinks every pointy thing sticking up is a nail.

      • And you are the reason that things run slow. Every problem has a specific solution, pushing RDBMS on everyone without regard to the problem is self-serving...oh wait you are making a small fortune on this, hence the vested interest into something you know.
  • If you create a library or set of libraries that have the interface to the queries/reports, then you can implement the reports in the database for speed, and if you ever want to change vendors you just change the code in the library. This way the rest of your application stays the same. Portability is achieved with smallish work, and you can get all the speed bootst you want from the RDBMS without commiting to it for eternity.

  • It's hard to give a more precise answer without knowing the details of your architecture.

    But, in general, I prefer to partition an app that requires extensive business logic into separate logical layers (whether or not these are separate physical layers).

    Database-side stored procedures do limit portability, but if you can run similar code at a point in your application that is physically close to the DB server, or at least on the same fast network segment, this gives you better performance than you'd get

  • portability (Score:3, Interesting)

    by pizza_milkshake ( 580452 ) on Tuesday May 11, 2004 @06:21PM (#9122030)
    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.

  • In the DB IMO (Score:2, Interesting)

    by GLHMarmot ( 124846 )
    I have been developing DB based applications for 13 years along with being a full time DBA for the last 3. Primarily with Oracle but also using Postgres and Mysql. Every time I have been part of an development project that tried to implement the Business Rules in the middle tier it has taken forever and been buggy.

    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

  • If you're using Oracle (Score:3, Interesting)

    by f00zbll ( 526151 ) on Tuesday May 11, 2004 @07:49PM (#9122790)
    I would suggest reading up on materialized views, which were originally designed for data mining and reporting purposes. The next thing i would do is look at Oracle's OLAP support and see if OLAP covers all the functionality you need. The only time I would put the business logic in a middle tier is when SQL doesn't provide the expressiveness needed to generate the reports. For example, if you need to use some complex mathematic calculations that combine several rows, doing it in PLSQL probably would be painful. If you're ok with embedding java in your sql, Oracle allows you to do that and optimize those calculations.

    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.

    • Oracle10g will allow you to take a query, view it as a (2D+) spreadsheet, and then aggragate/calculate across dimensions in almost any useful way. I believe that many/most calculations for reports can now be done inside a single query, assuming that the data is reachable from Oracle (i.e. on your Oracle server, or in a file Oracle can access, or on another database server that your Oracle instances can access).

      The point is, you probably don't need (much) code for calculations, outside of your queries. Most
      • yeah, I forgot about the new features of 10g, which is considerably cheaper. The other good thing about Oracle's solutions is you can temporarily store adhoc queries. Assuming of course the default functions in oracle cover your calculation needs and you're not adverse to using Java for the more complicated calculation not coverd by Sum(), average() and other standard functions.

  • Unhelpful but funny --or-- things BL stands for (Score:4, Interesting)

    by MarkusQ ( 450076 ) on Tuesday May 11, 2004 @09:14PM (#9123424) Journal

    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

  • Where Does the Business Logic Belong? (Score:5, Funny)

    by pyrrhonist ( 701154 ) on Tuesday May 11, 2004 @11:41PM (#9124056)
    According to the CFO, it belongs in India. See you in the unemployment line.
  • The best solution, also with respect to scalability, is to implement an application server, which might run on the same machine for that matter as the database server. Go for a three-tier solution. Implementing business logic at the client is a bad idea. Implementing it as stored procedures is a less good idea.
  • There is no logic in business. It's all about who you smile at, who you lie to, who you steal from, who you do favours for, who you do special favours for, who you fuck, who fucks you, who you back stab, who you kick when they're down and who you offer a helping hand up only to get a blow job later.
  • look at Ralf Kimballs books on DW design.

    The reporting logic can be held using multi-dimensional tables in OLAP cubes or similar so the reports then 'pop put' very quickly.

    The hard bit is organising the data properly and getting the SQL tuned for the ETL phase.

  • Definately depends (Score:3, Informative)

    by sbryant ( 93075 ) on Wednesday May 12, 2004 @04:14AM (#9124846)

    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

  • portability, reports, etc (Score:4, Insightful)

    by adamf!csh ( 204125 ) on Wednesday May 12, 2004 @09:21AM (#9126284)
    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.

  • 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 j
  • Putting business logic in the database may be faster for a single client, but what happens as you get lots of client? I would think it would be easier to cluster the application server as opposed to the database server, so put the busines logic there.
  • It depends.

    There are many ways to query

    You can use embedded-SQL, stored procedures, or perhaps (if you're careful|lucky) pull off similar performance from a prepared statement.

    In many cases, the real overhead isn't the query, but merely passing the large amounts of data from the db to the app server. It depends.

    THE issue with web based applications**

    I run into the portability vs. speed issue all the time. It's THE fundamental issue with web-based architectures. OO Design on a large-scale, web-ba
  • You have posted a good architectural question yet nobody here could truly answer that for you with the general description of your system.

    My experience is that Shit Flows Downhill so head higher up the hill and ask a person more closely involved with the customer, contractor, etc..

    This might be a good time for an evolutionary prototype where you create a business tier class that allows for more portability. You then create some PL/SQL, T-SQL , (whatever RDBMS you are using) code and test the perform

Slashdot Top Deals

Algebraic symbols are used when you do not know what you are talking about. -- Philippe Schnoebelen

Close