Open Source Database Technologies
Server-based databases for nonprofits
April 8, 2002
This article by Michelle Murrain, an Independent Consultant and member of the Summit Collaborative, describes two prominent open source database management systems. She lays out some of the advantages and disadvantages of going with open source software and explains commons uses for this type of software.
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?
Here you can find a definition of Open Source, along with a list of licenses . 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 wish.
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 for an organization. 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 -helf product, so balance the cost of proprietary software with the potentially increased cost of implementation.
What are Server-Based Databases?
Server-based databases differ from database management systems such as FileMaker Pro and Microsoft Access, which are primarily designed as stand-alone 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 on 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 Windows. In general, however, Unix is the better OS for multi-user database applications because of its stability and scalability.
Primarily cost, stability, and security are the reasons to choose an Open Source server-based database management package, 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 nonprofit sector is for Web-based databases. Many nonprofits use server-based DBMS for their interactive Web sites, 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 server-based DBMS is to underlie large shared databases, such as client management databases and financial packages.
What if you are already using Access at your organization? 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 more robust and secure, because it is server-based.
Should you use MySQL or PostgreSQL?
MySQL is simpler and smaller, and therefore 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, inexpensive 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.