Flat File vs Relational Database for Single User Desktop Application

Question

So I keep track of all my finances using an Excel document (and have been doing this for many years) and it's become quite a pain. So for a fun side-project I want to implement something to handle it for me.

Initially I set out using Entity Framework with a LocalDB to handle the backend for me. But as I continually refactor this application, I'm starting to think that maybe a flat file is much better for my needs.

I would like to be able to make changes and those changes not persist until I actually save the state with a Ctrl + S. For example, just because I change my income in a future year, I don't want that to be saved to the database (allows me to develop "what-if" scenarios). It seems that this could be done with an ORM/Relational Database, but there would be a lot of rollbacks.

Other requirements would include about ~500 expense transactions a year, several investment transactions, ~50 income transactions, etc. So after 5 years of use I'm looking at 7200 transactions that need to be stored.

So looking at my requirements with the big two being the number of records and rollbacks vs commits to the database, does a flat file make more sense? Additionally, how to decide between flat files and a full-scale database? Finally, this would be for my own personal use so network concurrency, multiple users, etc. should NOT be a factor.

Answer 1

I would recommend you to continue making improvement in you project. I am sure you will be able to achieve your goal one day (Trying to motivate you). Okay, for your answer I have made a lot of assumption. Let me know if I am wrong, I will try to edit my answer.

Solution: (Assuming) After every transaction you are committing your changes. (Suggestion) Rather than committing, try to maintain your session and try to make changes in #temp tables. The beauty of these table is that they get deleted as soon as the session is over. Now every time you run your application, make a copy of all your tables in #Temp (You may use better name then Temp)

(Assuming your tables names)

SELECT * INTO #Temp1 FROM Investment
SELECT * INTO #Temp2 FROM Transaction
... (All the other tables you have)

(something like this) Now when you make changes from front end, your application should use temp table to save it in temp table. when you are sure that you want to save it in database then copy the from Temp table to your original tables. And commit.

I am assuming here that you have a screen with some text boxes and a Save & a cancel button. Every time you make some changes you hit Save button, it shows you output and saves the data in database. You can have an analysis button which will evaluate your output by using data in temp table and if you like to save click on save. Save will copy data from Temp table to your original tables. And in Cancel button you may either delete temp table explicitly or end the session and you will go back to your original data (like rollback what you do right now [again assuming]).

If you are not a fan of Temp Table then you may use traditional tables but they won't go away after you have ended the session so you have to delete them explicitly on cancel. You may also truncate the table (then you don't have to delete the table, just re-use the table).

I hope this may solve your problem. Good Luck.

Answer 2

Databases store our data in such a way (relational model) that makes it easy for us to query them using a standard (since 1986), universal, documented and tried and tested language: SQL

A database is [a] file(s) plus the data management system.

If for some reason your data do not fit well into the relation model you can either use an alternative format (such as a non-relational database or XML files) or come up with your own. The latter requires that you also come up with the appropriate data management system (e.g. an API).

For example, Thunderbird (the mail client) uses Mbox to store all messages as plain text in a single (flat) file.

Oh, BTW, using a huge ORM such as EF in a fun side-project is probably going to be an overkill.

Answer 3

Entity can be used to call stored procedures too. You can leverage the framework for your simple CRUD methods and provide procedures with proper commit/rollbacks. Flat files are fine if you're doing something as a POC or to test, but if you plan on making the application scalable a database would be the preferred route. Traditional relational databases could be replaced with unstructured ones. If you're dealing with files that could have random number of columns and definitions, using an unstructured database like Firebase or MongoDB would be a good alternative.