Databases are like cars and trucks: there isn't one choice that's right for everyone.

General comments

is occasionally asked what the best database is. This is a bit like asking, what is the ideal car? The first question is "what do you want it for?" and the second "what is the budget"?

Some factors worth considering are:

• Hard constraints from the target application (many database related applications only run on certain databases)
• Local experience and expertise
• Scale of the database (the Mack truck versus mini-scooter question)
• Local versus remote/unattended operation
• Reliability requirements
• Ease-of-use requirements
• Performance requirements

The marketing materials from the various database vendors often suggest performance is a critical factor in database selection. In fact most performance problems can be traced to

1. inappropriate or poorly worked out database design
2. failure to execute correctly against the design
3. insufficient hardware: too little RAM, slow disks, small network pipes and the like

Oracle

The 800 pound gorilla of the database market. Oracle is a perfectly reasonable choice as a primary database, although it has some "gotchas" which make consideration of alternatives prudent.

In particular, Oracle is rather harder to administer than some other databases. In fact, as one of our colleagues discovered, it is possible to get an Oracle database so curdled that it is not possible to run the restore procedures.

has done ports from Informix to Oracle. Our experience is that the Oracle cost-based optimizer was not in the same league as the Informix optimizer; it needed a great deal more help from nearby humans if it was to execute queries at a reasonable speed, given a specific hardware configuration.

Oracle's PL/SQL language has some nice features. PL/SQL's debugging support was bizarre, requiring manual intervention by the DBA for setup. Oracle's bulk load/unload procedures -- needed for conversions, synchronization with remote databases, and the like -- are unimpressive.

Oracle's strong suit is its suites: Oracle has a wealth of applications, including much stuff for the web. (Apparently this plentitude is partly due to political issues within Oracle: different factions will construct essentially the same tool in an attempt to garner favor from Ellison.)

SQL Server

The other 800 pound gorilla of the database market.

The current version, SQL Server 2000, appears to represent an attempt to compete with Oracle. We're not entirely clear that it is successful in doing this, but it does represent a very significant step forward from earlier versions. Integration with Access and NT is, of course, a strong suit.

The user interface is clean and easy to use. The data transformation services (DTS) tools are good. (Some shops have acquired SQL Server just for DTS.) The system administration tools are easy to use; features such as automatic running of statistics should be more widely emulated.

The stored procedures execute very crisply. There may be some concurrency issues in aggressively multi-user environments -- would be hesitant to use SQL Server to build an airline reservation system or the like -- but for small N situations SQL Server seems reasonable.

The stored procedure language has a bit too much syntactic sugar for our taste, but not so much as to make it unliveable. The default front-end to the stored procedure language would have been impressive in a DOS context, but is frankly a bit clunky in early 21st century terms.

Informix

has worked with Informix since 1987, starting with version 2 (Informix is now on version 9). has been particularly happy with the Informix Standard Engine (SE) product. This has offered an unequaled combination of ease of use and performance, within its weight class (again, don't build an airline reservation system with this).

In general the Informix line offers a nice combination of ease of administration and performance, making it surprising they do not have more market share than they do. Their cost-based optimizer is good, and has only occasionally required hinting. (Hinting: putting comments in an SQL query to tell the optimizer how to run the query to achieve best performance.) The Informix 4GL language -- a tool oriented to the old 24 x 80 screens -- was (and is) quite good. The Informix stored procedure language (SPL) is fast enough, but curiously limited in features by current standards: primitive exception handling for instance, and relatively few operators. Informix does not offer a lot of GUI tools, but good GUI tools are available from third parties.

has built a variety of Informix databases and applications. These include cable advertising, medical laboratory, and financial applications.

Ingres

built a large cemetery management database in this. Ingres has good performance and a clean design. found especially attractive the fact that all of the reports and screens were fully described within the database itself. This meant that if some small change was required to a field -- say widening it -- the change could often be applied by doing a single update to the internal tables, without need to go the perhaps twenty screens where that field was used.

Ingres was originally an academic product. This perhaps showed in the error messages, which would sometimes have been less confusing if written in Greek, as then no time would have been wasted attempting to decipher them.

Omnis

A fast if somewhat strange product, Omnis is really a hierarchical rather than a relational database. But it gives best overall performance if its native hierarchical features are eschewed in favor of the relational. Omnis is characterized by a somewhat idiosyncratic internal language but very good performance. It has a rich feature set.

Filemaker

As its name suggests, Filemaker is really a flat file database, albeit with some relational features, and the word "relational" featured prominently on the shrinkwrap. It is very easy to build a simple but attractive front-end with this tool. And it is sufficiently simple that much routine database maintenance and enhancement may be done by non-IT personnel.

4th Dimension

4th Dimension is another effective tool at the low end. It has very good facilities for constructing GUI front-ends, and a rich internal language.

It is not a relational database, again not withstanding the statements on the shrinkwrap.

has found 4th Dimension to be a good tool for building user-friendly one person databases. In our experience, its multi-user and networking performance rule it out for use on a larger scale.

Other databases

has worked with a number of other SQL and non-SQL databases as well, e.g. Empress, Access, File, MySQL, Postgres, and so on.