Whenever you create a more involved project, you often think of how to break down the application into persistance layer, management or application layer, presentation layer. A major part of this decomposition is the decision of where to maintain your business logic with two possible routes to follow:
The application layer
You maintain the rules in your Java, C#, Perl or god forbid PHP, and use the database to retain and manage the data.
- A vocabulary as wide as the eye can see for the development of the application – you are not limited by the pl/sql or T-SQLs of the world.
- No need to maintain an additional code base in the database’s programming language or even learn that language
- Rich development environments for the programming language
- You are not married to the database – and this is a big consideration. Databases are often very expensive; programming languages are cheap or altogether free.
- Object-relational mapping needs to exist – whether you do it yourself or use something like Hibernate; you may need to learn how to use Hibernate
- Programming languages are often slower in execution
- Not portable between languages – you are married to the language (as opposed to the database…)
The persistance layer
Business logic is maintained in the database layer through stored procedures and user-defined database functions.
- Speed – databases are normally faster and more optimized than the code you wrote
- Lightweight application code with simple object-relational mapping
- Application-layer portable – you can port your code in relatively simple fashion to other technologies
- As mentioned above – database licensing can be very expensive
- Developing and testing stored procedures and functions can be hairy as the development environments are not as robust and often consist of glorified text editors
- Separate code-base to maintain, often more difficult to maintain in revision control
- Requires knowledge of the database-specific programming language. Some databases can compile high level language code, though (Java, C#, etc.)
I found the latter solution more logical but then, I spent a lot of time learning T-SQL. Still, most of the front end code I started out with was in Cold Fusion, which forces you to move as much as possible into the database. PHP, is just as unappealing and T-SQL (or any other database language) appears refreshing compared to it. Nonetheless, this approach allowed me a relatively quick porting to Java and JSP.
It is very much a chicken and egg consideration. Whatever works for you will do, but in a world where you can get the database for free, a great IDE for free and application servers and language compilers for free, we are in a happy place where we can decide and not spend a fortune.
This article discusses this database-centric approach.