Accounting and database design, storage of debit and credit amounts

Question

QUESTION: In the case below, should I store my entire amount as positive decimal amounts, and then indicate the amount as "Debit" or "Credit", and not store debits as a negative amount and loans as a positive amount?

---

In my database design, I save the “debit” as a negative amount and the credit as a positive amount.

Now, sometimes reporting, the results go wrong, because if you do

TotalAmount = Amount-Fee, and if the withdrawal amount is $ 100, and the commission is $ 1.

As a result, you get $ 100- $ 1 = - $ 101, which is the wrong result!

Answer 1

You can use the ABS function in SQL Server to get the absolute value. This will allow you to consider negative numbers as positive.

eg:

select ABS(-100)

returns 100 , not -100 .

Answer 2

Using one column for everything and then using negative numbers for debits or credits does not work as you discovered. Credentials are not scalars — they are vectors that contain an enumeration (debit or credit) and a decimal number with a fixed comma (which can be positive or negative).

Any accounting transaction should contain an equal number of debits and credits. If this is not the case, this is not a valid transaction.

Similarly, the account balance is also the same vector. At any time, the total write-offs and total loans for all accounts in the accounting system must be equal to each other, otherwise something broke.

Another way to look at this is to think of the importance of accounting as a complex number, where debit accounts are real and loans are imaginary. This means that 4 debit + 3 credits = 4 + 3i. This makes it obvious that you cannot simplify this further by folding the imaginary term into a negative real term - this is not the same axis of the number line. This will be the same as claiming that 4 + 3i = 4 - 3. Invalid mathematics.

If the database could store complex numbers initially, complex numbers would actually be a good way to store credentials, probably eliminate the big confusion that programmers usually have about accounting, and lead to various interesting properties. For example, a balanced transaction will always have a phase angle of 45 degrees, as well as a balanced set of accounts. But for most databases, you need to decompose the complex number to its real and imaginary term before storing and store these terms in different columns - in the world of accounting, the names of these two columns are “debit” and “credit”, respectively.

PS: I know that some people use negative for loans and positive for debits, but it requires great care to act correctly and fragile. You must keep track of the normal balance of any account every time you touch it — for example, since the asset account has a debit normal balance, you can use a positive number to increase it. But the liability account has a negative normal balance, so increasing this account is a negative number. You cannot sum these two values ​​together at any time - they are not the same thing. Debit is what you have, and credit is what you owe. Putting both on the same column in the database table smells bad.

Answer 3

I work with the Sage Timberline accounting system and save debit as positive amounts and credits as negative amounts. In all reports, including the trial balance, you make debits + credits. Then you make negative debits for debit reversals and positive loans to cancel the loan. Works great

Answer 4

Since accounting is all based on journal entries, it is best for your data model to follow from this. This would mean having two columns in your table, one for debit and one for credit. You then leave this to the application to determine what should be considered a “positive” value and what should be considered a “negative”. (The question always arises - positive from the point of view? When you transfer money between bank accounts, it is "negative" for one account, but "positive" for another.)

This is the time since I worked on this case, but it seems that I remember that the debit and credit columns can contain both positive and negative values. Accountants have a different way of thinking about numbers than we programmers do, so when writing software for them, it can simplify the work if you try to work with your agreements.

Answer 5

This is a transaction diagram from a large book called the Data Model Resource Book. This layout meets all recording requirements without using two columns.

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

Simple and efficient, and it does not use extraneous columns as other answers suggest. One column for storing all data of numerical values ​​and still gives you the ability to properly track asset and liability accounts.

Answer 6

Well, I'm a little late to the party, but there are interesting answers here, and I thought I should add my own technique.

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

Short answer: you do not need to add a flag, because any system automatically applies the flag “debit” or “credit” when you save the number in the correct signed form. It's a sign "-". Should you store values ​​in one of two columns, debit or credit? Absolutely not! Why keep an empty field for every transaction in the system? A single column with the correct sign value is much easier to manage.

A longer answer to the question: “Accounting and database design”, storage of the amount of debit and credit.

