当前位置：文江博客话题详情

会计和数据库设计，存储借方和贷方金额

发布于 2024-10-18 22:41:03 字数 279 浏览 9 评论 0原文

问题：在下面的情况下，我是否应该将所有金额存储为正小数金额，然后将该金额标记为“借方”或“贷方”，而不是将借方存储为负金额，将贷方存储为正金额？

在我的数据库设计中，我将“借方”存储为负金额，将贷方存储为正金额。

现在，在报告中，有时结果会出现错误，因为如果您这样做

TotalAmount = Amount-Fee，并且提款金额为 100 美元，费用为 1 美元。

您最终会得到 -$100-$1 = -$101，这是不正确的结果！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

橪书 2024-10-25 22:41:03

正如您所发现的，对所有内容使用一列，然后对借方或贷方使用负数是行不通的。会计值不是标量——它们是包含枚举（借方或贷方）和定点十进制数（可以是正数或负数）的向量。

任何会计交易都必须包含相同数量的借方和贷方。如果没有，则该交易不是有效的。

同样，账户余额也是同样的向量。在任何时刻，会计系统中所有帐户的总借方和总贷方必须彼此相等，否则就会出现问题。

另一种看待这个问题的方法是将会计价值视为一个复数，其中借方是实数，贷方是虚数。这意味着 4 个借方 + 3 个贷方 = 4 + 3i。这清楚地表明，您无法通过将虚数项折叠为负实数项来进一步简化 - 它不是相同的数轴轴。这与声称 4 + 3i = 4 - 3 相同。不是有效的数学。

如果数据库可以本地存储复数，那么复数实际上是存储会计数据的好方法，可能会消除程序员通常对会计产生的许多困惑，并且会产生各种有趣的属性。例如，平衡交易的相位角始终为 45 度，平衡账户集也是如此。但大多数数据库需要您在存储之前将复数分解为其实数项和虚数项，并将这些项存储在不同的列中 - 在会计领域，这两列的名称分别是“借方”和“贷方”。

PS：我知道有些人确实使用负数表示贷方，正数表示借方，但这需要非常小心才能正确执行，而且很脆弱。每次接触任何帐户时，您都必须跟踪该帐户的正常余额 - 例如，由于资产帐户有借方正常余额，那么您可以使用正数来增加它。但负债账户的正常余额为负，因此该账户价值的增加是负数。你不能在任何时候将这两个值加在一起——它们不是同一件事。借方是您所拥有的东西，而贷方是您所欠的东西。将两者放在数据库表的同一列中味道很糟糕。

Using one column for everything and then using negative numbers for either debits or credits doesn't work, as you've discovered. Accounting values are not scalars -- they are vectors which contain an enum (debit or credit) and a fixed-point decimal number (which can be positive or negative).

Any accounting transaction must contain an equal number of debits and credits. If it doesn't, it's not a valid transaction.

Likewise, an account balance is also that same sort of vector. At any instant in time, the total debits and the total credits across all the accounts in an accounting system must be equal to each other, or else something broke.

Another way of looking at this is to think of an accounting value as a complex number, where debits are real and credits are imaginary. This means that 4 debits + 3 credits = 4 + 3i. This makes it obvious that you can't simplify that any further by collapsing the imaginary term into a negative real term -- it's not the same number line axis. It would be the same as claiming that 4 + 3i = 4 - 3. Not valid math.

If a database could store complex numbers natively, then complex numbers would actually be a good way of storing accounting data, would probably clear up a lot of the confusion that programmers usually have about accounting, and would lead to all sorts of interesting properties. For instance, a balanced transaction would always have a phase angle of 45 degrees, as would a balanced set of accounts. But most databases need you to decompose the complex number into its real and imaginary terms before storage, and store those terms in different columns -- in the accounting world, the names of those two columns are "debits" and "credits", respectively.

P.S.: I'm aware that some folks do use negative for credits and positive for debits, but this takes great care to do right, and is fragile. You have to keep track of the normal balance of any account every time you touch it -- for instance, since an asset account has a debit normal balance, then you can use a positive number to increase it. But a liability account has a negative normal balance, so an increase in that account's value is a negative number. You can't sum those two values together at any time -- they aren't the same thing. A debit is something you have, while a credit is something you owe. Putting both in the same column in a database table smells bad.

回复收藏 0 原文

甜警司 2024-10-25 22:41:03

