Approaches to Web Development for Bioinformatics

Relational Databases

In this section:

• Introduction
• Setting up a Schema
• Accessing a Relational Database with PHP
• Accessing a Relational Database with Java

Introduction

Relational databases are useful as a central location to store data. It may not be necessary to use one if your application purely reads data from somewhere, performs computations, and returns a result to the user. In that case, the 'somewhere' may as well be a flat file. In the section Working with Data from Public Databases I discuss that kind of data processing. In this section, however, I will discuss the use of relational databases for inserting and updating data and then reading back data from a web user interface.

With development of software that exploits relational databases were are touching on an area that can quickly become too difficult for people other than professional software developers and may need professional database administrators to maintain these in production. However, databases are such an integral part of developing web applications that some knowledge of them is important for most people writing web applications.

A relational database is a better fit for storing data that can be created or updated by users on-line because it has built in capabilities (transaction processing) to prevent corruption of data by concurrent updates. Such a case may be where scientists are inputting data from laboratory tests into the database. If the database were a flat file the application (your) program would have to perform the appropriate locking and unlocking to ensure that the file did not become corrupted. Relational databases also have the capability to perform queries on the data using Structured Query Language (SQL). You may want this to, for example, search for a particular set of lab tests and sort the results.

There are a large number of free and commercial relational databases available. MySQL⁴⁰ and PostgresSQL⁴¹ are two of the more popular freely available databases. Oracle⁴² and Microsoft SQL Server⁴³ are a couple of the more popular commercial databases. Trial versions of Oracle and Microsoft SQL Server are also freely available. The different databases support many different capabilities but they all support SQL and thus allow a standard way to access data. Most of the databases, with the exception of Microsoft SQL Server, support most of the operating system you are likely to consider using. The databases differ primarily in their ease of use (the most important consideration for me) and a long list of features that you are unlikely to use developing medical or bioinformatics applications.

Usually, you will use SQL directly to define a database schema (tables) and to insert initial data. However, your web application will need an additional layer to execute and return data from the database. This layer is usually called a database driver. Usually, each programming language will support one or more interfaces to access that driver. I will discuss these below for several of the programming languages that I have been discussing in the article.

Please send ideas and opinions by email at alexamies@gmail.com.