Which open source database is the best option for an accounting-related system?

Question

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 1

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.

Answer 2

My advice? Don't. Better to buy one. People who know more about accounting have written good packages that already deal with GAAP. They have a larger user base than you'll ever have, which will uncover defects faster. This is a classic "buy versus build". There's no competitive advantage to your firm by writing their own. If you're doing it because you're worried about license costs, I'd say you haven't accounted for the development time properly. That's the only way you could justify doing this in-house.

With that said, if you are worried about SQL Server licensing costs, I'd recommend PostgreSQL first or MySQL second as your database of choice.

Answer 3

I agree strongly with answers from duffymo and tuinstoel and others. Reconsider your build vs. buy decision. Let me tell you a story:

While I was working at a mid-sized company (international, >$100M/yr revenue), the CFO decided to replace the financial systems with Oracle Financials. Only that package didn't exactly match the accounting practices used by this company.

So the CFO hired a team of contract programmers, and paid them to customize Oracle Financials to the preferred accounting practices. She sunk 12 months of time, $1 million in programmer wages, plus the initial cost of the software, just to duplicate the accounting system that they had intended to replace.

She said if she had to do it over again, she would buy the commercial package, but adapt the company's accounting habits to the defaults supported by the software. That would be far easier, quicker, and more likely to succeed.

So consider the cost of building your own custom package. Also consider the ongoing cost to your company of maintanance, debugging, and enhancement for that software. Even if buy a six-figure commercial package, that'll probably be less expensive than paying the programmers to develop and maintain such a system.

---

To answer your stated question more directly, I don't think there's a significant difference between PostgreSQL and MySQL that is relevant to your project. Since you are comfortable with MySQL, you might as well go with that.

I would like to offer an obligatory reminder not to use inexact data types like FLOAT or DOUBLE PRECISION for financial data.

Answer 4

For any application that wants to use an open source database, the hands-down answer is Postgres. It's a LOT more "enterprise-ready" than MySQL, not to mention that it follows the SQL standard a lot better. MySQL has improved a lot with its later versions, but Postgres still beats it in every category.

Answer 5

There are open source free accounting systems. Like osFinancials. I really can't understand why you want to build your own system?

Answer 6

I build accounting software on PostgreSQL. It works very well. I would highly recommend it. In fact (shameless plug), you might consider working with us to improve our project and using it as a jumping off point.

There are a couple reasons in particular:

1. LISTEN/NOTIFY gives you an ability to hook other programs into your accounting db when something changes without having to check tables every so often.
2. We've found extremely good performance with most complex queries.

Firebird and Ingres will give you a very rock-solid relational solution. MySQL I wouldn't recommend because you are really tying everything to one app only that can write to the db (sql mode soup means relations are basically a private API instead of the public API they are in PostgreSQL, Firebird, and Ingres), and this means less flexibility down the road.

However, with PostgreSQL, you get a top-notch, extensible development platform in a box. Pace of development is high. It is rock solid. The advanced features are very helpful. You won't be disappointed. We haven't been.

Answer 7

For your application, it won't really matter. Anything from sqlite to MySQL to Postgres would probably work just fine. Pick the one you're most familiar with.

Answer 8

Firebird - http://www.firebirdsql.org/

Answer 9

If you are familiar with MySQL then use it. But select proper database engine instead of default MyISAM

List of Storage Engines

Answer 10

Honestly, any of the usual suspects will do the job. Maintaining the chart of accounts and related data tables is the root issue that drove most all of the relational model. In fact, if you think about the general journal view of an accounting system, all you have is the chart-of-accounts and the general journal, composed of transaction number, date, description, debit account and amount, credit account and amount. Everything else you do is a SELECT on those.

That said, though, there are so many perfectly adequate, well-tested and accepted financial packages, including open-source free (as in beer) versions, that unless you mean it for an etude, a study project, I'd put my effort into googling and selecting one.

Saw your update. The thing is, this is an issue that's mostly going to be determined by nonfunctional requirements. Are you going to distribute the database across more than one server? how much load do you expect? Transactions per second or transactions per day? I've built systems around both in the last couple of years, and it's usually reliability and avilability requirements that are the most decisive: PostgreSQL deals with concurrent updates of a single row more effectively, by enforcing row atomicity and serializing concurrent updates. On the other hand, MySQL seems to deal with really large databases better. Yet a third question is backup --- one of them (I don't recall which one right now) more or less requires some down time to backup.

Answer 11

For an in-house web-based accounting application, you might be better off with Gemstone as a free but not open source object database and Seaside as the web framework. Otherwise known as GLASS.

For an in-house application, you're going to be limited in developer effort. Gemstone, as a smalltalk image, provides the best developer productivity by far. Its support for migrating objects when changing their definition allows real iterative development. Seaside replaces templates by a well-designed domain-specific language for building web applications.

Answer 12

If this is a desktop application you might want to look at SQLite. It's very well known, in the public domain, and not terribly difficult to work with.

Answer 13

Since you don't need stored procedures, take that off your list.

You'll be a lot happier putting business logic in code, not in the database. If you have the chance to start "clean", then use the database for what makes the most sense -- persistence not processing.

Once you make that decision, the subtle differences between MySQL and PostgreSQL go away. Both are relational engines that handle nearly identical SQL. Focus on the things they do best.

Recommendation: Make your application independent of any database peculiarities.