Database design for platform independence

Developers should be aware of the hazards of using nonstandard SQL commands such as those found in Oracle, Microsoft, and MySQL systems. Instead, you should use ANSI SQL, which is a platform-independent database language. Regardless of the database system you use, it should support the ANSI SQL-92 standard if it supports SQL at all. Unfortunately, “should support” and “do support” are two different things. Whenprogramming for DBMS independence, not all systems fully support the ANSI SQL standard. This article provides a list of risks to consider when using nonstandard SQL.

Don’t use stored procedures
In some programming environments, for efficiency and security reasons, stored procedures are the only way programmers can develop database applications. Visual Basic, C, and Java programmers are not required to know SQL. However, in other programming environments, stored procedures are not allowed. Commonly, MySQL is used on an Apache Web server because the software for both is free, reliable, and powerful—but MySQL does not support stored procedures.

Oracle and SQL Server both include “built-in” stored procedures, and some of them are very handy. Unfortunately, relying on them is not advisable—you have no assurance that the database administrator hasn’t blocked, deleted, or modified them. If you find some that you like, you can use their functionality in your own code.

Generally, you can use stored procedures on Oracle or SQL Server but not on MySQL. If you do use them, be sure you don’t use platform-specific commands (discussed later).

Don’t use joins, either
Joins become a problem with Oracle and SQL Server DBMSs because the two systems have fundamentally different approaches to the concept. Basically, joins don’t always work the same on every DBMS, and you can end up with unexpected result sets. Before we talk about the differences and how to get around them, you should understand the basic types of join clauses:

Join
Joins are powerful SQL commands for creating tables from data that is retrieved from multiple sources.

Equi-Join
Equi-Joins are formed by retrieving all the data from two separate sources and combining it into one, large table.

Inner/Outer Join
Inner Joins are joined on the inner columns of two tables. Outer Joins are joined on the outer columns of two tables.

Left/Right Join
Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables.

Compound/Complex Joins
There are also other kinds of joins—left/inner, left/outer, right/inner, and right/outer.

The important thing to remember about joins is to not use them if you want your application to work well with different database servers.

ANSI SQL: CREATE, DROP, ADD, UPDATE, DELETE, INSERT, SELECT
As a rule, the only commands you should use are:

CREATE and DROP for Data Definition Language (DDL).

ADD, UPDATE, DELETE and INSERT for Data Manipulation Language (DML).

SELECT for data retrieval.

If you use case-sensitive table or field names with these commands, or the names contain spaces or other special characters, you should enclose the names in brackets. Doing so also helps to keep nonstandard characters in the names from reacting unfavorably with the DBMS.

Referential integrity: Keys and data types
The definitive reason for using relational databases is to identify relationships between data and maintain the integrity of those relationships. Doing so allows developers to store data in a very efficient way with minimal redundancy. Keys define the relationships. Data types define the rules for storing and manipulating that data. These are fundamental aspects of a database, but syntax may vary among different database systems.

Data types
Only a few data types are supported on every database server. Each DBMS has its own, proprietary set of data types and reasons for using them. Some of them (like Microsoft Access’ AUTONUMBER) are quite handy.

These are the ANSI data types:
BIT, CHARACTER, DATE, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INTERVAL, NUMERIC, REAL, SMALLINT, TIMESTAMP, TIME, VARBIT, VARCHAR, CHAR

These “standard” data types are not supported in Oracle/Access/SQL:
INTERVAL, TIME, VARBIT

Only Oracle supports DATE, but these “standard” data types are not supported in Oracle:
BIT, DECIMAL, NUMERIC, TIMESTAMP

These “standard” data types are not supported in Access:
BIT, CHARACTER, DATE, NUMERIC, SMALLINT, TIMESTAMP

Thus, the only data types you can use with the assurance that they will work everywhere are as follows:
DOUBLE PRECISION, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT, VARCHAR, CHAR

Primary/foreign keys
The syntax for creating a table with two fields is as follows:
CREATE TABLE [MyTable] ([FieldA] VARCHAR, [FieldB] VARCHAR);

To add a primary key, so that each record in the table can be uniquely identified, use the PRIMARY KEY statement:
CREATE TABLE [MyTableA] ([RecordID] VARCHAR PRIMARY KEY, [FieldA] VARCHAR, [FieldB] VARCHAR);

When you create another table, with a field that references the first table, you define that field as a foreign key with a relationship to the first table’s field:
CREATE TABLE [MyTableB] ([RecordID] VARCHAR PRIMARY KEY, [FieldA] VARCHAR, [FieldB] VARCHAR, [FieldC] VARCHAR REFERENCES [MyTableA]([FieldA]))

In the samples, all fields have been VARCHARs; but remember that fields of one data type can only reference fields of the same data type.

ODBC and JDBC
If you’re writing an application that uses SQL database connectivity, it’s probably doing so via ODBC. If it’s a Java application, it’s probably using JDBC (which is simply a Java interface to ODBC).

Because you want your application to require minimal attention from the administrator, it’s important to include all the necessary ODBC drivers for your users’ DBMSs. That way, if users change from one platform to another, they’ll already have the drivers available.

Your application should also programmatically determine the DBMS to which the currently used ODBC driver corresponds. That way, you can enable code that uses the powerful, platform-specific commands on the database server.

Another browser war?
The most popular Web browsers are, of course, Microsoft’s Internet Explorer and Netscape’s Navigator. But no matter which one you get, it supports all current Web sites, right? Wrong. The glitzy stuff, like DHTML, frames, and multimedia are powered by nonstandard HTML tags in both browsers.

Because the programs stray from the international standards, you have to code twice (one for each browser) or code to the lowest common denominator—forgoing dynamic content for static content.

The same problem comes up with database servers. ANSI SQL-92 is the standard to which everybody agreed. However, Oracle, Microsoft, and all the rest have added platform-specific features that break well-written SQL code. So you not only have to code to the lowest common denominator (SQL-92), you have to code with just those features that all vendors have implemented.

Within your own applications, you will have to correctly determine what type of database the application will be using, and where it is located. You’ll also have to gracefully handle any errors generated by the application or a user. You don’t want your code to crash (or to crash a server) just because a file was deleted or a network cable came loose. In well-designed networks, the database server is physically separate from workstations and Web or app servers, so broken connections are commonplace.

Summary
As a Web developer, you need to watch out for the hazards of using nonstandard SQL commands in your applications. This article proposes programming for ANSI SQL as a platform-independent database language. Taking advantage of ANSI SQL reduces the headaches encountered with nonstandard dialects found in commercial database systems.