A framework using the metaphor of bricks

Be Wary of Generic Database Layers

picture by Alejandro via Flickr

Over the years we’ve seen a number of projects where application architects use a generic layer to hide a database. This is a common approach with object-oriented languages accessing a relational database. Application code accesses the layer which in turn accesses the database. The use of a generic layer can be a valuable technique, but it is overused. Some architects seem to be unaware that there are other possibilities.

What is a Generic Database Layer?

A generic database layer is a collection of in-memory data structures that mediate access between an application and a database. The layer is generic in that it supports operations on records but has no knowledge of application semantics. Database layer frameworks, such as Hibernate, TopLink, and Cayenne, provide such a layer. Applications do not directly read and write to the database. Rather they access the layer which in turn accesses the database.

Programmers write application code in terms of the layer and need not deal with SQL. The layer hides the mechanics of database interaction. A generic layer provides logic that inserts, deletes, and updates values and records. The changes can be accumulated in memory and written to the database upon request. The layer maps the activity on in-memory data structures to commands that modify the database.

Advantages of Generic Database Layers

A generic database layer can have a number of advantages.

  • A layer is often an excellent metaphor when dealing with long transactions that can last for hours or days.
  • A generic layer can reduce the effort of building applications and is much better than writing the logic by yourself.
  • A layer can provide fast buffered writing and simple programming yet still leverages relational DBMS technology.
  • Programmers who lack database skills can still work on database applications.
  • The generic layer hides the DBMS and can sometimes make an application DBMS independent.

A CAD editor is a suitable application for a generic layer. A CAD editor locks a portion of the database for exclusive use and checks it into memory. Subsequent reads and writes are made against in-memory data structures. Once work is finished, the data is posted to the database and made available for general use.

Disadvantages of Generic Database Layers

However, a database layer is not a panacea. There are subtle issues to consider.

  • A layer is awkward for short transactions. Database locking is already complex and then you add the nuances of layer locking. Even with documentation, it can be difficult to figure out exactly what layer software is doing. When there is concurrent access from other applications and users, are you reading clean or dirty data? Can you specify transaction scope for writing? Can your attempt to write be invalidated by activity from another user?
  • The developer loses the benefit of set oriented operations, the expressiveness of SQL, and the power of relational DBMS optimization.
  • A layer exposes the data structures of a database to the application. If you change data structures, you may disrupt the application. This runs counter to the goals of encapsulation.

An improper use of a generic layer would be for posting credit card transactions to an account. Credit card applications repeatedly process small amounts of data that are subject to simple operations. At best a layer adds needless complexity.

An Alternative for Short Transaction Applications

There is a better alternative to a generic database layer for short transaction applications.

You can prepare a custom database layer by defining an API. API methods hide data structure. API methods can access the richness of SQL and provide an input/output interface in application terms. You can implement an API with programming techniques, such as JDBC, or with database stored procedures.

The Larger Issue

There is a larger issue to this article than the trade-offs of generic layer technology. There are multiple ways for coupling an application to a database. It is a big mistake to glom on to a single approach and try to use it in every situation. One of our past books (Blaha and Premerlani, Object-Oriented Modeling and Design for Database Applications, Prentice Hall, 1997, p190-193) covers nine different ways for interfacing an application to a database.

A skilled architect should consider all the coupling options, the business needs, and the available technical infrastructure and only then determine a proper approach.

Leave a Reply

Your email address will not be published. Required fields are marked *