Django webapp - 跟踪财务帐户信息
我需要一些编码建议，因为我担心我正在创建臃肿且低效的代码。
我有一个网络应用程序可以跟踪公司的财务数据。我有一个名为 Accounts 的表，其中包含与典型财务帐户（例如收入、现金、应付帐款、应收帐款等）相对应的记录集合。这些记录只是作为外键指向的名称持有者。
我还有一个名为 Account_Transaction 的表，它记录了 Accounts 中所有帐户进出的所有资金交易。本质上，Account_Transaction 表完成了所有繁重的工作，同时指向正在更改的各个帐户。
例如，进行销售时，会在 Account_Transaction 表中创建两条记录。一项记录用于增加现金余额，第二项记录用于增加收入余额。
Trans Record 1:
Acct: Cash
Amt: 50.00
Date: Nov 1, 2011
Trans Record 2:
Acct: Revenue
Amt: 50.00
Date: Nov 1, 2011
现在我有两条记录，但它们各自指向不同的帐户。现在，如果我想查看我的现金余额，我必须查看每条 Account_Transaction 记录并检查该记录是否涉及现金。如果是这样，请添加/减去该记录的数量并移至下一条记录。
在一个典型的工作日内，可能会有多达 200-300 笔以上的交易。因此，Account_Transaction 表将增长得非常快。几个月后，该表可能有几千条记录。当然，这对于数据库来说并不算多，但是，每次用户想要知道应收账款的当前余额时，我都必须遍历整个 Account_Transaction 表来汇总所有记录处理账户名称为“应收账款”。
我不确定我是否以最优化的方式设计了这个。我曾考虑为每个帐户创建一个不同的表（一个用于“现金”，另一个用于“应收帐款”，另一个用于“收入”等......），但通过这种方法，我创建了 15-20 个具有完全相同参数的表，除了他们的名字。这看起来设计很糟糕，所以我采用了这个 Account_Transaction 想法。
这看起来是处理此类数据的合适方法吗？有没有更好的方法来做到这一点，我真的应该采用？
谢谢！
I need some coding advice as I am worried that I am creating, well, bloated code that is inefficient.
I have a webapp that keeps track of a company's financial data. I have a table called Accounts with a collection of records corresponding to the typical financial accounts such as revenue, cash, accounts payable, accounts receivable, and so on. These records are simply name holders to be pointed at as foreign keys.
I also have a table called Account_Transaction which records all the transactions of money in and out of all the accounts in Accounts. Essentially, the Account_Transaction table does all the heavy lifting while pointing to the various accounts being altered.
For example, when a sale is made, two records are created in the Account_Transaction table. One record to increase the cash balance and a second record to increase the revenue balance.
Trans Record 1:
Acct: Cash
Amt: 50.00
Date: Nov 1, 2011
Trans Record 2:
Acct: Revenue
Amt: 50.00
Date: Nov 1, 2011
So now I have two records, but they each point to a different account. Now if I want to view my cash balance, I have to look at each Account_Transaction record and check if the record deals with Cash. If so, add/subtract the amount of that record and move to the next.
During a typical business day, there may be upwards of 200-300 transactions like the one above. As such, the Account_Transaction table will grow pretty quickly. After a few months, the table could have a few thousand records. Granted this isn't much for a database, however, every time the user wants to know the current balance of, say, accounts receivable, I have to traverse the entire Account_Transaction table to sum up all records that deal with the account name "Accounts Receivable".
I'm not sure I have designed this in the most optimal manner. I had considered creating a distinct table for each account (one for "Cash", another for "Accounts Receivable" another for "Revenue" etc...), but with that approach I was creating 15-20 tables with the exact same parameters, other than their name. This seemed like poor design so I went with this Account_Transaction idea.
Does this seem like an appropriate way to handle this kind of data? Is there a better way to do this that I should really be adopting?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
为什么需要遍历所有记录来确定
Accounts Receievable帐户的状态？我是否错过了一些想法，您不能只在 Django ORM 中使用.filter来有选择地选择您需要的记录？随着记录的增长，您可以在报告中添加一些日期过滤。在大多数情况下，您的会计师只需要本季度、本月等的数据，而不是整个历史数据。
向该列添加索引以优化选择，然后查看 Django 聚合 总结数据库中的值。
最后，您可以进行一些保守的缓存，以加快“快速查看”样式报告的速度，在这些报告中您只需要快速获得总数，但您需要小心这一点，以免出现误报，因此在发生任何更改时重置该缓存记录是必须的。
Why do you need to iterate through all the records to figure out the status of
Accounts Receievableaccounts? Am I missing something in thinking you can't just use a.filterwithin the Django ORM to selectively pick the records you need?As your records grow, you could add some date filtering to your reports. In most cases, your accountant will only want numbers for this quarter, month, etc., not entire historic data.
Add an index to that column to optimize selection and then check out Djangos aggregation to Sum up values from your database.
Finally, you could do some conservative caching to speed up things for "quick view" style reports where you just want a total number very quickly, but you need to be careful with this to not have false positives, so reseting that cache on any change to the records would be a must.
为什么不在
Account表中跟踪确切的可用金额？Account_Transaction只能用于查看交易历史记录。Why don't you keep track of the exact available amount in the
Accounttable? TheAccount_Transactioncould only be used to view transaction history.