个人理财应用数据库设计 [英] 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)
   ...

列 key 将包含一个日期和一个以零填充的数值(即 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 和 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

示例:获取帐户#2011-01-01 和 2011-02-23 之间的所有交易(余额除外)code>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屋！