Personal finance app database design
After searching around for a minimalistic money tracking/budgeting app, I decide开发者_JS百科d to build one for my own personal use.
However I'm unsure with part of the database design. Basically at the moment, I have an entries table which obviously stores data about each transaction, whether is credit or debt etc.
The dilemma that I have is, I don't know if I should create another table to store the current balance of each account or if I should populate it dynamically by subtracting the debits from the credits.
Part of me is saying that as the entries table grows the ability to generate the balance for each account will get slower (yes premature optimization is supposedly evil), but it also seems unnecessary to add another table when I can calculate the data from existing tables.
Thanks
EDIT: Sorry I may not have been clear, I understand how to implement either method of creating the account balance. I was more looking the advantages/disadvantages of either method as well as what would be the 'best practice'. Thanks very much for the replies!
If I were to design a minimalistic accounting application, I would probably do something like
ledger
-------------
key INT(12) PRIMARY KEY
account_id INT(10)
category_id INT(10)
trans_type CHAR(3)
amount NUMERIC(10,2)
account
------------
account_id INT(10) PRIMARY KEY
created DATETIME
name VARCHAR(32)
...
category
------------
category_id INT(10)
name VARCHAR(32)
...
The column key
would consist of a date and a zero-padded numeric value (i.e. 201102230000
) where the last 4 digits would be the daily transaction id. This would be useful to track the transactions and return a range, etc. The daily transaction id 0000
could be the account balance at the beginning (or end) of the day, and the id 0001
and up are other transactions.
The column trans_type
would hold transaction codes, such as "DEB" (debit), "CRE" (credit), "TRA" (transfer) and "BAL" (balance), etc.
With a setup like that, you can perform any kind a query, from getting all the "credit" transactions between any given date, to only the account balance at any given date, or date range.
Example: fetch all credit and debit transactions between 2011-01-01
and 2011-02-23
SELECT ledger.*, account.name, category.name
FROM ledger
JOIN account
ON ledger.account_id = account.account_id
JOIN category
ON ledger.category_id = category.category_id
WHERE (ledger.trans_type = "CRE"
OR ledger.trans_type = "DEB")
AND ledger.key BETWEEN 201101010000 AND 201102239999
ORDER BY ledger.key ASC
Example: fetch all transactions (except balances) between 2011-01-01
and 2011-02-23
for the account #1
(ex: Mortgage)
SELECT ledger.*, account.name, category.name
FROM ledger
JOIN account
ON ledger.account_id = account.account_id
JOIN category
ON ledger.category_id = category.category_id
WHERE ledger.trans_type <> "BAL"
AND ledger.key BETWEEN 201101010000 AND 201102239999
AND account.id = 1
ORDER BY ledger.key ASC
So there you go, flexibility and extensibility.
For a personal financial database today's relational database systems are plenty fast enough to calculate the balance of multiple accounts dynamically. You don't need a column to hold the current balance. Even Microsoft Access is fast enough. I know this because I built and use a personal financial database in Access. It might even be what you were originally looking for. You can read about it and download it at http://maiaco.com/software/ledger/index.php
I am actually working on just this website idea right now and the way I've setup my database is:
TABLE account
id
account_name
current_balance
TABLE transaction
id
account_id
payee
date
amount
category
And whenever a new transaction is added I update the account's current balance.
FYI, I hope to launch my site within a month and if you're interested in using person's site, just check out my profile.
I would think a single table read would be better and allow for more flexibility in the future. You could eventually track averages for balance, credits and debits.
Don't store calculated values in tables unless you need to for performance reasons. I would use a View to exposes the calculated values instead.
精彩评论