衍生帐户余额与简单银行帐户的存储帐户余额？ [英] Derived account balance vs stored account balance for a simple bank account?

问题描述

所以，它像我们正常的银行帐户，我们有很多交易，导致流入或流出的钱。帐户余额总是可以通过简单地对事务值求和来导出。

每个帐户的预期交易量：<每日5次

/ p>

预期的帐户余额检索：每当交易发生时，每天一次，否则。

您会如何建议对此做出决定？
非常感谢！

解决方案

前言

这是一个主观的真理的时代，客观的真理，从一个管理局流出，已经存在了四千年，是我们现在正在编程反抗的事情。

我在会计系统工作了34年，银行系统工作了24年。有一个客观的真实：审计要求。此外，在处理公共资金时，必须遵守立法机关的规定。

您不必实施完整的会计要求，你需要。

相反，执行标准会计要求（其中的部分）之外的其他事情是不明智的，因为这保证了错误数量或负载超过某个阈值，或者系统扩展，您将不得不重新实现。

它还需要说明：不雇用不合格，未经认可的（由管理局）审计员。这是（a）反垄断权力行为，和（b）将有后果，就像你聘请一个不合格的开发商。这可能更糟，如果税务局等上级机关罚款你。

因此，请注意授权的层次结构，并遵守最高的授权。这确保了和平的生活。这确保自动，无需额外的努力，你遵守任何较低的权威。如果你不这样做，即。

方法

>

在不那么原始的国家的标准会计方法是这样的。

此方法适用于具有类似操作的任何系统;需求;

考虑

首先，

1. 不要复制资料。如果可以导出当前余额（这里它很简单），不要使用摘要列复制它。这样的列是数据的复制。它打破规范化规则。




2. 如果使用摘要列，则每当事务处理完成时，更新（如已更改，而不是插入新事务时），摘要列值已过时，因此必须始终更新。这是更新异常的结果。




3. 外部发布。如果余额被发布，如在每月银行对账单中，这样的文件通常具有法律限制和影响，因此发布的CurrentBalance值在发布后不能改变。

   
   
   
   
   

   • 在发布日期后，在数据库中对外公布的数字进行任何更改，都是不诚实行为，欺诈等的证据。

     
     
     
     
     
     • 尝试更改已发布的历史记录的此类行为是新手的标志。新手和精神病人将坚持可以改变病史。但是每个人都应该知道，对法律的无知并不构成有效的辩护。
     
     
   
   

   • ，于2015年4月将您在其银行对帐单中发布的现有余额变更为2014年12月的现金余额。

   
   

   • 这个数字必须被视为审核数字，出版和不变。

   
   



4. 任何必要的更正或调整都会在当月作为新交易进行，适用于前一个月。这是因为适用于月份已关闭并发布，因为在发生并记录后，不能更改历史记录。

   
   
   
   
   

   • 对于不太原始的国家的付息系统等，当发现错误并且具有历史效应时（例如，您在2015年4月发现从证券计算的利息不正确，自2014年12月以来），修正的利息付款/扣除的价值是今天计算的，为错误的天数，并且该总和作为当前月份中的交易插入。再次，唯一有效的月份是当前的月份。

     
     
     

     当然，安全利率也必须得到纠正，以使错误不重复

   
   

   • 如果您在银行计算您的储蓄（计息）帐户的利息时发现错误，单笔存款，构成整个调整值，在当月。这是当月的交易。

     
     
     

     银行没有：更改历史;在每个历史月份申请利息;召回历史银行报表;重新出版历史银行报表。

   
   

   • 同样的原则也适用于库存控制系统。

   
   