由于会计全部基于日记账分录，因此您的数据模型最好遵循日记账分录。这意味着表中有两列，一列用于借方，一列用于贷方。然后，由应用程序决定什么应被视为“正”值以及什么应被视为“负”。（问题总是出现——从谁的角度来看是积极的？当你在银行账户之间转账时，对一个账户来说是“消极”，但对另一个账户来说是“积极”。）

我已经有一段时间没有从事这种事情了，但我似乎记得借方和贷方栏可能同时包含正值和负值。会计师对数字的思考方式与我们程序员不同，因此在为他们编写软件时，如果尝试遵循他们的惯例，可以简化事情。

回复收藏 0 原文

泪痕残 2024-10-25 22:41:03

我使用 Sage Timberline 会计系统，它将借方保存为正金额，将贷方保存为负金额。在所有报告中，包括试算表，您都会进行借方+贷方。然后，您对借方冲销执行负借方操作，对贷方冲销执行正贷方操作。工作正常

回复收藏 0 原文

破晓 2024-10-25 22:41:03

好吧，我参加聚会有点晚了，但这里有一些有趣的答案，我想我应该添加我的看法。

回答这个问题：您应该将值存储为正数并标记为借方还是贷方？

简短的回答：您不需要添加该标志，因为当您以正确的签名形式保存号码时，任何系统都会自动应用“借方”或“贷方”标志。这是“-”号。您是否应该将值存储在借方或贷方两列之一中？绝对不是！为什么系统中的每笔交易都保存一个空字段？具有正确符号值的单列更容易管理。

问题标题的较长答案：会计和数据库设计，存储借方和贷方金额。

只要您了解复式记账法，它就非常简单和强大。当您将日记帐过帐到名义分类帐时，您为用户提供日记帐中每一行的借方字段和贷方字段。在您的应用程序中，您只允许其中一个字段具有值（每行），并且它必须是正的无符号值。当您编写交易时，如果您有借方，您只需按原样编写即可。如果信用字段中有一个值，则将其反转并将其写为负数。数据库在每行（记录）的单个列中只能看到一个带符号的值。正如任何会计师都会告诉您的那样，日记帐分录必须平衡，以便日记帐交易行的数据库记录加起来为零。应用程序代码必须确保日记帐必须平衡。

现在考虑用户添加到系统中的采购发票。假设我们有 Widget 公司的这张（不太可能）发票：

钢筋 500 英镑

一盒信封 100 英镑车床

10000 英镑车床

2120 英镑购置税

12720 英镑发票总额

应用程序将一条记录写入文档表。它具有到事务表的一对多链接。对于三行发票，应用程序写入 5 个交易行。

500 英镑与销售成本（损益总账账户）相关联。借方余额 =

与文具（损益总账账户）相关联的损益 100 英镑时的费用。借方余额 = 损益表中与

机械相关的 10000 英镑的费用（资产负债表总账科目）。借方余额 = 处于 bs 时的资产。

2120 英镑与可退还进项税（bs gl 账户）相关。借方余额 = 资产，税务人员欠我们

12720 英镑，与债权人控制（bs gl 账户）相关联。贷方余额 = 负债，我们欠供应商

写入交易表的 5 条记录的总价值 0.00 英镑。

现在考虑用户添加的销售发票：

高级小部件 250 英镑

，标准小部件 250 英镑小部件

£100 销售税

£600 发票总计

再次将单个记录写入文档表中。对于两行发票，写入 4 个交易行。由于这是销售发票，因此应用程序必须在幕后反转值。销售发票行是记账贷项，但用户不希望将它们添加为负值。

250 英镑- 与 Premium Widget Sales 关联，这是一个盈亏总账账户。贷方余额 = 盈亏

250 英镑时的收入/利润 - 与标准小部件销售（盈亏总账账户）相关联。贷方余额 = 盈亏 100

英镑时的收入/利润 - 与应付销项税（bs gl 帐户）相关联。信用余额 = BS 中的负债。我们欠税务人员这笔钱。

600 英镑与 Debtors Control（bs gl 账户）相关联。借方余额=资产，这是客户欠我们的。

写入交易表的 4 条记录的总价值为 0.00 英镑。

如果您想对退回的商品进行信用，则在销售发票中添加负行是完全可以的。在写入交易之前，它们只是与所有其他行逆转。更常见的是，您会开具贷方票据，其中的行记为销售收入的借方，从而减少损益表中的销售价值。

如果系统进行库存控制，数量将写入事务处理行，并且链接到产品表。

