Virtually every architect has wrestled with problem of where to put ‘Business Logic’ and as technology changes and matures I doubt there will ever be a correct answer. The rules stating that ‘business logic’ should always go here or there are stated by people who would only be able to depict application architecture with a set of crayons and a colouring-in book. I have seen applications where none of the 'business logic' is in the database and a whole lot of wrapper classes do everything that a database would do (such as enforce foreign key constraints) - to the other extreme where virtually the entire application is developed in the database. As with everything in software architecture, you need to weigh the pros and cons that are specific to your application. So some pointers:
- Define what you mean by 'Business Logic' - this means many different things to different people and it is a good idea to establish some sort of baseline. One could argue that tables in the database, by virtue of the fact that they represent business semantics, are in fact business logic. Generally 'Business Logic' refers to some sort of procedural programming, but you would be well advised to define, agree and document what functions your business logic performs. You will probably find that 'business logic' spans all layers and technologies - you have to understand what type of business logic goes where.
- RDBMS's do perform some 'business logic' incredibly fast and it would be pointless to try and 'roll your own' mechanism in another layer. Things that are best left to the database include enforcing referential integrity, ensuring primary keys are unique etc.
- Most native database languages (by this I refer to PL/SQL & T-SQL - not Java or .NET in the database) execute extremely fast and almost always faster than an external language. There are some cases that you can see a performance benefit in external applications, but this is usually for bulk operations where plenty memory is available - where you can suck lots of data out, work with it in memory and dump records into the database in one go. As a .NET or Java developer, you will almost always lose an argument if performance is an issue.
- Database vendors are trying to turn their databases into application servers and referring to 'the Oracle database' actually refers to more than just the RDBMS. SQL2005 has a 'Database Engine' (the RDBMS) as well as 'Reporting Services', 'Integration Services' etc. which, although bundled with the database are not really part of the RDBMS. So when assessing the 'extended' functionality that is offered such as providing 'Business Object Services' out of the database, think carefully that this is not the core of the database and the suitability of the database platform for providing the architectural framework needs to be assessed as if it was any other (non-database) technology. My favourite example is a database 'feature' of being able to send email from within database stored procedures; not RDBMS functionality at all! Obviously database vendors want to sell more product and create tie-in to their platform, particularly Oracle who uses the well establish RDBMS brand to punt the rest of their products - be wary of database vendor marketing material.
- On the newer database platforms you can run Java or .NET code that is executed by the database. This is useful in some cases but mostly it is unnecessary because the native languages a frikkin' fast by comparison as the engine has been coded up over the years with them in mind, and the set-based paradigm that we are used to in database languages doesn't translate all that well with more object oriented constructs. Mostly you would use this for accessing some external functionality. Also, the database has less control over these types of applications and badly coded .NET or Java on the database server can bring it to its knees. In SQL2005 the CLR actually runs inside the SQL memory space and processes and is managed by the database engine - allowing the database itself to prioritise, allocate memory or even kill rogue tasks in order to satisfy the real RDBMS operations that are being performed. I know that Oracle9i spawned off JVM's that were in control of the OS, not the database - I don't know if this has changed in 10g.
- Lastly - make sure that you are consistent in where you put business logic. Often the biggest problem is that in one module a type of business logic is in one place and in and in another module it is elsewhere making it difficult to maintain, debug and migrate. Wherever you decide to put the business logic (or parts of it), make sure that it is consistent across the project.
This question has been around for a long time and will there will never be an answer that applies across the board. Like everything in systems architecture, you have to look at the specifics of your project to come up with the best approach.
Simon Munro