WebJunction

This article is designed to introduce you to two particular database management systems (DBMS), called MySQL and PostgreSQL. These are both open source, server-based database management systems.

What is open source?

First, you might ask: What does open-source mean? Generally, open source software is available for free, and the source code is also available. This means that you (or others) can see how the software was actually programmed and change it if you want to. You can find a definition of open source here, along with a list of approved licenses.

The advantage of freely available software is obvious, but why is the source code availability important? Open source software products are most often the collective effort of many volunteer programmers. Anyone can submit bug fixes, security patches, and the like. This means that bugs get fixed faster, programs are more secure, and, in general, as a program matures, it is of higher quality.

One of the primary disadvantages of open source software is that it can be user-unfriendly, and it may be difficult to find someone with expertise in the software to set it up. Also, even though the software itself may be free, it may cost you more to implement an open source software solution than to buy an off-the-shelf product, so balance the cost of proprietary software with the potentially increased cost of implementation. For more information read the article, Going With Open Source.

What are server-based databases?

Server-based databases differ from database management systems such as FileMaker Pro and Access, which are primarily designed as standalone desktop databases for a single user. In contrast, server-based databases, such as MySQL, PostgreSQL, Microsoft SQL server and Oracle, are designed to be used on servers instead of a desktop, and they are meant to be shared by many users. Technically, multiple users can share FileMaker Pro and Access, but as the number of users increases (along with the number of requests for information) performance suffers greatly. That is why a server-based database is a common choice for organizations where many people need to share data.

MySQL and PostgreSQL

There are two primary open source server-based database management systems in use: MySQL and PostgreSQL. Both of these database systems run well on Unix-based operating systems, such as Linux, as well as on Windows. In general, however, Unix is the better OS for multiuser database applications because of its stability and scalability.

Why choose an open-source, server-based database management package? Primarily cost, stability, and security are the reasons to choose an open source solution, rather than, for example, Microsoft's SQL server. Both MySQL and PostgreSQL are freely available, fairly easy to set up, and more secure than Microsoft's SQL server.

The most common use of server-based DBMS in the library sector is for catalog databases. A library can also use server-based DBMS for an interactive Web site, providing data and content to their constituencies. MySQL is probably the most common of the open source database systems used for Web sites because almost all virtual hosting companies provide it.

Another common use of a server-based DBMS is to underlie large shared databases, such as your online public access catalog, patron information databases, financial packages, and the like.

What if you are already using Access? You can set up a Linux-based database with either MySQL or PostgreSQL, and use an ODBC (Open Data Base Connectivity) connection between the desktop Access databases and the server database. This means that on the front end, your users can still use Access, but the back end is much more robust and secure, because it is server-based.

Should you use MySQL or PostgreSQL?

MySQL is simpler and smaller, and thus, generally faster than PostgreSQL. It is also the better choice for Web-based use, as MySQL is most often the only DBMS available in virtual hosting accounts. In addition, there are more users of MySQL, so it would be easier to find someone to help you set it up and work with it.

However, PostgreSQL is much more robust, scalable, and standards compliant, and it can handle multiple transactions easily. (See the links below for more information on these two databases).

In conclusion, both PostgreSQL and MySQL, which are freely available, stable, robust, and secure database management systems, provide a clear, cost effective way to design and implement server-based databases. Through a variety of methods, such as Web-based interfaces, Access front ends, or custom designed GUIs, these DBMS can be used easily in a multi-platform environment.

For additional information:

Offical MySQL site

Offical PostgreSQL site

Some comparisons of the two:

From New Architect Magazine

From Php Builder