银行账目常常让非簿记员望而却步。他们说，我们把钱存入银行，所以我们记入了账户。将银行视为企业外部的人。当您将钱交给安全保管时，她/他就成为债务人，并且必须按要求归还您的钱。因此，存入银行的收入记录为借方，支出记录为贷方。当我们收到客户的付款时，我们会写入两个交易行：

链接到银行帐户的 600 英镑，即 bs gl 帐户。借方余额=资产价值增加，我们有更多的钱。

600 英镑- 与债务人控制 (Debtors Control) 相连，这是一个 BSGL 账户。贷方余额=资产价值降低，我们欠的钱减少了。

写入交易表的 2 条记录的总价值为 0.00 英镑。

如果您按照此操作进行操作，您将看到在开具销售发票时，债务人控制已写入 600 英镑，并且写入了 600 英镑收到付款后即可。净余额 = £0.00 这是我们客户现在所欠的。

因此，通过正确的设计、关系和索引，所有报告都是通过文档和交易的组合完成的。

就是这样。任何时候对交易表求和都应该始终返回零。无需维护两列。应用程序需要做两件事，需要进行编码，以便将正确的签名应用于各种交易类型，并且需要根据交易是 >0 还是 <0 将交易呈现在两列之一中。因此，您可以将试算表、客户和供应商分类账、银行和现金账户以及总分类账全部格式化为借方和贷方栏。

建立一个在一笔交易中记录复式记账双方的系统是很有吸引力的。如果单笔交易失败，账户不会失衡。您仍然只有一列已签名的值。您将为每笔交易记录两个 gl 外键，一个用于记录您所记录的值，可以是表示借方或贷方的正值或负值，另一个 gl 外键用于记录您要过账的帐户相反（“复式输入”）值。您可能还需要记录两个税务控制科目的 gl fk，一个用于销项税控制科目，一个用于进项税控制科目。因此，您最终可能会将交易行链接到四个总账账户，而不是一个（加上适用于这两种方法的客户、供应商和产品表的链接）。控制账户将有大量与之相关的交易。一张 10 行发票将有 10 笔交易与其关联，而不是每个文档只有一笔交易。您必须单独计算每个发票行的税项，而不是计算文档的总计（无论如何您都可以这样做）。最后，您必须对日记帐分录凭证进行特殊安排，其中可能包括 10 行借方，全部抵消一行作为贷方，因此单笔交易方法在这里不起作用。

OK, I'm a bit late to the party but there some interesting answers here and I thought I should add my take.

To answer the question: Should you store values as positive amounts and flag as debit or credit ?

The short answer: You don't need to add the flag, because any system automatically applies the flag 'debit' or 'credit' when you save the number in it's correct signed form. It's the '-' sign. Should you store values in one of two columns, debit or credit, instead ? Definitely not ! Why save an empty field to every transaction in the system ? A single column with the correct signed value is much easier to manage.

The longer answer to the question title: Accounting and Database Design, storing debit and credit amount.

It's perfectly straightforward and robust as long as you understand double entry book keeping. When you post a journal to the nominal ledger, you offer the user a debit field and a credit field for each line in the journal. In your application you permit only one of the fields to have a value (per line) and it must be a positive, unsigned value. When you write the transaction, if you have a debit you just write it as is. If you have a value in the credit field, you reverse it and write it as a negative number. The database sees only a single signed value in a single column, per line (record). As any accountant will tell you a journal entry must balance so the database records for the journal transaction lines will add up to zero. Application code must ensure that a journal has to be balanced.

Now consider a purchase invoice that the user adds to the system. Let's say we have this (unlikely) invoice for the Widget Company:

£500 for steel bar

£100 for a box of envelopes

£10000 for a lathe

£2120 purchase tax

£12720 invoice total

The application writes a single record to the documents table. It has one-to-many links to the transaction table. For the three-line invoice, 5 transaction lines are written by the application.

£500 linked to Cost of Sales, a p&l general ledger account. Debit balance = expense when in the p&l

£100 linked to Stationery, a p&l general ledger account. Debit balance = expense when in the p&l

£10000 linked to Machinery, a balance sheet general ledger account. Debit balance = asset when in the bs.

£2120 linked to Input Tax Reclaimable, a bs gl account. Debit balance = asset, we are owed money by the tax man

-£12720 linked to Creditors Control, a bs gl account. Credit balance = liability, we owe this to the supplier

£0.00 total value of 5 records written to the transaction table.

Now consider a sales invoice that the user adds:

£250 for premium widgets

