There are some who will tell you it can’t be done – that porting an application from MySQL to Oracle, or PostgreSQL to Sybase, or MS SQL to Informix will only ever be a monumental task. It is true, the difference between vendors is significant and this does present problems, particularly when trying to leverage vendor-specific features in an application. The obvious solution is to employ only the most simplistic of queries, but this can severely limit an application. Fortunately, there are a few tricks to maximising the portability of SQL amongst the major database types. In this tutorial, we start by covering these basic principles, and then we take a look at how a database abstraction layer (DAL) can be used to leverage vendor-specific features without over-simplifying the business logic in the process. Daniel Winter explains all.

Portable naming conventions

 
Identifier lengths               

Using SQL-92 as the lowest common denominator, make sure all data identifiers are 18 characters or less. This refers to constraints and column names as well as tables and views. Although SQL-99 and MS SQL will permit up to 128 character identifiers, there is rarely a reason why a name must be longer than the suggested 18. Sticking to this limit also keeps query lengths down, making them easier to read as well as occupying less memory.

Special characters

Using special characters in identifier names is asking for trouble. Even if the database engine supports symbols such as $, # and @, using them can limit the system’s portability to another DBMS.

The ISO-11179 Standard

This standard covers the naming conventions for metadata and, although it is quite general, provides some basic guidelines that can be applied to SQL. In section 4, the ISO-11179 standard provides 5 simple rules for scalar data elements. They should:

a) be stated in the singular

b) state what the concept is, not only what it is not

c) be stated as a descriptive phrase or sentence(s)

d) contain only commonly understood abbreviations

e) be expressed without embedding definitions of other data or underlying concepts