Developing financial application

Question

My boss wants me to develop an application very similar to an accounting system (that will be a part of another big system, written in ruby (using rails) and javascript (using extjs))

How can I start?

For example, I had a plan to use mongodb for our system, but now I'm not sure because of luck of transactions and ACID in mongodb.

1. What DB engine you can recommend to me?
2. Where can I read about state of art in developing of financial systems, like patterns or something like that?
3. Maybe there is an open source system that I will be able to easily integrate with mine?

Answer 1

For the first part of your question I would recommend Postgres, due to it being properly ACID compliant and a much better alternative than MongoDB which doesn't support transactions across multiple collections and MySQL since MyISAM doesn't support transactions at all and InnoDB is just a poor implementation of Postgres.

If it turns out, you don't need multiple transactions, I would still stay with Postgres due to the fact that MongoDB requires a lot of infrastructure to properly take advantage of the horizontal scaling benefits (mongos, replica sets, shard clusters, config servers, etc) and it is the horizontal scaling which is mainly what mongo is all about (that and a non-relational data model).

The second and third part of this question is a bit vague, firstly ignore the javascript for now, Javascript should be added as a nicety, rather than as the only way that the application works (http://en.wikipedia.org/wiki/Unobtrusive_JavaScript).

Read up on transactions - how they work, row level locking and data modelling.

Then you can consider what you think your application is required to do before you think, how.

Answer 2

Firstly, your instincts about MongoDB are correct - it is not suitable for handling this kind of application.

Any decent RDBMS that has transactions is fine. Some will argue nothing less than Oracle will do, others with a pet hate of MySQL will insist on Postgres, but here's all you need to know:

Make sure columns holding financial data are DECIMAL and do not use FLOAT.

I can't stress this enough - if you use FLOAT, you will see rounding errors all over the place, and your boss will hate you. Just use DECIMAL, understand the DECIMAL type and use it.

You should also be aware that Ruby's floating point arithmetic is not quite what you might think it is (and I've seen all sorts of weird rounding errors), so you might need to monkey patch http://www.hans-eric.com/code-samples/ruby-floating-point-round-off/

I personally like MySQL (using InnoDB), but Postgres is fine, just use what works best in your environment.

In terms of the second part of your query, about "patterns", well, where to start?

I think you need to understand concurrency a little, as you'll need to understand why transactions and row-level locking are needed. Imagine this method used to make a deposit into an account:

def deposit(amount)
  self.balance = self.balance + amount
end

This would trigger a SELECT (to get the current balance), we then do an addition and then an INSERT back into the DB.

Now imagine if in between the SELECT and the INSERT another process came along and did a SELECT. In theory, one of the deposits could be "missed" because it would be over-written by the second INSERT - that's why locking is important.

Some systems get around this by never having a column for the balance - it's derived from the total number of deposits and withdrawals. That obviously has performance implications.

Either way, if you have a ledger of some sort, you need to take this into account and understand it before you start writing code.

As to the final point, there are few decent open source accounting systems out there that are any good in my experience. Accounting normally requires an accountant, and few OSS developers have the skills to produce something that is up-to-date in terms of accounting rules and few accountants produce elegant code. That said, Lazy8web is looking promising to my eye, despite being PHP. It might be good to take a peek at the source and see where it's doing things you hadn't thought about doing things. Good luck.

Answer 3

As someone who has written an accounting system in Python, I would recommend at least an elementary education in double-entry bookkeeping. All the preceding advice is of course also great, definitely do not use Mongo for this, use an RDBMS instead.