£250 for standard widgets

£100 sales tax

£600 invoice total

Again a single record is written to the documents table. For the two-line invoice, 4 transaction lines are written. Because this is a sales invoice, the application must reverse the values behind the scenes. Sales invoice lines are book keeping credits but the user doesn't expect to have to add them as negative values.

£250- linked to Premium Widget Sales, a p&l gl account. Credit balance = income/profit when in the p&l

£250- linked to Standard Widget sales, a p&l gl account. Credit balance = income/profit when in the p&l

£100- linked to Output Tax Payable, a bs gl account. Credit balance = liability when in the BS. We owe this money to the tax man.

£600 linked to Debtors Control, a bs gl account. Debit balance = asset, we are owed this by the customer.

£0.00 total value of 4 records written to the transaction table.

It's perfectly ok to add negative lines to the sales invoice if you want to give credit for something returned. They just get reversed with all the other lines before writing the transactions. More usually you would issue a credit note, which would have the lines written as debits to sales income, reducing the value of sales in the p&l.

If the system is doing stock control, quantities are written into the transaction lines, and they are linked to the Product table.

The bank entries often catch non-book keepers out. They say, we put money in our bank so we credited the account. Think of the bank as a person external to the business. When you hand over your money for safe keeping, s/he becomes a debtor, and must hand your money back on demand. So receipts into the bank are recorded as debits and payments out are recorded as credits. When we receive payment from the customer we write two transaction lines:

£600 linked to Bank Account, a bs gl account. Debit balance = increases the value of the asset, we have more money.

£600- linked to Debtors Control, a bs gl account. Credit balance = reduces the value of the asset, we are owed less money.

£0.00 total value of 2 records written to the transaction table.

If you follow this through you'll see that Debtors Control has £600 written to it when the sales invoice is raised, and £600- written to it when the payment is received. Net balance = £0.00 which is what our customer now owes.

So with the right design, relationships and indexes all the reporting is done from the combination of documents and transactions.

And that's it. Any time you sum the transaction table it should always return zero. There's no need to maintain two columns. The application needs to do two things, it needs to be coded so that it applies the correct signing to the various transaction types, and it needs to present the transaction in one of two columns according to whether it is >0 or <0. So you can have your trial balance, your customer and supplier ledgers, bank and cash accounts, and general ledger all nicely formatted into debit and credit columns.

Building a system where both sides of the double entry are recorded in one transaction is appealing. If a single transaction fails, it doesn't unbalance the accounts. You would still have just one column for the value, signed. You would record two gl foreign keys to each transaction, one for the value of what you've recorded, which could be a positive or negative value to represent debit or credit, and another gl foreign key to record the account that you are posting the opposing ('double entry') value to. You might also need to record the gl fk for two tax control accounts as well, one for the output tax control account and one for the input tax control account. So you might end up linking your transaction line to four gl accounts instead of just one (plus the links for the customer, supplier and product tables which applies to both methods). The control accounts would have a very high volume of transactions linked to them. A 10-line invoice would have 10 transactions linked to it instead of just one per document. You would have to calculate the tax element for each invoice line individually instead of as a total for the document (you might do this anyway). Finally you would have to have a special arrangement for a journal entry document which might include 10 lines as debits all offset by one line as a credit, so the single-transaction approach doesn't work here.

回复收藏 0 原文

痴情换悲伤 2024-10-25 22:41:03

您可以使用sql server中的ABS函数来获取绝对值。这将允许您将负数视为正数。

例如：

select ABS(-100)

返回100，而不是-100。

回复收藏 0 原文

乙白 2024-10-25 22:41:03

这是来自一本名为“数据模型资源书”的好书的事务详细架构。该模式无需使用两列即可满足所有记录要求。

PK TransactionID - int
PK TransactionDetailSequenceID - smallint
   Amount decimal
   CreditDebitFlag char(1)

简单而有效，并且它不使用无关的列，正如其他答案所建议的那样。一列可存储所有数值数据，并且仍然使您能够正确跟踪资产和负债帐户。

Here is a transaction detail schema from a great book called "The Data Model Resource Book". This schema meets all the recording requirements without using two columns.

PK TransactionID - int
PK TransactionDetailSequenceID - smallint
   Amount decimal
   CreditDebitFlag char(1)

Simple and effective, and it doesn't use extraneous columns as other answers here suggest. One column to store all the numeric value data and still gives you the ability to track asset and liability accounts properly.

回复收藏 0 原文