5. 所有真实会计系统（即审计机构认可的适用的国家，而不是米奇老鼠包大量）使用双重输入系统的交易，正是因为它防止了一大堆错误，其中最重要的是，资金不会丢失。这需要一个简单的总帐。

   
   
   
   
   
   • 你可能不需要它，因此我不是在这里描述它，但记住它金钱失踪，因为这是你将要实现，而不是一些创可贴的解决方案;
   
   

   • 影响效果的主要问题不在此问题的范围之内，他们是在您是否实现真正的关系数据库（在SQL数据库容器中的记录归档系统，由ID代表）领域。

     
     
     
     
     

     • 使用真正的关系密钥等将保持高性能，而不管表的数量。

     
     

     • 相反，RFS会执行得不好，他们根本无法执行。

     
     
   Scale用于RFS的上下文时，是一个欺骗性术语：它隐藏了原因并寻求解决除了原因之外的一切。
   

实施

1. 将在AccountStatement表（每月每个帐户一行）中包含ClosingBalance，以及StatementDate和其他语句详细信息。

   
   
   
   
   

   • 这不是重复，因为为了审计和健全的目的。

     
     
     

     对于库存，在PartAudit表中的QuantityOnHand列（每个零件每月一行）

   
   
   • p>它有一个附加值，因为它限制了当前月份需要查询的事务行的范围。
     
     
     
     
     

     • 再次，如果您的表是关系，AccountTransaction的主键将是（AccountCode，TransactionDateTime），它将以毫秒速度检索事务。

     
     

     • 记录归档系统，主键将是（TransactionID），并且您将通过TransactionDate检索当前月份，这可能会或可能不会正确索引，并且所需的行将分布在该文件中。

     
     
   
   
   / li>
   

2. 事务表保持简单（银行帐户的真实世界概念事务很简单）。




3. 对于每个帐户，CurrentBalance为：

   
   
   
   
   

   • 上一个月的Statement.ClosingBalance

     
     
     

     （对于广告资源，PartAudit.QuantityOnHand）

   
   

   • 加上当前月份的Transaction.Amounts的总和，其中TransactionType表示存款

     
     
     

     （对于库存， QuantityAffected）

   
   

   • 减去当月的Transaction.Amounts的总和，其中TransactionType表示提款

   
   



4. 在此方法中，仅当月的交易处于通量状态，因此必须<派生。




5. 事务表中较旧的行可以是清除。




6. 当然，有关会计系统的任何代码是必不可少的使用真正的OLTP标准和真正的SQL ACID事务。




7. 此设计包含所有范围级别的性能注意事项（如果这不明显，请要求扩展）。缩放是一个非问题，现在缩放问题在数据库之外。

纠正建议

这些项目只需要说明，因为在答复中提供了不正确的建议（当然，民主地向民众投票），互联网是充满了不正确的建议（业余爱好者喜欢发布他们的主观真理）：

1. 显然，有些人不明白我已经给出了一个方法在技​​术术语。因此，它不是特定国家的特定应用的伪代码。该方法适用于有能力的开发人员，但对于需要由手牵头的人员来说并不够详细。

   
   
   
   
   

   • 不明白一个月的截止期是一个例子：如果你的税务局截止目的是季度，那么一定要使用季度截止;如果你唯一的法律要求是年度，使用年度。

   
   

   • 即使您的截止日期是为了外部或合规目的，季度，公司可能会选择每月截止，即，使通量状态的周期的长度保持最小）。

     
     
     

     例如，在澳大利亚，企业的税务局截止日期是季度，但大型公司每月截止库存控制（这可以节省长期追逐错误）。

     
     
     

     例如。

   
   

   • 在原始国家和流氓国家，银行在每个月都有法律合规要求，因此他们对数字进行内部审计，保持它们的通量状态期间在最大，为明显的邪恶目的。其中一些只每年提交合规报告。这是澳大利亚银行不会失败的一个原因。

   
   



2. 在金额列中使用负/正。金钱总是有一个正值，没有负二十美元这样的东西（或者你欠我减去五十美元，然后解决双重否定意味着什么）。




