I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please forgive me for a long post wanted to ensure much detail and it may sound like a rambling I'm just sorting my thoughts of this project as much as I could.
I would like to ask if there any concerns about this database design I am going to implement in MS SQL.
My main objective is to make our family's personal finance easier if a solution would involve MS SQL v12 (the free version) as a backend and the frontend is I'm unsure (?VBA Excel via ADO/?Powerquery via ADO/?C# program via ADO within Visual Studio 2015 Community with Web support so iPhone/iPAD could review result over personal network). Any thoughts on the frontend given this backend design?
The tables chosen as follows:
obvious field types isn't a concern at this time. Meaning of word Term in Goals table helps classify short/medium/long term.
Users(**ID**,Name,Addtimestamp) Reports(**ID**,Type,SubReportID,Addtimestamp) SubHeadings(**ID**,Type,Description,Addtimestamp) Transactions(**ID**,Datetimestamp,Description,Amount,Addtimestamp) Goals(**ID**,Term,Priority,GoalDetail,TotalCost,Duration,Addtimestamp) Targets(**ID**,Datetimestamp,Description,MonthlyCost,Addtimestamp) Strategies(**ID**,*GoalID*,*TargetID*,Addtimestamp) Evaluations(**ID**,*StrategiesID*,GoodDetail,BadDetail,Addtimestamp)
The relationships between tables are defined as follows:
- Users "makes" Transactions 1:M
- Transactions "identifies" Users M:1
- Reports "summarise" Transactions 1:M such as Assets / Liabilities for net worth
- Transactions "creates" Reports M:1
- Reports "contains" SubHeadings M:1 such as Cash, Property, Current Bills
- SubHeadings "classified for" Reports 1:M
- Transactions "involves" Goals M:1
- Goals "requires" Transactions 1:M
- Targets "is associated with" Goals 1:M
- Goals "has" Targets M:1
- Targets "supports" Strategies M:1
- Strategies "contains" Targets 1:M
- Strategies "reviewed by" Evaluations 1:M
- Evaluations "is checked against" Strategies 1:M
Views from tables I will need but this only a rough sketch/guide.
There will be 4 members of the family as Users in database
Hoping Report table can calculate net worth as monthly figure instead yearly
SubHeadings is populated according to how the report containing headings, I believe queries can help me ensure all outputs from Transaction table is broken down by months Jul to Jun per Financial Year.
Transactions is manually entered at the moment but considering transforming data after importing via PowerQuery from my financial institution into Transactions.
Strategies (per financial year like 2016/2017) with Targets (like 5% decrease of say Telecommunication expenses) I am hoping MS SQL functions could or the frontend can make like "GoalSolver" in Excel to find a optimal solution given that populates Goals table to get me there. I can then track against actual for variation afterwards.
Main function of the front end would need to inital populate the tables via views? firstly, then frontend would receive manual input to place data via views into tables including transactions. Reporting will list how it goes for budgeting, forcasting is how the Strategies->Targets->Goals is played around and scheduling is sorting some transactions according to Goals's Priorities order where (I haven't decided if a lower number is a higher importance or vica versa).
That's about it I have in my thoughts for now.
Thanks and kind regards, Peter.