views:

1121

answers:

13

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.

A: 

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.

S.Lott
-1 This is not really the place to get into religious wars about the merits of stored procedures.
ConcernedOfTunbridgeWells
Your comment makes it sound like SPs are lethal even if you're already most comfortable using them. Bad advice.
le dorfier
SP's are not lethal -- but I've never seen them work out well in the long run. Making them part of the decision process is a mistake.
S.Lott
Since there are no merits to stored procedures, it's not religious. It's my experience over the last 20 years of RDBMS usage. Your experience may be different.
S.Lott
SP's are not evil, but is just another part of a project you have to maintain, debug and fix. I prefer doing this on the code and keep a closer control over it.
Ubersoldat
Since I have to maintain, debug and fix the code in my programming language anyway, why split my maintain, debug and fix time between two languages? Why not skip the SP's and just focus on one language for code?
S.Lott
Business logic should reside in the database to ensure data integrity.I've never seen a database that didn't use sps that wasn't bloated, inefficent, inaccurate and poorly designed, YMMV. Database people will tell you the last place they want database code is in the application.
HLGEM
@HLGEM: "Business logic should reside in the database". Cool. Why write applications, then? Why even have an application layer?
S.Lott
+15  A: 

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.

duffymo
This site lists five accounting packages for small businesses, including one from Microsoft. Your website leads me to believe that this might be your business. Buy something and spend the time invoicing billable hours instead.
duffymo
Thanks for the reply. Usually, I'd agree with this sentiment, but in this case the accounting system need is very atypical. There are only two proprietary accounting systems that relate to this particular need and both are priced in the six figure range for just the install. Hence, our in-house dev.
wrburgess
http://sbinfocanada.about.com/od/accountin1/tp/accountsoft.htm
duffymo
Then my second recommendation stands - PostgreSQL would be my open source choice.
duffymo
Thank you, it's my fault for not mentioning that this is basically an accounting+ type of system.
wrburgess
General ledger/accounts payable/accounts receivable/ and what...?
duffymo
It's a hybrid system that accounts for security management. So, it's a type of portfolio management/accounting system. Portfolio management needs seem fairly easy to build a system upon, but the accounting needs are more strict. Thus, I am looking for the database with the more needy side in mind.
wrburgess
Don't forget that a 6-figure cost is barely more than one year's salaray plus overhead for you. Plus you get someone else to blame, and they deal with all the legal stuff that you'd have to worry about. You will need to be earning a very low salary and be very good at coding to warrant writing it.
Jonathan Leffler
I'm still not convinced, but that's your management's problem. This link says that there are portfolio management packages out there, too: http://letmegooglethatforyou.com/?q=portfolio+management+software
duffymo
Well, I don't outsource jobs just to assign blame for problems. Plus, I can't pull money for a solution with none in the budget. I have the business problem, a prototype to use, and an allocation of time from my managers. That's it.I do appreciate the answers to the question about the database.
wrburgess
+14  A: 

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.

ConcernedOfTunbridgeWells
I know of a telecoms system that uses postgres in a mission-critical environment (and I mean lives-at-stake type critical!), its not failed us yet.
gbjbaanb
Nice balanced assessment.
Jonathan Leffler
I suppose it's worth mentioning Derby if in an all-Java environment, as well, but I've not seen a particularly convincing argument for its use.
Calum
+1 for PostgreSQL. MySQL is a nice Web Database, but it does not really have a good history in terms of data integrity, and the "Oops we did it again" blog post regarding MySQL 5.1 does not help either. It's not bad, but i would not use it for accounting.
Michael Stum
+1 for Firebird. Firebird 2.1 include many Fyracle enhancement
Hugues Van Landeghem
A: 

I know this isn't precisely your question but you might find the answers to this question helpful.

http://stackoverflow.com/questions/29044/good-free-alternative-to-ms-access

Onorio Catenacci
+1  A: 

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

tuinstoel
Obviously, we can Google it, but it is nice to include a URL.
Jonathan Leffler
A: 

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.

Scott
+4  A: 

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.

Bill Karwin
"....adapt the company's accounting habits to the defaults supported by the software...." - exactly. Too many companies end up in no man's land by buying and then customizing. You lose all the benefit of buy and incur all the cost of build.
duffymo
For the life of me I cannot understand how anybody could have voted this answer down. An excellent point, and well written.
duffymo
Believe me, I appreciate this stance. I have outsourced almost all of the technical functions of my small firm (20 people) because I usually side with the "Buy" argument, but this particular project is different in terms of needs and budget.
wrburgess
That's the answer, then. Good luck!
duffymo
Well, I think your candor about Buy vs. Build is warranted. The consideration of that debate should ALWAYS be asked when making application decisions. It just so happens that BvB was already decided upon before I asked the question...a question that sounded pretty amateur in my first posting.
wrburgess
A: 

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.

Ross
+1  A: 

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

jussij
+3  A: 

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.

Wayne M
A: 

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

List of Storage Engines

Malx
Yes, it's InnoDB for referential integrity.
duffymo
+1  A: 

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.

Charlie Martin
Thanks for the reply. These are the kind of details that I'm thinking through as I review the options.
wrburgess
A: 

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.

Stephan Eggermont