Which open source database is the best option for an accounting-related system?
I am in the early stages of planning and designing a custom accounting application for my firm. My goal is to utilize an open source relational database for the data storage portion and I'm aware of two solid databases that are widely supported: MySQL and PostgreSQL.
For a system that will require transactions, stored procedures, functions, and security, are there any opinions on which of these two databases would be best suited for an accounting application or is there another database I'm missing?
I'm more familiar with MySQL and MS SQLServer 2005, but I'm trying to move away from the latter due to license costs.
Let me add: This is not an accounting need like Quickbooks or Peachtree. This is basically a system that handles accounting for a specific business service we provide. There are maybe two or three systems that cater to this need, are priced in the six figure range before any customization, and would require my small firm to be married to a vendor for the long-term. Thus, we are building the application in-house.
Also, while I appreciate the Buy vs. Build argument, I'd like to move away from that particular religious question because the Buy road was already taken and the vendor failed miserably. Sometimes you just need to do the job yourself and this particular project and budget warrants it.
Thanks for everyone's replies thus far.
Answer
There are four main open-source relational database management systems of note that might be appropriate to this sort of application: Postgresql, MySQL, Firebird and Ingres. There are other systems such as SQLite, but they do not have this type of architecture and are not really designed for this type of workload. Some other open-source database management systems of this type do exist, but do not appear to be strongly viable for some reason, such as a lack of apparent vendor commitment. An example of a system that has this type of issue is SAP-DB.
Postgresql has the best feature set of any of the open-source databases, and support for XA transactions, which you will probably want if your application is a three-tier system and supports transactions of non-trivial complexity. In particular you will want this if you want to do transactions spanning more than one call to the database.
Several commercial variants of PostgreSQL have been built over the years, such as Illustra, Greenplum and EnterpriseDB. Illustra was a commercial release of PostgreSQL which was subsequently bought by Informix. Greenplum is a mofified version designed for data warehousing applications. EnterpriseDB is a company that provides supported commercial versions of PostgreSQL with some value-added software.
MySQL 5.x has a feature set that supports a reasonable cross section of capabilities, but it is not as feature-rich as PostgreSQL. It has more widespread mainstream acceptance and would be the easiest of the open-source database management systems to recruit skilled developers for. Although older versions did not have robust transaction support, transactional storage engines such as InnoDB have been available for some time. The current politics surrounding the acquisition by Sun have generated code forks and the MySQL landscape is somewhat messy, with controversy about quality issues in the 5.1 release. However, MySQL is by far the most popular and best known of the open-source database management systems and is the only one with significant brand recognition outside of open-source circles.
Firebird is an open-source version of Interbase. Last I looked, it did not have XA support but would be fine if your application was set up as a two-tier client-server system. Update: I can't find a definitive specification on this, but the documentation does indicate that it has support for two-phase commit, but what I could find was not specific on whether it supported the XA protocol. The documentation implies that the JDBC driver does have support for two-phase commits.
An interesting variant on this system is Fyracle, which is designed to offer a degree of compatibility with Oracle. This was originally developed for use as a back-end to Compiere, which was built against Oracle and quite tightly coupled to it.
Ingres is now available with an open-source license, but has been greeted with a bit of a collective yawn by the open-source community. However It is quite feature-rich and very mature - I know people who were doing INGRES apps in 1990, and it dates back to the 1980s.