QUESTION: In the case below should I have stored all my amount as positives decimal amounts then flag the amount as either being a "Debit" or "Credit" rather than storing debits as negative amount and credits as positive amount?
问:在下面的情况下，我是否应该将我的所有金额作为正数十进制数来存储，然后标记为“借”或“贷”，而不是将借方作为负数来存储，将贷方作为正数来存储?
In my database design, I store "debit" as negative amount, and credit as positive amount.
在我的数据库设计中，我将“借记卡”存储为负数，信用卡存储为正数。
Now in reporting sometimes the results come out wrong because if you do this
在报告中有时结果会出错因为如果你这样做的话
TotalAmount = Amount-Fee, and if withdraw amount is $100, and fee is $1.
total金额=金额，如果取款金额为$100，费用为$1。
You would end up with -$100-$1 = -$101, which is the incorrect result!.
你会得到-$100-$1 = -$101，这是错误的结果!
7 个解决方案
#1
3
You can use the ABS function within sql server to get the absolute value. This would allow you to treat negative numbers as positive ones.
您可以使用sql server中的ABS函数来获取绝对值。这会让你把负数当作正数。
eg:
例如:
select ABS(-100)
选择ABS(-100)
returns 100, not -100.
返回100,而不是100年。
#2
9
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.
另一种看待这个问题的方法是把会计价值看成一个复数，其中借方是实数，贷方是虚数。这意味着4个借方+ 3个贷方= 4 + 3i。这很明显，你不能通过把虚数项化为负实项来进一步简化它，它不是相同的数轴。这就等于说4 + 3i = 4 - 3。无效的数学。
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.
如果一个数据库能够自然地存储复杂的数字，那么复杂的数字实际上是存储会计数据的好方法，这可能会消除程序员通常对会计的困惑，并会导致各种有趣的特性。例如，平衡的事务总是具有45度的相位角，平衡的帐户集也是如此。但大多数数据库都需要您在存储前将复杂数字分解为实数和虚数项，并将这些术语存储在不同的列中——在会计界，这两列的名称分别是“debits”和“credits”。
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.
注:我知道有些人会用否定的词来表示信用，也会用积极的方式去借，但这需要非常小心的去做，而且是很脆弱的。你必须跟踪任何一个账户的正常余额，例如，由于一个资产账户有一个借项正常余额，那么你可以用一个正数来增加它。但负债账户的正常余额为负，因此账户价值的增加是负数。你不可能在任何时候把这两个值相加，它们不是同一个东西。借项是你拥有的东西，而信用卡是你欠的东西。将两者放在数据库表的同一列中闻起来很糟糕。
#3
6
I work with the Sage Timberline accounting system and it saves debits as positive amounts and credits as negative amounts. In all reports, including the Trial Balance, you do debits + credits. Then you do negative debits for debit reversals and positive credits for credit reversals. Works fine
我和Sage Timberline会计系统一起工作，它将借记作为正数，贷记作为负数。在所有的报告中，包括试算平衡表，你都做了积分+积分。然后是借记倒转的负借项和贷记的正借项。工作正常
#4
5
As accounting is all based on journal entries, it might be best for your data model to follow from that. This would mean having two columns in your table, one for debit and one for credit. You then leave it up to the application to determine what should be considered a "positive" value and what should be considered "negative". (The question always arises - positive from whose point of view? When you transfer money between bank accounts, it a "negative" for one account but a "positive" for the other.)
由于会计都是基于日志记录的，所以您的数据模型最好遵循日志记录。这意味着您的表中有两列，一列是借方的，一列是贷方的。然后由应用程序决定什么应该被视为“积极”值，什么应该被视为“消极”值。(问题总是从谁的观点出发?当你在银行账户间转账时，它对一个账户是“负面”的，而对另一个账户却是“积极的”。
It's a while since I worked on this kind of thing, but I seem to remember that it is possible for the debit AND credit columns to contain both positive AND negative values. Accountants have a different way of thinking about numbers than us programmers, so when writing software for them, it can simplify things if you try to work with their conventions.
我从事这类工作已经有一段时间了，但我似乎记得，借方栏和贷方栏可能同时包含正值和负值。会计人员对数字的思考方式与我们程序员不同，因此，当为他们编写软件时，如果你试图按照他们的惯例来处理，它可以简化事情。
#5
2
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.
简单而有效，而且它不像这里的其他答案一样使用无关的列。一个列可以存储所有数值数据，并且仍然能够正确跟踪资产和负债帐户。
#6
1
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.
简短的回答是:您不需要添加标志，因为当您将标志号保存在正确的签名表单中时，任何系统都会自动应用标志'借记卡'或'credit'。“-”符号。您应该将值存储在两列中的其中一列(借记卡或信用卡)中吗?当然不行!为什么要为系统中的每个事务保存一个空字段?具有正确签名值的单个列更容易管理。
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
对钢筋£500
£100 for a box of envelopes
£100一盒信封
£10000 for a lathe
车床的£10000
£2120 purchase tax
£2120购买税
£12720 invoice total
£12720发票总
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.
应用程序将一条记录写入documents表。它有到事务表的一对多链接。对于三行发票，应用程序编写了5个事务行。
£500 linked to Cost of Sales, a p&l general ledger account. Debit balance = expense when in the p&l
£500与销售成本,损益表总分类帐帐户。在损益表中，借方余额=费用。
£100 linked to Stationery, a p&l general ledger account. Debit balance = expense when in the p&l
£100与文具,损益表总分类帐帐户。在损益表中，借方余额=费用。
£10000 linked to Machinery, a balance sheet general ledger account. Debit balance = asset when in the bs.
£10000与机械、资产负债表总分类帐帐户。借记余额=在银行的资产。
£2120 linked to Input Tax Reclaimable, a bs gl account. Debit balance = asset, we are owed money by the tax man
£2120与进项税额可收回的,bs gl帐号。借项余额=资产，我们被税务员欠了钱
-£12720 linked to Creditors Control, a bs gl account. Credit balance = liability, we owe this to the supplier
-£12720与债权人控制、bs gl帐号。信用余额=责任，我们欠供应商的
£0.00 total value of 5 records written to the transaction table.
£0.00总额的5条记录写入事务表。
Now consider a sales invoice that the user adds:
现在考虑用户添加的销售发票:
£250 for premium widgets
£250的优质小部件
£250 for standard widgets
£250的标准部件
£100 sales tax
£100的销售税
£600 invoice total
£600发票总
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.
同样，一个记录被写入到documents表。对于两行的发票，写了4条交易行。因为这是销售发票，应用程序必须在幕后反转这些值。销售发票行是簿记贷方，但用户不需要将它们作为负值添加。
£250- linked to Premium Widget Sales, a p&l gl account. Credit balance = income/profit when in the p&l
£250 -与优质部件销售,损益表gl帐号。信用余额=损益表中的收入/利润
£250- linked to Standard Widget sales, a p&l gl account. Credit balance = income/profit when in the p&l
£250 -与标准部件销售,损益表gl帐号。信用余额=损益表中的收入/利润
£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.
£100 -与输出应纳税额,bs gl帐号。信用余额=在银行的负债。我们把这笔钱付给了税务人员。
£600 linked to Debtors Control, a bs gl account. Debit balance = asset, we are owed this by the customer.
£600与债务人控制、bs gl帐号。借方余额=资产，这是客户欠我们的。
£0.00 total value of 4 records written to the transaction table.
£0.00总额4记录写入事务表。
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与银行账户,bs gl帐号。借项余额=增加资产的价值，我们有更多的钱。
£600- linked to Debtors Control, a bs gl account. Credit balance = reduces the value of the asset, we are owed less money.
£600 -与债务人控制、bs gl帐号。信用余额=降低资产的价值，我们欠的钱更少。
£0.00 total value of 2 records written to the transaction table.
£0.00总额2记录写入事务表。
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.
如果你遵循这个通过你会发现债务人控制£600写入销售发票时,和£600 -写入的时候收到付款。净余额=£0.00也就是现在我们的客户欠。
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.
就是这样。任何时候对事务表求和，它都应该返回0。不需要维护两个列。应用程序需要做两件事，需要对其进行编码，以便对各种事务类型应用正确的签名，并且需要根据事务是>还是<0在两列中的一列中显示事务。这样你就可以得到你的试算平衡表，你的客户和供应商的账簿，银行和现金账户，以及总账都很好地格式化为借方和贷方栏。
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.
在一个交易中，建立一个双入口的两边都被记录的系统是很有吸引力的。如果一个事务失败了，它不会使帐户失去平衡。值仍然只有一列，签名。您将为每笔交易记录两个gl外键，一个用于记录您所记录的值，它可以是一个正值或负值来表示借项或贷方，另一个gl外键用于记录您发布的相反(“double entry”)值的帐户。您可能还需要为两个税收控制帐户记录gl fk，一个用于输出税收控制帐户，一个用于输入税收控制帐户。因此，您可能最终将您的事务行连接到4个gl帐户，而不只是一个(加上客户、供应商和产品表的链接，这两个方法都适用)。控制帐户将有大量与之关联的事务。10行发票将有10个交易链接到它，而不是每个文件只有一个。您将必须分别计算每个发票行的税务元素，而不是作为文档的总数(无论如何，您都可以这样做)。最后，您必须为一个日志条目文档安排一个特殊的安排，该文档可能包含10行作为借方，所有的借方都被一行作为贷方抵消，因此单事务方法在这里不起作用。
#7
0
Though there is already an accepted answer which is very too the point of the question but I also want to share my opinion because it may help others in decide specially when they are designing their database!
虽然已经有了一个公认的答案，这也是问题的重点，但我也想分享我的观点，因为它可以帮助其他人在设计数据库时做出特别的决定!
Overall both have their own cons and pros and matter can be easily ended by using abs() as in accepted answer! But the problem comes when you talk across teams where different people may have different mind and believe me saving (-ve) values caused more confusions actually, specially if they are directly reading values from database!
总的来说，两者都有各自的优缺点，使用abs()可以很容易地解决问题。但是当你在不同的团队中讨论不同的人可能有不同的想法并且相信我保存(-ve)值实际上导致了更多的混淆，特别是当他们直接从数据库中读取值的时候!
I am not against of saving -ve values in database when debit in most cases but saving +ve leads to less confusions actually, even as database programmer, because we always have a column of telling (is it Debit or Credit) and who ever is going to write code can easily convert it at App Level.
我并不是反对储蓄负数据库中的值时借记在大多数情况下,但储蓄+ ve少会导致混淆,甚至作为数据库程序员,因为我们总有一列告诉(借记卡或信用卡),曾经是编写代码在应用程序级别可以很容易地将其转换。
Only exception comes with using sum(value) at database level but in reality this is least used scenario because in accounting mostly we are showing running balances and at app level we could use (+) or (-ve).
只有在数据库级别使用sum(值)时才会出现异常，但实际上，这是最不常用的场景，因为在会计中，大多数情况下我们都显示了运行余额，在app级别上我们可以使用(+)或(-ve)。
The point I want to raise is that the database can be used a Company perspective or Customer perspective and now companies have Data Analysts who think from both perspective and once we save just +ve numbers it becomes easier to remember because we already have a flag to know what value is what!
我想提高,数据库可以使用公司或客户角度和现在公司数据分析师认为从两个视角,一旦我们拯救+ ve数字变得更容易记住,因为我们已经有一个国旗知道值是什么!
#1
3
You can use the ABS function within sql server to get the absolute value. This would allow you to treat negative numbers as positive ones.
您可以使用sql server中的ABS函数来获取绝对值。这会让你把负数当作正数。
eg:
例如:
select ABS(-100)
选择ABS(-100)
returns 100, not -100.
返回100,而不是100年。
#2
9
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.
另一种看待这个问题的方法是把会计价值看成一个复数，其中借方是实数，贷方是虚数。这意味着4个借方+ 3个贷方= 4 + 3i。这很明显，你不能通过把虚数项化为负实项来进一步简化它，它不是相同的数轴。这就等于说4 + 3i = 4 - 3。无效的数学。
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.
如果一个数据库能够自然地存储复杂的数字，那么复杂的数字实际上是存储会计数据的好方法，这可能会消除程序员通常对会计的困惑，并会导致各种有趣的特性。例如，平衡的事务总是具有45度的相位角，平衡的帐户集也是如此。但大多数数据库都需要您在存储前将复杂数字分解为实数和虚数项，并将这些术语存储在不同的列中——在会计界，这两列的名称分别是“debits”和“credits”。
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.
注:我知道有些人会用否定的词来表示信用，也会用积极的方式去借，但这需要非常小心的去做，而且是很脆弱的。你必须跟踪任何一个账户的正常余额，例如，由于一个资产账户有一个借项正常余额，那么你可以用一个正数来增加它。但负债账户的正常余额为负，因此账户价值的增加是负数。你不可能在任何时候把这两个值相加，它们不是同一个东西。借项是你拥有的东西，而信用卡是你欠的东西。将两者放在数据库表的同一列中闻起来很糟糕。
#3
6
I work with the Sage Timberline accounting system and it saves debits as positive amounts and credits as negative amounts. In all reports, including the Trial Balance, you do debits + credits. Then you do negative debits for debit reversals and positive credits for credit reversals. Works fine
我和Sage Timberline会计系统一起工作，它将借记作为正数，贷记作为负数。在所有的报告中，包括试算平衡表，你都做了积分+积分。然后是借记倒转的负借项和贷记的正借项。工作正常
#4
5
As accounting is all based on journal entries, it might be best for your data model to follow from that. This would mean having two columns in your table, one for debit and one for credit. You then leave it up to the application to determine what should be considered a "positive" value and what should be considered "negative". (The question always arises - positive from whose point of view? When you transfer money between bank accounts, it a "negative" for one account but a "positive" for the other.)
由于会计都是基于日志记录的，所以您的数据模型最好遵循日志记录。这意味着您的表中有两列，一列是借方的，一列是贷方的。然后由应用程序决定什么应该被视为“积极”值，什么应该被视为“消极”值。(问题总是从谁的观点出发?当你在银行账户间转账时，它对一个账户是“负面”的，而对另一个账户却是“积极的”。
It's a while since I worked on this kind of thing, but I seem to remember that it is possible for the debit AND credit columns to contain both positive AND negative values. Accountants have a different way of thinking about numbers than us programmers, so when writing software for them, it can simplify things if you try to work with their conventions.
我从事这类工作已经有一段时间了，但我似乎记得，借方栏和贷方栏可能同时包含正值和负值。会计人员对数字的思考方式与我们程序员不同，因此，当为他们编写软件时，如果你试图按照他们的惯例来处理，它可以简化事情。
#5
2
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.
简单而有效，而且它不像这里的其他答案一样使用无关的列。一个列可以存储所有数值数据，并且仍然能够正确跟踪资产和负债帐户。
#6
1
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.
简短的回答是:您不需要添加标志，因为当您将标志号保存在正确的签名表单中时，任何系统都会自动应用标志'借记卡'或'credit'。“-”符号。您应该将值存储在两列中的其中一列(借记卡或信用卡)中吗?当然不行!为什么要为系统中的每个事务保存一个空字段?具有正确签名值的单个列更容易管理。
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
对钢筋£500
£100 for a box of envelopes
£100一盒信封
£10000 for a lathe
车床的£10000
£2120 purchase tax
£2120购买税
£12720 invoice total
£12720发票总
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.
应用程序将一条记录写入documents表。它有到事务表的一对多链接。对于三行发票，应用程序编写了5个事务行。
£500 linked to Cost of Sales, a p&l general ledger account. Debit balance = expense when in the p&l
£500与销售成本,损益表总分类帐帐户。在损益表中，借方余额=费用。
£100 linked to Stationery, a p&l general ledger account. Debit balance = expense when in the p&l
£100与文具,损益表总分类帐帐户。在损益表中，借方余额=费用。
£10000 linked to Machinery, a balance sheet general ledger account. Debit balance = asset when in the bs.
£10000与机械、资产负债表总分类帐帐户。借记余额=在银行的资产。
£2120 linked to Input Tax Reclaimable, a bs gl account. Debit balance = asset, we are owed money by the tax man
£2120与进项税额可收回的,bs gl帐号。借项余额=资产，我们被税务员欠了钱
-£12720 linked to Creditors Control, a bs gl account. Credit balance = liability, we owe this to the supplier
-£12720与债权人控制、bs gl帐号。信用余额=责任，我们欠供应商的
£0.00 total value of 5 records written to the transaction table.
£0.00总额的5条记录写入事务表。
Now consider a sales invoice that the user adds:
现在考虑用户添加的销售发票:
£250 for premium widgets
£250的优质小部件
£250 for standard widgets
£250的标准部件
£100 sales tax
£100的销售税
£600 invoice total
£600发票总
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.
同样，一个记录被写入到documents表。对于两行的发票，写了4条交易行。因为这是销售发票，应用程序必须在幕后反转这些值。销售发票行是簿记贷方，但用户不需要将它们作为负值添加。
£250- linked to Premium Widget Sales, a p&l gl account. Credit balance = income/profit when in the p&l
£250 -与优质部件销售,损益表gl帐号。信用余额=损益表中的收入/利润
£250- linked to Standard Widget sales, a p&l gl account. Credit balance = income/profit when in the p&l
£250 -与标准部件销售,损益表gl帐号。信用余额=损益表中的收入/利润
£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.
£100 -与输出应纳税额,bs gl帐号。信用余额=在银行的负债。我们把这笔钱付给了税务人员。
£600 linked to Debtors Control, a bs gl account. Debit balance = asset, we are owed this by the customer.
£600与债务人控制、bs gl帐号。借方余额=资产，这是客户欠我们的。
£0.00 total value of 4 records written to the transaction table.
£0.00总额4记录写入事务表。
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与银行账户,bs gl帐号。借项余额=增加资产的价值，我们有更多的钱。
£600- linked to Debtors Control, a bs gl account. Credit balance = reduces the value of the asset, we are owed less money.
£600 -与债务人控制、bs gl帐号。信用余额=降低资产的价值，我们欠的钱更少。
£0.00 total value of 2 records written to the transaction table.
£0.00总额2记录写入事务表。
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.
如果你遵循这个通过你会发现债务人控制£600写入销售发票时,和£600 -写入的时候收到付款。净余额=£0.00也就是现在我们的客户欠。
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.
就是这样。任何时候对事务表求和，它都应该返回0。不需要维护两个列。应用程序需要做两件事，需要对其进行编码，以便对各种事务类型应用正确的签名，并且需要根据事务是>还是<0在两列中的一列中显示事务。这样你就可以得到你的试算平衡表，你的客户和供应商的账簿，银行和现金账户，以及总账都很好地格式化为借方和贷方栏。
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.
在一个交易中，建立一个双入口的两边都被记录的系统是很有吸引力的。如果一个事务失败了，它不会使帐户失去平衡。值仍然只有一列，签名。您将为每笔交易记录两个gl外键，一个用于记录您所记录的值，它可以是一个正值或负值来表示借项或贷方，另一个gl外键用于记录您发布的相反(“double entry”)值的帐户。您可能还需要为两个税收控制帐户记录gl fk，一个用于输出税收控制帐户，一个用于输入税收控制帐户。因此，您可能最终将您的事务行连接到4个gl帐户，而不只是一个(加上客户、供应商和产品表的链接，这两个方法都适用)。控制帐户将有大量与之关联的事务。10行发票将有10个交易链接到它，而不是每个文件只有一个。您将必须分别计算每个发票行的税务元素，而不是作为文档的总数(无论如何，您都可以这样做)。最后，您必须为一个日志条目文档安排一个特殊的安排，该文档可能包含10行作为借方，所有的借方都被一行作为贷方抵消，因此单事务方法在这里不起作用。
#7
0
Though there is already an accepted answer which is very too the point of the question but I also want to share my opinion because it may help others in decide specially when they are designing their database!
虽然已经有了一个公认的答案，这也是问题的重点，但我也想分享我的观点，因为它可以帮助其他人在设计数据库时做出特别的决定!
Overall both have their own cons and pros and matter can be easily ended by using abs() as in accepted answer! But the problem comes when you talk across teams where different people may have different mind and believe me saving (-ve) values caused more confusions actually, specially if they are directly reading values from database!
总的来说，两者都有各自的优缺点，使用abs()可以很容易地解决问题。但是当你在不同的团队中讨论不同的人可能有不同的想法并且相信我保存(-ve)值实际上导致了更多的混淆，特别是当他们直接从数据库中读取值的时候!
I am not against of saving -ve values in database when debit in most cases but saving +ve leads to less confusions actually, even as database programmer, because we always have a column of telling (is it Debit or Credit) and who ever is going to write code can easily convert it at App Level.
我并不是反对储蓄负数据库中的值时借记在大多数情况下,但储蓄+ ve少会导致混淆,甚至作为数据库程序员,因为我们总有一列告诉(借记卡或信用卡),曾经是编写代码在应用程序级别可以很容易地将其转换。
Only exception comes with using sum(value) at database level but in reality this is least used scenario because in accounting mostly we are showing running balances and at app level we could use (+) or (-ve).
只有在数据库级别使用sum(值)时才会出现异常，但实际上，这是最不常用的场景，因为在会计中，大多数情况下我们都显示了运行余额，在app级别上我们可以使用(+)或(-ve)。
The point I want to raise is that the database can be used a Company perspective or Customer perspective and now companies have Data Analysts who think from both perspective and once we save just +ve numbers it becomes easier to remember because we already have a flag to know what value is what!
我想提高,数据库可以使用公司或客户角度和现在公司数据分析师认为从两个视角,一旦我们拯救+ ve数字变得更容易记住,因为我们已经有一个国旗知道值是什么!