It is completely simple and durable, if you understand that you need to store double entries. When you publish a journal in the nominal register, you offer the user a debit field and a credit field for each line in the journal. In your application, you allow only one of the fields to have a value (for each row), and it must be a positive unsigned value. When you write a transaction, if you have a debit, you simply write it as it is. If you have a value in the credit field, you change it and write it as a negative number. The database sees only one significant value in one column, in a row (record). Since any accountant will tell you that the journal entry should be balanced, so the database entries for the journal transaction lines will be added to zero. Application code should provide log balance.

Now consider the purchase account that the user adds to the system. Say we have this (unlikely) account for Widget:

£ 500 for steel bar

£ 100 for an envelope box

£ 10,000 for a lathe

£ 2120 purchase tax

£ 12,720 bill total

The application writes one entry to the document table. It has one-to-many links to the transaction table. For a three-line account, the application contains 5 transaction lines.

£ 500 related to cost of sales, general p & l account. Debit balance = expense when in p & l

£ 100 associated with Stationery, the p & l general ledger account. Debit balance = expense when in p & l

£ 10,000 related to machine, balance sheet account. Debit balance = asset when in bs.

£ 2120 associated with Input Tax Reclaimable, bs gl. Debit balance = asset, we owe money to a tax man

- £ 12,720 associated with Creditors Control, bs gl. Credit balance = liability, we owe this provider

£ 0.00 total value of 5 entries recorded in the transaction table.

Now consider the sales account that the user adds:

£ 250 for premium widgets

£ 250 for standard widgets

£ 100 sales tax

£ 600 total bills

Again, one entry is written to the document table. For a two-line invoice, 4 transaction lines are recorded. Since this is a sales invoice, the application must change the values ​​behind the scenes. Invoice accounts are accounts, but the user does not expect to add them as negative values.

£ 250 - Associated with Premium Widget Sales, p & l gl account. Credit balance = income / profit when in p & l

£ 250 - related to standard widget sales, p & l gl account. Credit balance = income / profit when in p & l

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

£ 600 associated with Debtors Control, bs gl. Debit balance = asset, we owe this client.

£ 0.00 total value of 4 entries recorded in the transaction table.

It is perfectly normal to add negative lines to the invoice if you want to pay tribute to something returned. Before writing transactions, they simply handle all the other lines. As a rule, you issue a credit note containing lines written as debit sales revenue, which reduces the cost of sales in p & l.

If the system performs inventory management, quantities are recorded in the transaction lines and they are associated with the Product table.

Bank records are often caught by non-book keepers. They say we put money in our bank so that we credited the account. Think about the fact that the bank is a person external to the business. When you transfer your money for safe storage, it becomes a debtor and must return your money on demand. Thus, bank receipts are recorded as debit and payments that are recorded as loans. When we receive a payment from a client, we write two lines of transactions:

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

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

£ 0.00 total value of 2 entries recorded in the transaction table.

If you follow this, you’ll see that GBP 600 is credited to the Debtors Control account when the invoice for the sale is issued, and GBP 600 written on it when the payment was received. Net balance = $ 0.00, which should now be our customer.

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

What is it. Each time you summarize a transaction table, it should always return zero. No need to support two columns. An application must do two things: it must be encoded so that it applies the correct signature to various types of transactions, and it needs to present the transaction in one of two columns, depending on whether it is> 0 or <0. Thus, you You can have your own trial balance, books and registers of suppliers, bank and cash accounts and a general ledger that are well formatted in debit and credit columns.

Creating a system in which both sides of a double-entry are recorded in a single transaction is attractive. If one transaction fails, it will not unbalance the accounts. You will still only have one column for the value signed. You must record two foreign keys for each transaction, one for the value of what you wrote, which can be a positive or negative value for representing debits or credits, and the other foreign key for recording the account that you publish the opposite (the value is "double post "). You may also need to write gl fk for two tax control accounts, one for the tax control account and one for the input tax control account. Thus, you can link your transaction line to four gl accounts, and not to one (plus links for customer tables, suppliers, and products that apply to both methods). Control accounts will have a very large volume of transactions associated with them. An account with 10 lines will contain 10 transactions, and not just one document. You would need to calculate the tax element for each line of the account individually, and not as a total for the document (you can do this anyway). Finally, you will have to have a special arrangement for a journal entry document, which can include 10 lines as debit, all offset by one line as credit, so a single transaction approach does not work here.