Open Source Database

What is Open Source

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. That is, the user is allowed to implement, share and further develop the database software to suit various needs.

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.

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

MySQL and PostgreSQL are two primary Open Source server-based database management systems in use. 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.

In conclusion, both PostgreSQL and MySQL 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.

Database Storage Engines

Database Storage Engines:

Database Storage Engines are programs that are integrated into MySQL database management system to manage data tables. Database Storage Engines are also called Table Types.

Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

For example, if you work with a large amount of temporary data, you may want to make use of the MEMORY storage engine, which stores all of the table data in memory. Alternatively, you may want a database that supports transactions (to ensure data resilience).

Each of these different techniques and suites of functionality within the MySQL system is referred to as a storage engine (also known as a table type). By default, MySQL comes with a number of different storage engines pre-configured and enabled in the MySQL server. You can select the storage engine to use on a server, database and even table basis, providing you with the maximum amount of flexibility when it comes to choosing how your information is stored, how it is indexed and what combination of performance and functionality you want to use with your data.

Benefits of Database Storage Engines Availability

Reliability and concurrency are enhanced with new algorithms for physical file interaction. These algorithms eliminate the need to run database console commands (DBCCs) as part of regular maintenance. However, DBCC is still available, and the new DBCC CHECK commands can be run without inhibiting online processing.

Scalability

The storage subsystem, which consists of the physical database files and their layout on disk, supports scaling from very small to very large databases. SQL Server can now support up to 64 GB of physical memory (RAM) and up to 32 processors.

Ease of use

Enhanced administration capabilities help the Database Administrator (DBA) to automate and centralize server management. This also allows easy maintenance of remote servers and applications without the necessity of having a DBA visit every site. Server configuration, managed by a sophisticated algorithm, is dynamically responsive to server usage patterns, freeing the DBA to concentrate on database management and optimization tasks.

Here are listed a few types of Database Storage Engines

MyISAM Storage Engine – MySQL default storage engine. Based on ISAM database concept. MyISAM is not transaction safe.

Innodb Storage Engine – A transaction safe storage engine developed by Innobase Oy (an Oracle company).

BDB (BerkeleyDB) Storage Engine – A transaction safe storage engine originally developed at U.C. Berkeley. Sleepycat Software, Inc. was created in 1996 to continue BDB development. Sleepycat is an Oracle company now.

CSV Storage Engine – A simple storage engine storing data as text files using comma-separated values format.

MEMORY (HEAP) Storage Engine – A storage engine storing data in computer main memory.

BLACKHOLE – A /dev/null storage engine (anything you write to it disappears)
We also have many other types of Database Storage Engines available for use.

To see the MySQL storage engines available on your server, use the show engines statement:

show engines;

However, in the output, only if DEFAULT or YES appears in the “Support” column is an engine available for use.