Database Systems Compared

My first experiences of a computer started with DBase III+ which is now dBASE, then went on to Foxpro, now Microsoft Visual Foxpro. I have since used Filemaker Pro, Microsoft Access, Microsoft SQL Server, MySQL, PostgreSQL, SQLite and HSQLDB. I have not yet used IBM DB2, Oracle. Wikipedia has a list of database systems.

Having worked with this range of database systems and having done copious amounts of research into DB2, Oracle and other DB systems I have not mentioned, I like answering the age old questions. Which is the best database system?

Ah! if only it was that simple. There is no database system that is appropriate for any given requirement. But then, if you have been in the technology sector long enough, you would already know that. It’s all about using the right tool for the job.

I separate these systems into two broad categories and Oracle. There are the Desktop based database systems:

  • DBase
  • Foxpro
  • SQLite
  • HSQLDB
  • Filemaker Pro
  • Microsoft Access
  • MySQL

DBase, FoxPro, Filemaker Pro and Microsoft Access are essentially a GUI frontend that has a database backing.

Access is the best choice for this purpose under the majority of circumstances. Filemaker Pro is relevant in some. The usual reason to use DBase or FoxPro is simply that the developer is used to it. This is not a good enough reason.

I have used DBase III+ for developing an office management suite back in 1994. I have since used Filemaker Pro to develop a simple contact management database in 1998, Microsoft Access to develop a patient management system for a clinic.

SQLite, HSQLDB and MySQL are database engines that are to be utilised by popping a frontend on top; sometimes the frontend is Microsoft Access. Microsoft Access can also be used for its database engine.

Access is usually the worst choice for this except as a stopgap. There are exceptions to this. One is for a web frontend if the site is not too busy and its running on a microsoft platform. You don’t have to go to the hassle of installing anything on the server. The drivers will take care of it all.

HSQLDB becomes an obvious choice for a light java based application and SQLite for any other lightweight applications.

MySQL is substantially more powerful and scales a lot better. I include it in this section because it is a server grade database system that can also work well in a desktop environment.

I have used Access for several web based systems and I have used HSQLDB for unit testing hibernate and for a quick and dirty MP3 library that linked into musicBrainz. I have used SQLite in passing to be utilised by open source products.

I have used MySQL with an Access frontend as a management suite for a website as well.

And we have the server based database systems:

  • MySQL
  • Microsoft SQL Server
  • IBM DB2
  • PostgreSQL

MySQL was used as the backed database system for the edFringe.com website. This was the perfect choice since the most important requirement was speed. Particuarly with the Query Cache and Master Slave replication, MySQL was the best choice.

SQL Server was used as the backend system for an online course for the Scottish Enterprise around 1999/2000. While MySQL would have been a good choice this, it was not of production quality at the time.

We have also used Ms SQL Server for an insurance company since all the infrastructure was based on Windows and PostgreSQL did not have a viable Windows version at the time.

We use PostgreSQL for megabus. While speed is absolutely critical, it is a ticketing system which means that transactionality is absolutely critical.

While MySQL now has transactionality with innodb, it is still nowhere near as good as the transactionality provided by PostgreSQL through MVCC (Multi-version Concurrency Control). We could have used Ms SQL Server but the cost savings are dramatic.

To summarise, each system has a specific use, specific strengths and weaknesses and which should be used is highly dependent on what it is to be used for. I am hopeful that the summary of what we have used each of these systems for us useful in determining which one is best placed to solve any specific problem 😀

We have not yet used Oracle and it was a strong contender for megabus but the serious heavyweight functionality provided by Oracle comes at a price and it is not yet a cost effective option.

Foxy Web

Since Firefox 2.0, I have never felt a desire to use Internet Explorer. There have been times when I have used IE, either out of a need to test a website on the browser or purely as the first step to downloading Firefox.

According to W3C, as of November 2008, IE(6/7) dominate 46.6% of the market with Firefox at 44.2%. Compare this to November 2007 when IE (5/6/7) dominated 56% of the market and Firefox only had 36.3%

It is interesting to note that between Nov 2007 and Nov 2008, Linux adoption (as far as internet browsing is concerned) went up a meagre .5% from 3.3% to 3.8%.

This means that a very large proportion of the firefox users are from the Windows Platform. Why is this impressive? There is technically no reason for a user on Windows to download Firefox. Windows comes with Internet Explorer, which should be adequate for all the internet browsing needs.

If 44.2% of all windows users went to the effort to download, install and use firefox instead of Internet Explorer which comes pre-installed, let me ask the question – if Windows came pre-installed with Firefox instead of Internet Explorer – how many would go to the effort of downloading and installing Internet Explorer.

While it is possible to install Internet Explorer on Linux, it might be a little unfair to answer this question based on the number of Internet Explorer’s running off linux. People who run linux have proven to be biased against Microsoft anyway, so it would be a loaded statistic.

While I have no doubt in my mind that Firefox is better than Internet Explorer, I still don’t feel that Firefox is perfect. It still feels far too bulky, with disproportionate memory usage and it is still not as fast as Safari in terms of page display.

Sure, the addons and themes functionality is great and useful. However, it would be nice if it was faster to load, faster to use and just felt more lightweight… like Safari does….

Having said that, I am not going to switch to safari. I like the browser but it is still just not as good as firefox.

One of the points of open source software, should be to bring all the benefits of all the competing pieces of software into one but it just doesnt work like that. If Firefox had all the benefits of firefox as well as the benefits of Safari, I am sure the adoption rate would be far higher…

Lets take it one step at a time… I vote for firefox feeling a lot quicker and snappier for a wishlist… 🙂