1

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.

|improve this question
  • Down-voter care to comment? – keelerjr12 Apr 21 '18 at 11:49
  • Technology recommendations are off topic. Using flat files often means creating your own format, which is error-prone. You could use an existing format like XML, though. But I don't think disabling auto-save is a good idea. Instead, make it easy to back up the DB and to roll-back actions? – amon Apr 21 '18 at 12:03
  • After reading the comment form amon I am not sure if my comment should be here: Just use MS Access. It can be almost as easy as using Excel but it gives you much more options - if you want them. – Edgar Apr 21 '18 at 12:17
  • For XML, I assume everything would be read into memory and then queried from that. Or when each entity needs to be used, the repository would need to re-read the XML file for the data it needs? – keelerjr12 Apr 21 '18 at 12:17
  • 3
    There are literally dozens of other alternatives than "flat file" and "full scale database". Lightweight single-file or in-memory databases like SQlite or MS Access, or you could simply use your Excel file as a database, or use some XML or JSON file as storage, or some object serialization format, just to name the most popular approaches. But there is no "right" answer to this, it depends a lot of your know-how, your personal preference and the surrounding non-functional requirements. So voting to close as "primarily opionion based". – Doc Brown Apr 21 '18 at 12:18
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.

|improve this answer
0

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.

|improve this answer
  • 1
    "Oh, BTW, using a huge ORM such as EF in a fun side-project is probably going to be an overkill" -- Agreed -- if there's a source of unnecessary complexity in this project that can be removed without consequences, losing the ORM and working directly with SQL (or a NoSQL db) is probably the way. – Jules Apr 21 '18 at 14:24
  • I don't think this answers the question; the OP already knew this. – reinierpost Apr 21 '18 at 17:40
  • @reinierpost I am sorry, but my answer does answer the original question: does a flat file make more sense? Additionally, how to decide between flat files and a full-scale database? – dpant Apr 21 '18 at 18:06
  • @reinierpost As far as commits and rollbacks are concerned I can't see the point for such a small application. It can perform/present the necessary calculations (maybe powered by a nice component, e.g. a grid) and persist everything whenever the user clicks the Save button. – dpant Apr 21 '18 at 18:22
0

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.