3. 运动方向，或者你要对资金做什么，是一个单独的和离散的事实（到Transaction.Amount）。这需要一个单独的列（一个数据中的两个事实打破了规范化规则，结​​果是它将复杂性引入代码中）。

   
   
   
   
   

   • 实施一个TransactionType列，即存入/提取的（D，W）作为起点。随着系统的增长，只需​​为调整，退款，ATM_Withdrawal，Management_Fee等添加（A，R，w，M）。

   
   
   
   



4. 在某些原始国家/地区，诉讼要求规定，在列出交易的任何报表中，显示在每一行。 （注意，这不是审计要求，因为它们比法院要求更优越[上述方法];审计员比律师更不愚蠢等等）

   
   
   

   显然，我不会与法院的要求争论。问题是，原始编码器将其转换为： 他们无法理解：

   
   
   
   
   

   • 在报表上列印栏的要求不是要在数据库

   
   

   • 任何类型的运行总计都是派生值，它很容易编码（如果对你不容易，可以提出问题）。只需在报表中实施所需的代码。

   
   

   • Transaction.CurrentBalance作为列会导致可怕的问题：

     
     
     
     
     

     • 引入重复的列，因为它是可导出的。中断规范化。引入更新异常。

     
     

     • 更新异常：每当事务以历史方式插入或者Transaction.Amount发生更改时，所有Transaction.CurrentBalances

     
     
   
   

   • 在此日期之前必须重新计算和更新

     上述案件，提交法院使用的报告现在已过时（每份在线数据报告在打印时已过时）。也就是说。打印;评论;更改事务;重印;重新审查，直到你快乐。

   
   

   • 这就是为什么在不太原始的国家，法院不接受任何旧的印刷文件， ，例如。

   
   
$ b $ b

So, its like our normal bank accounts where we have a lot of transactions which result in inflow or outflow of money. The account balance can always be derived by simply summing up the transaction values. What would be better in this case, storing the updated account balance in the database or re-calculating it whenever needed?

Expected transaction volume per account: <5 daily

Expected retrieval of account balance: Whenever a transaction happens and once a day on an average otherwise.

How would you suggest to take a decision on this? Thanks a lot!

解决方案

Preface

This is the age of subjective "truth", and objective truth, which flows from an Authority, and which has been in place for four thousand years, is something that we are now being programmed to rebel against.

I have worked on accounting systems for 34 years, and banking systems for 24 years. There is an objective truth: Audit requirements. Additionally, when dealing with public funds, there is Legislature that must be complied with.

You don't have to implement the full accounting requirement, you can implement just the parts that you need.

Conversely, it would be ill-advised to implement something other than the standard accounting requirement (the parts thereof) because that guarantees that when the number of bugs or the load exceeds some threshold, or the system expands,you will have to re-implement. A cost that can, and therefore should, be avoided.

It also needs to be stated: do not hire an unqualified, un-accredited (by an Authority) "auditor". That is (a) an act of rebellion against Authority, and (b) there will be consequences, the same as if you hired an unqualified developer. It might be worse, if the higher authority such as the Tax Office fines you.

Therefore, be aware of the hierarchy of Authorities, and comply with the highest one. That ensures a peaceful life. That ensures that automatically, without additional effort, you comply with any lower authority. If you fail to do that, ie. comply with some lower Authority, but rebel against some higher one, the higher Authority will get you in the end.

Method

The Standard Accounting method in not-so-primitive countries is this. The "best practice", if you will, in others.

This method applies to any system that has similar operations; needs; historic monthly figures vs current-month requirements, such as Inventory Control, etc.

Consideration

First, the considerations.

1. Never duplicate data. If the current balance can be derived (and here it is simple), do not duplicate it with a summary column. Such a column is a duplication of data. It breaks Normalisation rules. Further, it creates an Update Anomaly, which otherwise does not exist.

2. If you use a summary column, whenever the Transactions are updated (as in changed, not as in when a new Transaction is inserted), the summary column value becomes obsolete, so it must be updated all the time anyway. That is the consequence of the Update Anomaly. Which eliminates the value of having it.

