Dr. Paul Dorsey
Dulcian, Inc.
July 24, 2007
Many systems projects strive to achieve “database independence.” This entails eliminating the system's dependence on any specific database and severely limiting any database usage. In these types of projects, the database is used to store all persistent data, but there is no code implemented in the database. In the purest form of this concept, even transaction control is not handled in the database, but instead controlled by the application logic.
This paper will examine the roots of using a database-independent approach, discuss the costs and benefits of the approach and propose an alternative.
The motivation for making systems database-independent comes from several sources.
The trend towards database-independence began in response to the high cost of database licensing. Historically, Oracle has been a very expensive database engine to run. Annual license fees for large companies can run into the hundreds of thousands of dollars just for the database. Organizations like the idea of not making applications dependent on a particular database so they can easily switch from one database to another. If all of the application logic is also not dependent on the database, then it is usually an easy matter to switch from one database to another.
COTS vendors especially like the concept of database independence. They recognize that many companies are heavily dependent on one database or another and want to be able to sell customers using any database.
There must be user interface (UI) developers writing in Java EE, .Net or some other framework in order for database independence to be relevant. An organization that is database independent doesn't need database developers or the need for database experts is greatly reduced. Application developers are usually not skilled in database techniques and they frequently write poor quality database-side code when it is needed. Their application logic tends to be written better than their database server-side logic.
There are two clearly defined parts of an application: the database and the UI. Database tools are still evolving and improving but are much more mature and stable than UI tools. In the database, each new release adds features to make interaction with it more efficient and faster, but the basic underlying model remains constant.
The way in which database logic code is created in an Oracle system has certainly changed, but not nearly so drastically as the ways in which UI architecture has evolved over the last few years. A database programmer who has fallen behind trying to keep up with the latest techniques can still read and understand code written using state-of-the-art technology. Also, code written a few years ago is not that different from code written today. This code can easily run on the same systems and there is little motivation to refactor the code unless performance improvements are required.
During this same time period, UI architecture has been evolving very rapidly. The techniques considered to be best practice just a few years ago was to create JavaServer Pages (JSPs) with no framework at all. For many developers, this evolved into a JSP/Struts framework, and most recently to a Java Faces (JSF) environment.
Service Oriented Architecture (SOA) has added even more complexity. Even the best development teams are trying to figure out how to best use this design concept. Many efforts to adopt a SOA approach fail miserably in their early attempts.
Organizations tend to not switch from one database platform to another. The commitment to a database is a very expensive one. Learning how to maintain and use these platforms is a significant investment.
Changing from one UI architecture to another is much more common. Organizations frequently shift back and forth between Java EE and .Net. It is even more common to evolve the particular flavor of the development environment to keep up with best practices.
If the database environment is much more architecturally stable than that of the user interface and organizations switch UI architectures more frequently than database architectures, shouldn’t we be trying to be UI architecture-independent rather than database independent?
The alternative to a database-independent approach is a "thick database" approach. Rather than trying to minimize use of the database, instead code is placed in the database whenever possible.
Using a thick database approach can be described as a "Micro-Service Oriented Architecture" approach to application development. The application is divided between the database and user interface portions. A precise communication protocol is defined to allow these two system parts to interact.
There are two key features involved in "thick database thinking":
Using a thick database approach does not simply mean stuffing everything into the database and hoping for the best. For example, using a tool like Oracle’s Application Express that is itself stored in the database and generates HTML pages from the database is NOT an example of the thick database approach. The idea is that creating a thick database makes your application UI technology-independent. If most of your logic is implemented in database objects, changing from one UI architecture to another becomes much easier.
The thick database approach entails creating reusable UI technology-independent views and APIs that reduce the complexity of UI development. The database can provide whatever objects are necessary to reduce the burden on the UI developer.
Database independence comes at a cost. The differences between code in the database and code in the application server are dramatic.
There are numerous advantages to using a thick database approach. The most important ones are summarized here.
Using a database-independent approach, all SQL needs to be built and sent between the application server and the database server. Unprocessed data then needs to be sent to the application server prior to any processing taking place. Using a thick database approach, this same logic would reside and be executed in the database. Only the request and the processed response would be sent from the database server to the application server. In practice, this will decrease the network traffic by over 99% for complex routines.
UI-based transaction control is also quite expensive from a network traffic perspective. Each time a transaction is left in a "dirty" state, the information must be stored in some central location (usually the database server) since the next request might not come from the same application server. Each time a transaction occurs, an additional round trip must be executed to retrieve that data. At the close of the transaction, the data must be sent back to the central location.
It is much easier to write data-intensive code in the database. If the code needs to access the database and update that same logic, having the logic near the data is easier. There are also lots of techniques that can be used when writing server-side code that are specifically designed to maximize performance. The expertise of server-side developers tends to be much greater in the utilization of server-side logic than is the case with their UI counterparts. When UI developers write database-independent SQL, they are not taking advantage of any database-specific logic or capabilities.
Data intensive code requires significantly less code when using server-side programming. Using Oracle’s PL/SQL as an example, bulk operations and collections enable fewer context switches and much cleaner code. The number of code lines needed for the core logic of complex routines usually decreases between 50-90% when that logic is refactored to database-side routines.
Less code translates into less development time. Writing code in the database usually cuts complex coding time by at least 50%.
Keeping data intensive coding in the database effectively partitions the project into two parts. Each part is much smaller so it becomes easier to maintain the system.
We should not accept “database independence” as a universal good. It comes with heavy costs in development time, maintainability, performance and network traffic. Organizations are much more likely to change their UI architecture than they are to change their database. Even if they choose to stay with a particular UI architecture, best practices for that UI architecture are certain to drastically change in the next 12-18 months. Therefore, it is just as valid to argue for UI architecture independence as it is for database independence.
There are situations where database independence makes logical sense. If you are building a product where you are trying to maximize your market potential, then a database independent approach can justify the cost. However, if you are trying to build a complex COTS package to support hundreds or thousands of simultaneous users with large data volumes, then database independence may carry too high of a cost.
If you have only UI architecture talent in your organization, you may feel that you have little choice. However, organizations that freely recognize the need to train developers to use the newest UI framework should be equally willing to take advantage of the database where appropriate.