Schemaless financial data, and NoSQL?

Question

We have an application that works with financial data that is schemaless. More accurately, the shemaless data is information about an order, where fields are customized by the merchant. Consistency and durability is important.

Because of how dynamic our data is reporting is very difficult. Each record can vary slightly, or be completely different. If we continue to use a relational database, it appears our only option is to serialize the "document" as a blob. The reporting would have to be done separately, perhaps by copying data into a common structure defined by user defined reports (each "report" would have a custom table).

Another option is a document-oriented NoSQL database like MongoDB. After doing some research it appears that most people wouldn't trust a NoSQL database with financial data because it relies on BASE rather than ACID.

I seem to have found myself in the middle of two completely different use cases. My data fits extremely well into a document-oriented database (MongoDB), but I need the reliability of an ACID database. At the same time complex user-defined reports are a necessity.

So I seem to have three choices:

1. Use two MySQL databases: One for storing data (blob), and the other for user define reports (lots of tables).
2. Use MongoDB, which supports large databases, but has a global write lock and is "eventually consistant".
3. Use MySQL to store data (blob), then copy it to MongoDB for reporting. Given that the only index would probably be merchantID, how well would this work?

So which of the three is my best option (most flexibility and durability)? Are there other options I haven't considered knowing I can't change how dynamic the data is? Anyone use MongoDB for reporting in production?

(For our RDMS we use MySQL. Thinking of switching to MariaDB. Programming language of choice is PHP. Thinking about using Sphinx for FULL TEXT search, like searching for someone's name.)

Answer 1

Just a couple of points:

MongoDB is eventually consistent only if you read from secondary nodes. Otherwise, it's consistent.

If you need multi-object ACID transactions, then MongoDB won't work. If you need atomicity, consistency, and durability, you can get that MongoDB if you enable journaling and use write-concern judiciously.