个人财务应用数据库设计 [英] Personal finance app database design
问题描述
在搜索一个简约的货币跟踪/预算应用程序后,我决定为自己的个人使用构建一个。
After searching around for a minimalistic money tracking/budgeting app, I decided 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.
谢谢
编辑:对不起,我可能不清楚,我明白如何实现创建账户余额的两种方法。我更加了解任何一种方法的优点/缺点,以及什么是最佳实践。非常感谢您的回复!
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)
...
列键
将包含日期和零 - 附加数字值(即 201102230000
)其中最后4位数字将是每日交易ID。跟踪交易并返回范围等有用。日交易ID 0000
可以是一天开始(或结束)的账户余额, id 0001
和其他事务。
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.
列 trans_type
将持有交易代码,如DEB(借记),CRE(信用),TRA(转账)和BAL(余额)等。
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.
示例:获取 2011-01-01
和<$ c $之间的所有信用和借记交易c> 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
示例:获取$ code之间的所有交易(余额除外)2011-01-01 和 2011-02-23
为帐号# 1
(例如:抵押)
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.
这篇关于个人财务应用数据库设计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!