3. External publication. If the balance is published, as in a monthly Bank Statement, such documents usually have legal restrictions and implications, thus that published CurrentBalance value must not change after publication.

   • Any change, after the publication date, in the database, of a figure that is published externally, is evidence of dishonest conduct, fraud, etc.

     • Such an act, attempting to change published history, is the hallmark of a novice. Novices and mental patients will insist that history can be changed. But as everyone should know, ignorance of the law does not constitute a valid defence.

   • You wouldn't want your bank, in Apr 2015, to change your Current Balance that they published in their Bank Statement to you of Dec 2014.

   • That figure has to be viewed as an Audit figure, published and unchangeable.

4. Any corrections or adjustments that are necessary are made as new Transactions in the current month, even though it applies to some previous month. This is because that applicable-to month is closed and published, because one cannot change history after it has happened and it has been recorded. The only effective month is the current one.

   • For interest-bearing systems, etc, in not-so-primitive countries, when an error is found, and it has an historic effect (eg. you find out in Apr 2015 that the interest calculated on a security has been incorrect, since Dec 2014), the value of the corrected interest payment/deduction is calculated today, for the number of days that were in error, and the sum is inserted as a Transaction in the current month. Again, the only effective month is the current one.

     And of course, the interest rate for the security has to be corrected as well, so that the error does not repeat.

   • If you find an error in your bank's calculation of the interest on your Savings (interest-bearing) Account, and you have it corrected, you get a single deposit, that constitutes the whole adjustment value, in the current month. That is a Transaction in the current month.

     The bank does not: change history; apply interest for each of the historic months; recall the historic Bank Statements; re-publish the historic Bank Statements. No. Except maybe in Idi Amin type countries.

   • The same principles apply to Inventory control systems. It maintains sanity.

5. All real accounting systems (ie. those that are accredited by the Audit Authority in the applicable country, as opposed to the Mickey Mouse "packages" that abound) use a Double Entry system for Transactions, precisely because it prevents a raft of errors, the most important of which is, funds do not get "lost". That requires a simple General Ledger.

   • You probably do not need that, therefore I am not describing it here, but do remember it in case money goes "missing", because that is what you will have to implement, not some band-aid solution; not yet another unaccredited "package".

6. The major issues that affect performance are outside the scope of this question, they are in the area of whether you implement a genuine Relational Database or not (a Record Filing System in an SQL database container, typified by IDs).

   • The use of genuine Relational Keys, etc will maintain high performance, regardless of the population of the tables.

   • Conversely, an RFS will perform badly, they simply cannot perform. "Scale" when used in the context of an RFS, is a fraudulent term: it hides the cause and seeks to address everything but the cause.

Implementation

1. For each Account, there will be a ClosingBalance, in a AccountStatement table (one row per Account per month), along with StatementDate and other Statement details.

   • This is not a duplicate because it is demanded for Audit and sanity purposes.

     For Inventory, a QuantityOnHand column, in the PartAudit table (one row per Part per month)

   • It has an additional value, in that it constrains the scope of the Transaction rows required to be queried for the current month

     • Again, if your table is Relational, the Primary Key for AccountTransaction will be (AccountCode, TransactionDateTime) which will retrieve the Transactions at millisecond speeds.

     • Whereas for a Record Filing System, the "primary key" will be (TransactionID), and you will be retrieving the current month by TransactionDate, which may or may not be indexed correctly, and the rows required will be spread across the file. In any case at far less than ClusteredIndex speeds, and where the retrieved rows are many, a tablescan.

2. The Transaction table remains simple (the real world notion of a bank account Transaction is simple). It has a single positive Amount column.

3. For each Account, the CurrentBalance is:

   • the Statement.ClosingBalance of the previous month

     (for inventory, the PartAudit.QuantityOnHand)

   • plus the sum of the Transaction.Amounts in the current month, where the TransactionType indicates a deposit

     (for inventory, the Transaction.QuantityAffected)

   • minus the sum of the Transaction.Amounts in the current month, where the TransactionType indicates a withdrawal

