-2

We have a finance project which should have a rather large database. The number of tables is not more than 40, but we have about 2 tables which store financial transaction data and the estimated amount of data in these tables is going to grow at 250,000 records per day, and we should keep them at least 10 years to be able to do various reports.

Most of the operation (98%) in these tables is Insert/read but we can separate updatable fields in a separate table.

My questions are the following:

  1. Should I use a NoSQL database for such large amounts of data or SQL database?
  2. What database should I use?
jonrsharpe
  • 115,751
  • 26
  • 228
  • 437
a.fti
  • 39
  • 1
  • 6

1 Answers1

1

"we have about 2 tables which store financial transaction data"

That sounds important. Businesses depend on financial transactions to keep in business, because money underpins everything. So this means:

  1. You need strong data typing (because doing arithmetic with strings is Teh Suck!)
  2. You need strong data schemas and relational integrity, because you need to be sure that the parties involved in those transactions are correctly identified and cannot be lost.
  3. With tables holding around (250000 * 250 * 10) records you probably need the ability to partition (not shard) your tables on a date column.
  4. You need good backup and recovery capabilities (because you don't want to lose any data), including frequent incremental backups and incremental restores (because with tables that big you don't want to backup let alone restore the whole thing).

This points to an Old Skool relational database rather than MongoDB or whatever. NoSQL databases have their use cases, but storing business-critical financial data isn't one of them.

What database should I use?

The best your company can afford. Assuming they want to stay in business for the long term. Remember, it's not the product you're paying for, it's the support.

APC
  • 144,005
  • 19
  • 170
  • 281