Best DB approach for a lot of updates

Question

Recently I built an app that gets that from SVN repositories to run a few metrics.

What I do is basically:

1) Read data from an existing SVN repository and save this data to a database (with only two tables Commit and Modification - which includes the list of files that were changed in an specific commit)

2) After, I go through all the data on the database, looking for information like average time between commits of a particular developer, how many files each developer changes on average, etc.

3) Then, I store those information on spreadsheets. For example: in the case of time between commits, I create an CSV file for each developer, and each line of this file represents a commit, storing the data of the commit and how many days since the last commit:

Having this info, I calculated the average by adding the numbers on column B and dividing the sum by the number of rows. So, I have another CSV file that sums up the average number of days that each developer on the team takes to make a new commit.

I follow this approach for many other information, like how many commits on average each developer makes by month, etc.

The main issue here is that whenever a new commit is made, it is not only stored in the database, but the CSV file for the developer that made the commit needs to be updated with the new information (and so does the file that sums up the averages of the team). I've been thinking that having spreadsheets might not be the best approach, since it's not very easy to update them. I'd considered discarding the spreadsheets and expanding the MySQL database that I'm using for commit information, but it didn't seem the best option.

My question is: what would be the best approach to use in this case? Relational DB? Document, Key-Value, Column Family? Keep the spreadsheets but in a different way (store less information, maybe)? I do a lot of updates and this is probably the main problem with spreadsheets. What would you suggest?

Sorry for the very long post and thank you in advance :)

Answer 1

Relational DB seems a perfect fit for your needs.

MySQL will probably give you some hard time on some of the queries you want to run or you need to off-load that to the application side but that doesn't mean it cannot be done. Depends on the size of your tables too.

Databases with window function capabilities (practically anything else than MySQL) will help you with queries like "time between commits". I'd personally recommend PostgreSQL which is great for analytical queries as well.

Document stores and key-value stores are not meant to be backends for serving analytical data retrieval. You might gain some performance on read/write operations but since your application has to do the heavy lifting instead of the database the number of operations increases significantly. So with those it might even be slower at the end.

How you represent that data at the end is more of a business questions than technical. What your users want. Do they prefer excel sheets over for example an internal websites where they can check? Google Sheets might be a middle ground there. Just an idea.