Designing a DAL

Building a database abstraction layer is no small undertaking. If there are no suitable DAL libraries available to you, or if you are dissatisfied with their features or performance, there are a few basic principles that may help to follow. Firstly, it is a good idea to separate the functionality of each database into a separate library. Unlike the simplified code examples given above, a base class or interface should be built which caters to the lowest common denominator - probably SQL-92. Each supported database would then be implemented in a separate class library that inherits the base class or implements the core interface. Where applicable, vendor specific features should be used to take advantage of the optimisations available. The base class must then be updated to provide a common workaround for the new DAL feature. Where equivalent features are not available in the other database types, the base class provides the equivalent functionality.

In addition to the object-oriented model described above, it is also a good idea to use a separate SQL statement store for each database type. This way, it is possible to avoid using SQL query strings within the business login of the application. Additionally, if a feature is available to a particular vendor – such as the CACHE keyword in MySQL SELECT statements – then it can be implemented in the statement storage file. Although a little beyond the scope of this tutorial, many portability issues that result from over-complexity can be avoided simply by using better database design principles. A familiarity with joins and indexes and their effect on performance can prove to be more valuable than a thoroughly implemented abstraction layer.

Online Resources

SQL-92/99/2003

http://savage.net.au/SQL

ADOdb for PHP

http://adodb.sourceforge.net

libdbi for C

http://libdbi.sourceforge.net/

Perl DBI

http://dbi.perl.org/

openrico.org/rico/home.page