4. In this Method, the Transactions in the current month, only, are in a state of flux, thus they must be derived. All previous months are published and closed, thus the Audit figure must be used.

5. The older rows in the Transaction table can be purged. Older than ten years for public money, five years otherwise, one year for hobby club systems.

6. Of course, it is essential that any code relating to accounting systems uses genuine OLTP Standards and genuine SQL ACID Transactions.

7. This design incorporates all scope-level performance considerations (if this is not obvious, please ask for expansion). Scaling is a non-issue, now scaling issues are honestly outside database.

Corrective Advice

These items need to be stated only because incorrect advice has been provided in SO Answers (and up-voted by the masses, democratically, of course), and the internet is chock-full of incorrect advice (amateurs love to publish their subjective "truths"):

1. Evidently, some people do not understand that I have given a Method in technical terms. As such, it is not pseudo-code for a specific application in a specific country. The Method is for capable developers, it is not detailed enough for those who need to be lead by the hand.

   • They also do not understand that the cut-off period of a month is an example: if your cut-off for Tax Office purposes is quarterly, then by all means, use a quarterly cut-off; if the only legal requirement you have is annual, use annual.

   • Even if your cut-off is quarterly for external or compliance purposes, the company may well choose a monthly cut-off, for internal Audit and sanity purposes (ie. to keep the length of the period of the state of flux to a minimum).

     Eg. in Australia, the Tax Office cut-off for businesses is quarterly, but larger companies cut-off their inventory control monthly (this saves having to chase errors over a long period).

     Eg. banks have legal compliance requirements monthly, therefore they perform an internal Audit on the figures, and close the books, monthly.

   • In primitive countries and rogue states, banks keep their state-of-flux period at the maximum, for obvious nefarious purposes. Some of them only make their compliance reports annually. That is one reason why the banks in Australia do not fail.

2. In the Transaction table, do not use negative/positive in the Amount column. Money always has a positive value, there is no such thing as negative twenty dollars (or that you owe me minus fifty dollars, and then working out that the double negatives mean something else).

3. The movement direction, or what you are going to do with the funds, is a separate and discrete fact (to the Transaction.Amount). Which requires a separate column (two facts in one datum breaks Normalisation rules, with the consequence that it introduces complexity into the code).

   • Implement a TransactionType column, which is ( D, W ) for Deposit/Withdrawal as your starting point. As the system grows, simply add ( A, R, w, M ) for Adjustment, Refund, ATM_Withdrawal, Management_Fee, etc.

   • No code changes required.

4. In some primitive countries, litigation requirements state that in any report that lists Transactions, a running total must be shown on every line. (Note, this is not an Audit requirement because those are superior [Method above] to the court requirement; Auditors are somewhat less stupid than lawyers; etc.)

   Obviously, I would not argue with an court requirement. The problem is that primitive coders translate that into: oh, we must implement a Transaction.CurrentBalance column. They fail to understand that:

   • the requirement to print a column on a report is not a dictate to store a value in the database

   • a running total of any kind is a derived value, and it is easily coded (post a question if it isn't easy for you). Just implement the required code in the report.

   • implementing the running total eg. Transaction.CurrentBalance as a column causes horrendous problems:

     • introduces a duplicated column, because it is derivable. Breaks Normalisation. Introduces an Update Anomaly.

     • the Update Anomaly: whenever a Transaction is inserted historically, or a Transaction.Amount is changed, all the Transaction.CurrentBalances from that date to the present have to be re-computed and updated.

   • in the above case, the report that was filed for court use, is now obsolete (every report of online data is obsolete the moment it is printed). Ie. print; review; change the Transaction; re-print; re-review, until you are happy. It is meaningless in any case.

   • which is why, in less-primitive countries, the courts do not accept any old printed paper, they accept only published figures, eg. Bank Statements, which are already subject to Audit requirements (refer the Method above), and which cannot be recalled or changed and re-printed.

这篇关于衍生帐户余额与简单银行帐户的存储帐户余额？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！